vlookup VBA not working!!!

[bran8989]

Not sure what the issue is.. hopefully someone can help!!!

Heres my code:

Sub Test()

Dim SeriesName As Range
Dim LookupRange As Range
NumRows = Range("A1", Range("A1").End(xlDown)).Rows.Count

For x = 2 To NumRows
Set SeriesName = Series3.Range("A" & x).Value
Set LookupRange = CMBs.Range("A:C").Value
Range("C1").Select
shtInput.Range("C" & x).Value = Application.WorksheetFunction.VLookup(SeriesName, LookupRange, 2, False)


ActiveCell.Offset(1, 0).Select
Next


End Sub

Thanks!!

 

[Logit]

.
I believe you need to Dim X As Integer.

Not certain that will totally correct your macro.

Can you explain in clear language how your worksheet is laid out and what you expect the macro to do with the data ?
A best practice would be to post your example workbook to DROPBOX or other cloud website for download so it can be
examined.

 

[Fluff]

How about

Sub Test()

Dim SeriesName As Range
Dim LookupRange As Range
numRows = Range("A1", Range("A1").End(xlDown)).Rows.count
Set LookupRange = CMBs.Range("A:C")

For x = 2 To numRows
   Set SeriesName = Series3.Range("A" & x)
   ShtInput.Range("C" & x).Value = Application.vlookup(SeriesName.Value, LookupRange, 2, False)
Next


End Sub

 

[bran8989]

I get an object error. Run time 424 "Object required". Same issue I had with my code.

 

[Fluff]

Which line of code gave that error?

 

[bran8989]

Looks like its here...

Set LookupRange = CMBs.Range("A:C")


For x = 2 To numRows
Set SeriesName = Series3.Range("A" & x)
ShtInput.Range("C" & x).Value = Application.VLookup(SeriesName.Value, LookupRange, 2, False)

 

[Fluff]

Do you have a variable called CBs, or a sheet with that codename?

 

[bran8989]

Yes i do, thats what is driving me crazy..

 

[bran8989]

I've got this now..

Sub Test1()


Dim SeriesName As Range
Dim LookupRange As Range
Dim Reference As Range


numRows = Range("A1", Range("A1").End(xlDown)).Rows.Count




For x = 2 To numRows
Set SeriesName = Sheet1.Range("A" & x)
Set LookupRange = Reference.Range("A:C")
Sheet3.Range("C" & x).Value = Application.VLookup(SeriesName, LookupRange, 2, False)
Next




End Sub


But it still gives me an "object variable or with block variable not set"..

 

[Fluff]

As the lookupRange never changes you should put that before the loop, otherwise it's constantly being reset every iteration.
What is "Reference" meant to be? you have declared it as a Range, but never assigned a value to it.