VBA Run-Time Error 1004

[ctouchberry]

I'm getting a "Run-Time Error 1004: Application-defined or object-defined" message during the debug process on this VLookup function. Error is happening on the VLookup, can't proceed further.

I got the function to work on a previous section of code, and then adapted it for use in this new section. I'm not sure what the issue is.

I want the code to look at the value in JCX/14, and it to the value in 08-Attribute/1. If they match, then put the value in 08-Attribute/9 in JCX/14, in the correct row. If there are multiple values in 08-Attribute/9 with the same value in Column 1, then the Column 9 values need to be put in successive columns in that same row.

https://imgur.com/a/imwMx
https://imgur.com/a/tDT8X

TagRowCounter = 2
Do
    Sheets("JCX").Cells(TagRowCounter, 14).Value = Application.VLookup(Sheets("JCX").Cells(TagRowCounter, 10), Sheets("08-Attribute").Range("A"), 9, 0)
        
        If Sheets("08-Attribute").Cells(AttributeRowCounter, 9) = "CFM" Then
                Sheets("JCX").Cells(TagNumberRow, 14).Value = Sheets("08-Attribute").Cells(AttributeRowCounter, 11).Value
                
            ElseIf Sheets("08-Attribute").Cells("AttributeRowCounter,9") = "SP" Then
                Sheets("JCX").Cells(TagNumberRow, 15).Value = Sheets("08-Attribute").Cells(AttributeRowCounter, 11).Value
                
            ElseIf Sheets("08-Attribute").Cells("AttributeRowCounter,9") = "RPM" Then
                Sheets("JCX").Cells(TagNumberRow, 16).Value = Sheets("08-Attribute").Cells(AttributeRowCounter, 11).Value
                
            ElseIf Sheets("08-Attribute").Cells("AttributeRowCounter,9") = "Motor_HP" Then
                Sheets("JCX").Cells(TagNumberRow, 17).Value = Sheets("08-Attribute").Cells(AttributeRowCounter, 11).Value
                
            Else
                'Tag Value Name
                Sheets("JCX").Cells(TagNumberRow, 20).Value = Sheets("08-Attribute").Cells(AttributeRowCounter, 9).Value
                TagValueNameColumn = TagValueNameColumn + 2
                'Tag Value Number
                Sheets("JCX").Cells(TagNumberRow, 21).Value = Sheets("08-Attribute").Cells(AttributeRowCounter, 11).Value
                
                TagValueColumn = TagValueColumn + 2
                
                TagRowCounter = TagRowCounter + 1
                
                AttributeRowCounter = AttributeRowCounter + 1
            End If

 

[Joe4]

How is range "A" defined on your "08-Attribute" sheet?

 

[ctouchberry]

Range A is a list of Tag Numbers. For now, it's "AHU-2" for the first 6 rows.

Check out the first image link for that sheet screenshot.

 

[Joe4]

I cannot see any of those images. Corporate security where I work blocks all those sites.
Note that the third argument in your VLOOKUP function is returning the 9th column.
If you Range("A") does not contain at least nine columns, you will get an error (if Range("A") only has column A in it, you will have problems).

 

[ctouchberry]

Ah, ok thanks.

I changed my Range to ("A:K"), and that put the value in the right column.

But it didn't put it in the right row. The CFM number for AHU-2 is still on Row 2, which is for HN2.

It also completely skipped the rest of the data, the loop didn't engage.

Do
    Sheets("JCX").Cells(TagRowCounter, 14).Value = Application.VLookup(Sheets("JCX").Cells(TagRowCounter, 10), Sheets("08-Attribute").Range("A:K"), 9, 0)
        
        If Sheets("08-Attribute").Cells(AttributeRowCounter, 9) = "CFM" Then
                Sheets("JCX").Cells(TagNumberRow, 14).Value = Sheets("08-Attribute").Cells(AttributeRowCounter, 11).Value
                
            ElseIf Sheets("08-Attribute").Cells("AttributeRowCounter,9") = "SP" Then
                Sheets("JCX").Cells(TagNumberRow, 15).Value = Sheets("08-Attribute").Cells(AttributeRowCounter, 11).Value
                
            ElseIf Sheets("08-Attribute").Cells("AttributeRowCounter,9") = "RPM" Then
                Sheets("JCX").Cells(TagNumberRow, 16).Value = Sheets("08-Attribute").Cells(AttributeRowCounter, 11).Value
                
            ElseIf Sheets("08-Attribute").Cells("AttributeRowCounter,9") = "Motor_HP" Then
                Sheets("JCX").Cells(TagNumberRow, 17).Value = Sheets("08-Attribute").Cells(AttributeRowCounter, 11).Value
                
            Else
                'Tag Value Name
                Sheets("JCX").Cells(TagNumberRow, 20).Value = Sheets("08-Attribute").Cells(AttributeRowCounter, 9).Value
                TagValueNameColumn = TagValueNameColumn + 2
                'Tag Value Number
                Sheets("JCX").Cells(TagNumberRow, 21).Value = Sheets("08-Attribute").Cells(AttributeRowCounter, 11).Value
                
                TagValueColumn = TagValueColumn + 2
                
                TagRowCounter = TagRowCounter + 1
                
                AttributeRowCounter = AttributeRowCounter + 1
            End If
    
