VBA to declare column headers and use in further code

[AggieJess27]

Im trying to build a vba code that will take the item name from Workbook1 and use that to lookup the column in Workbook2, then use that column to compare data between the two workbooks. I have most of the code working but im now getting the "Run-time error '1004': Application-defined or object-defined error" in the range lookup. Here is the code I am working with. Im sure its something simple but Im still relatively new to VBA.

Option Explicit
Public ITEM As String
Sub FindChg()

Dim File1 As Workbook, File2 As Workbook
Dim sh1 As Worksheet, sh2 As Worksheet
Dim i As Long
Dim need As String

'specify path and file names'
Set File1 = Workbooks.Open("C:\Users\......
Set File2 = Workbooks.Open("C:\Users\......

'specify the name of the sheets to be compared'
Set sh1 = File1.Sheets("Sheet 1")
Set sh2 = File2.Sheets("Sheet 2")
need = sh1.Range("B7")

'find column in summary to compare with'
With sh2
With .Rows(9)
On Error Resume Next
ITEM = Split(.Find("Name", .Cells(.Cells.Count), xlValues, , xlByColumns, xlPrevious).Address, "$")(1)
On Error GoTo 0
End With
End With

End Sub
Sub test1()
MsgBox ITEM

End Sub

Sub CompareExcelFiles()

Dim File1 As Workbook, File2 As Workbook
Dim sh1 As Worksheet, sh2 As Worksheet
Dim i As Long
Dim need As String
Dim ITEM As String


'specify path and file names'
Set File1 = Workbooks.Open("C:\Users\....
Set File2 = Workbooks.Open("C:\Users\....

'specify the name of the sheets to be compared'
Set sh1 = File1.Sheets("Sheet 1")
Set sh2 = File2.Sheets("Sheet 2")
need = sh1.Range("B7")

'loop through lines in both files'
For i = 1 To sh1.Range("D" & Rows.Count).End(xlUp).Row
'checks if the line in Workbook1 is same as in Workbook2'
If sh1.Range("D" & i).Value <> sh2.Range(ITEM & i).Value Then >>this is where the error is occurring
'Copy line from Workbook2 to Workbook1'
sh2.Range(ITEM & i).Copy sh1.Range("D" & i)
End If
Next


End Sub

 

[Engberg]

Very simple, you have declared ITEM in the sub CompareExcelFiles(), which overwrites the public variable with the same name (which is the one you want to use).
Simply remove "Dim ITEM As String" in that sub. I've tested your code, you might have another issue, not sure what result you're expecting though so I'll let you test first

Also, when you paste code, please format it as code - this hurts to read

 

[AggieJess27]

Very simple, you have declared ITEM in the sub CompareExcelFiles(), which overwrites the public variable with the same name (which is the one you want to use).
Simply remove "Dim ITEM As String" in that sub. I've tested your code, you might have another issue, not sure what result you're expecting though so I'll let you test first

Also, when you paste code, please format it as code - this hurts to read

View attachment 113750

I removed the Dim ITEM as String and im still getting the error.

 

[Engberg]

Did you run your Sub FindChg() first?

 

[AggieJess27]

Did you run your Sub FindChg() first?

Yes. I just walked through it and still get the 1004 error.

 

[Engberg]

I don't know why. I cleaned it up a bit and compressed all your code into one sub, are you still getting the error with this?

Option Explicit

Sub findAndCompare()
Dim File1 As Workbook, File2 As Workbook, sh1 As Worksheet, sh2 As Worksheet, ITEM As String
Set File1 = ThisWorkbook ' YOUR WORKBOOKS HERE
Set File2 = ThisWorkbook
Set sh1 = File1.Sheets("Blad1") 'YOUR SHEETS HERE
Set sh2 = File2.Sheets("Blad2")

' ---- This section is a bit confusing to me, what area are you trying to search in?
With sh2.Rows(9)
    ITEM = Split(.Find("Name", .Cells(.Cells.Count), xlValues, , xlByColumns, xlPrevious).Address, "$")(1)
    'MsgBox ITEM
End With

'checks if the line in Workbook1 is same as in Workbook2'
Dim i
For i = 1 To sh1.Range("D" & sh1.Rows.Count).End(xlUp).Row
    If sh1.Range("D" & i).Value <> sh2.Range(ITEM & i).Value Then ' >>this is where the error is occurring
        'Copy line from Workbook2 to Workbook1'
        sh2.Range(ITEM & i).Copy sh1.Range("D" & i)
    End If
Next
End Sub

 

[AggieJess27]

I don't know why. I cleaned it up a bit and compressed all your code into one sub, are you still getting the error with this?

Option Explicit

Sub findAndCompare()
Dim File1 As Workbook, File2 As Workbook, sh1 As Worksheet, sh2 As Worksheet, ITEM As String
Set File1 = ThisWorkbook ' YOUR WORKBOOKS HERE
Set File2 = ThisWorkbook
Set sh1 = File1.Sheets("Blad1") 'YOUR SHEETS HERE
Set sh2 = File2.Sheets("Blad2")

' ---- This section is a bit confusing to me, what area are you trying to search in?
With sh2.Rows(9)
    ITEM = Split(.Find("Name", .Cells(.Cells.Count), xlValues, , xlByColumns, xlPrevious).Address, "$")(1)
    'MsgBox ITEM
End With

'checks if the line in Workbook1 is same as in Workbook2'
Dim i
For i = 1 To sh1.Range("D" & sh1.Rows.Count).End(xlUp).Row
    If sh1.Range("D" & i).Value <> sh2.Range(ITEM & i).Value Then ' >>this is where the error is occurring
        'Copy line from Workbook2 to Workbook1'
        sh2.Range(ITEM & i).Copy sh1.Range("D" & i)
    End If
Next
End Sub

That worked!! Thanks so much!