Invalid or Unqualified Reference

[defcon_3]

Hello guys,

I have been working on this on a couple of hours but I always got an error stating "Invalid or Unqualified Reference". The code is actually look for match and copy.

Original Code:

Sub compare()
Dim a, b, na&, nb&, i&
Dim d As Object, x
a = ThisWorkbook.Sheets("sheet1").Range("B3").CurrentRegion.Resize(, 15)
na = UBound(a, 1)
Set d = CreateObject("scripting.dictionary")
d.comparemode = 1
For i = 2 To na
x = a(i, 1) & Chr(29) & a(i, 2)
d(x) = i
Next i
b = Workbooks("book1.xls").Sheets("sheet1").Range("B3").CurrentRegion.Resize(, 15)
nb = UBound(b, 1)
For i = 2 To nb
x = b(i, 1) & Chr(29) & b(i, 2)
If d.exists(x) Then
a(d(x), 7) = b(i, 7)
a(d(x), 8) = b(i, 9)
a(d(x), 9) = b(i, 11)

End If
Next i
Sheets("sheet1").Range("A1").Resize(na, 15) = a
End Sub

This work only that it doesnt loop to the end of column B so when there is an empty row it doesnt copy the data even if it match so I tweak it like this.


Sub compare()
Dim a, b, na&, nb&, i&
Dim d As Object, x
a = ThisWorkbook.Sheets("sheet1").Range("B3:B" & .Range("B" & .Rows.Count).End(xlUp).Row) '.Range("B3").CurrentRegion.Resize(, 15)
na = UBound(a, 1)
Set d = CreateObject("scripting.dictionary")
d.comparemode = 1
For i = 2 To na
x = a(i, 1) & Chr(29) & a(i, 2)
d(x) = i
Next i
b = Workbooks("book1.xls").Sheets("sheet1").Range("B3:B" & .Range("B" & .Rows.Count).End(xlUp).Row) '.Range("B3").CurrentRegion.Resize(, 15)
nb = UBound(b, 1)
For i = 2 To nb
x = b(i, 1) & Chr(29) & b(i, 2)
If d.exists(x) Then
a(d(x), 7) = b(i, 7)
a(d(x), 8) = b(i, 9)
a(d(x), 9) = b(i, 11)

End If
Next i
Sheets("sheet1").Range("A1").Resize(na, 15) = a
End Sub

This one returns me an "Invalid or Unqualified Reference" error.

Can you lend me some help with this? Thanks in advance.

[Bob Phillips]

Sub compare()
Dim a, b, na&, nb&, i&
Dim d As Object, x
With ThisWorkbook.Sheets("sheet1")
a = .Range("B3:B" & .Range("B" & .Rows.Count).End(xlUp)) '.Range("B3").CurrentRegion.Resize(, 15)
End With
na = UBound(a, 1)
Set d = CreateObject("scripting.dictionary")
d.comparemode = 1
For i = 2 To na
x = a(i, 1) & Chr(29) & a(i, 2)
d(x) = i
Next i
With Workbooks("book1.xls").Sheets("sheet1")
b = .Range("B3:B" & .Range("B" & .Rows.Count).End(xlUp)) '.Range("B3").CurrentRegion.Resize(, 15)
End With
nb = UBound(b, 1)
For i = 2 To nb
x = b(i, 1) & Chr(29) & b(i, 2)
If d.exists(x) Then
a(d(x), 7) = b(i, 7)
a(d(x), 8) = b(i, 9)
a(d(x), 9) = b(i, 11)

End If
Next i
Sheets("sheet1").Range("A1").Resize(na, 15) = a
End Sub

[defcon_3]

That was quick. Thanks xld. Ill try on different scenario and report back.

[defcon_3]

xld it returns to Runtime Error 1004, Aplication-defined or object defined error.

[defcon_3]

Got it with this

a = .Range("a1", .Range("b" & Rows.Count).End(xlUp)).Resize(, 15).Value


But applying it to the real workbook sends me this error again on the highlighted line.
Runtime Error 1004, Aplication-defined or object defined error.


Sub compare()
Dim a, b, na&, nb&, i&
Dim d As Object, x
With ThisWorkbook.Sheets("sheet1")
a = .Range("a1", .Range("b" & Rows.Count).End(xlUp)).Resize(, 32).Value
End With
na = UBound(a, 1)
Set d = CreateObject("scripting.dictionary")
d.comparemode = 1
For i = 2 To na
x = a(i, 1) & Chr(29) & a(i, 2)
d(x) = i
Next i
With Workbooks("prodtest.xls").Sheets("sheet1")
b = .Range("a1", .Range("b" & Rows.Count).End(xlUp)).Resize(, 32).Value
End With
nb = UBound(b, 1)
For i = 2 To nb
x = b(i, 1) & Chr(29) & b(i, 2)
If d.exists(x) Then
a(d(x), 7) = b(i, 6)
a(d(x), 8) = b(i, 8)
a(d(x), 9) = b(i, 10)

End If
Next i
Sheets("sheet1").Range("A1").Resize(na, 32) = a
End Sub