Loop Until Cells(AttributeRowCounter, 1) = False

 

[Joe4]

I changed my Range to ("A:K"), and that put the value in the right column.

But it didn't put it in the right row. The CFM number for AHU-2 is still on Row 2, which is for HN2.

It also completely skipped the rest of the data, the loop didn't engage.

Hmmm, without seeing those images you posted, I am afraid that doesn't seem to make a whole lot of sense to me.
I cannot see those until I am at home on my work computer.

In any event, it sounds like it solved the error issue you were having. So now it is a different issue of the logic in your loop.

 

[ctouchberry]

Yeah, the specific error has been resolved, though the overall section is far from complete.

I'm having issues uploading image content to the thread. Can I do that?

 

[ctouchberry]

Not sure what I need to include in this VBA

I'm rather new to VBA, and am having issues figuring out how to write what I want Excel to do. The first image link is the 08-Attribute screenshot link, the second is the results page that I'm loading data to.

https://imgur.com/a/imwMx
https://imgur.com/a/XjYQg

The "50" value in the CFM column is in the correct column, but not in the correct row. It should be in the AHU-2 row, because of the relationship in the 08-Attribute sheet {see first screenshot link}. I'm pretty sure I need to clarify this relationship somehow initially, but I'm not sure what the code would be.

Also, when the VBA loops for the first time, and does NOT see "CFM" in the 3rd row (3rd row because of the "TagRowCounter = TagRowCounter + 1" logic), it flips out and returns a #### in the Row 3/CFM Column. This happens again for Row 4.

TagRowCounter = 2
Do
    Sheets("JCX").Cells(TagRowCounter, 14).Value = Application.VLookup(Sheets("08-Attribute").Cells(TagRowCounter, 1), Sheets("JCX").Range("J:J"), 10, 0)
        
        If Sheets("08-Attribute").Cells(AttributeRowCounter, 9) = "CFM" Then
                Sheets("JCX").Cells(TagNumberRow, 14).Value = Sheets("08-Attribute").Cells(AttributeRowCounter, 11).Value
                
            ElseIf Sheets("08-Attribute").Cells(AttributeRowCounter, 9) = "SP" Then
                Sheets("JCX").Cells(TagNumberRow, 15).Value = Sheets("08-Attribute").Cells(AttributeRowCounter, 11).Value

The rest of the code is as follows

ElseIf Sheets("08-Attribute").Cells(AttributeRowCounter, 9) = "RPM" Then
                Sheets("JCX").Cells(TagNumberRow, 16).Value = Sheets("08-Attribute").Cells(AttributeRowCounter, 11).Value
                
            ElseIf Sheets("08-Attribute").Cells(AttributeRowCounter, 9) = "Motor_HP" Then
                Sheets("JCX").Cells(TagNumberRow, 17).Value = Sheets("08-Attribute").Cells(AttributeRowCounter, 11).Value
                
            Else
                'Tag Value Name
                Sheets("JCX").Cells(TagNumberRow, TagValueNameColumn).Value = Sheets("08-Attribute").Cells(AttributeRowCounter, TagValueNameColumn).Value
                'Tag Value Number
                Sheets("JCX").Cells(TagNumberRow, TagValueColumn).Value = Sheets("08-Attribute").Cells(AttributeRowCounter, TagValueColumn).Value
            End If
                TagValueNameColumn = TagValueNameColumn + 2
                TagValueColumn = TagValueColumn + 2
                TagRowCounter = TagRowCounter + 1
                TagNumberRow = TagNumberRow + 1
                AttributeRowCounter = AttributeRowCounter + 1
                
Loop Until Sheets("08-Attribute").Cells(AttributeRowCounter, 1) = False

 

[Joe4]

There are tools you can use to post screen images. They are listed in Section B of this link here: http://www.mrexcel.com/forum/board-a...forum-use.html.
Also, there is a Test Here forum on this board that you can use to test out these tools to make sure they are working correctly before using them in your question.