0

I keep getting error '483': Object doesn't support this property or method on he highlighted line. I'm a complete beginner with excel-vba and I am trying to learn it by myself.

Sub Magic()

Dim i As Integer, j As Integer, k As Integer
Dim featcode(9999)
Dim partnum(9999)
k = 4
i = 0
j = 0        
For i = 2 To 616 
    featcode(i) = Cells(i, 1).Value
Next i
    For j = 1 To 9999 
    partnum(j) = ThisWorkbook.Worksheets(3).Cells(j, 8).Value
Next j
        For i = 2 To 616
    For j = 1 To 1000
        If featcode(i) = partnum(j) Then
            **ThisWorkbook.Worksheets(2).Cells(i, k).Value = ThisWorkbook.Worksheets(3).partnum(j).Value**
            k = k + 1
        End If
    Next j
    k = 4
Next i
End Sub
Improve this question
4
  • 1
    Since partNum(j) is going to be a value, you can't do .Value.Value. What is partNum(j)? Is that a value for a row, or column? You're missing what Cell() you want to put partNum(j) in, if that was your intention. Can you explain what the sub is supposed to do? Commented Jun 17, 2016 at 14:54
  • Try replacing the line to ThisWorkbook.Worksheets(2).Cells(i, k).Value = partnum(j) . That's why you have the array in the first place, no ? Commented Jun 17, 2016 at 14:56
  • 1
    Just use ..= partnum(j) you dont need the reference to the Worksheet Commented Jun 17, 2016 at 14:56
  • Hi and thanks for helping. partNum(j) is supposed to store values down a column. What this vba is trying to do is the following: I have a long list of characteristics and a lot of part numbers on another worksheet that have some characteristics of their own. There are multiple characteristics for each part number and they are not unique. I need to extract all the part numbers for a certain set of characteristics. I know I'll need to use a 2D array but this was just the draft to see if it works. Commented Jun 18, 2016 at 9:18

1 Answer 1

Reset to default
1

This isn't exclusively an answer to your question, I wanted to als give you some tips regarding your code

Sub Magic()

Dim ws as Worksheet
Dim i As Integer, j As Integer, k As Integer
'You can use dynamic arrays in VBA so you dont have to "guess" the length beforehand, see first comment to this answer
Dim featcode(9999)
Dim partnum(9999)
Set ws = ThisWorkbook.Worksheets(3)
'you dont have to assign values to these variables outside of your for loop, as you assign them right there
k = 4
i = 0
j = 0        
For i = 2 To 616 
    featcode(i) = Cells(i, 1).Value
Next i
'indent new lines properly, so you don't lose overview
For j = 1 To 9999 
    partnum(j) = ThisWorkbook.Worksheets(3).Cells(j, 8).Value
Next j
For i = 2 To 616
   For j = 1 To 1000
        If featcode(i) = partnum(j) Then
            'you can declare objects for referencing to worksheets or cells, so you dont have to write these enormous blocks of code (see above)
            'so instead of ThisWorkbook.Worksheets(2).Cells(i, k).Value = partnum(j)
            'use
            ws.Cells(i,k) = partnum(j)
            k = k + 1
        End If
    Next
    k = 4
Next
End Sub

HTH :)

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.