vlookup

[jln via OfficeKB.com]

Im trying to do a row count with a vlookup so that only rows being used down

to the first space are in the vlookup. Here is the code that i have for just
the vlookup. So what im thinking is that i need to change

' Change this line
Set myR3 = Sheets("Modem").Range("Z2:Z4075")

' Change this line
myR3.FormulaR1C1 = "=VLOOKUP(RC[-24],MGICLPMI!R2C4:R621C14,11,0)"

myR3.Copy
myR3.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
myR3.Replace What:="#N/A", Replacement:="0", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

 

[Guest]

with Sheets("Modem")
Set myR3 = .Range(.Cells(2,"Z"),.Cells(2,"Z").End(xldown))
End With

With Worksheets("MGICLPMI")
set myR4 = .Range(.Cells(2,4),.Cells(2,4).End(xldown)).Resize(,13)
End with

myR3.FormulaR1C1 = "=VLOOKUP(RC[-24]," & _
myR4.Address(1,1,xlR1C1,True) & ",11,0)"

--
Regards,
Tom Ogilvy


--
Regards,
Tom Ogilvy

Im trying to do a row count with a vlookup so that only rows being used down
to the first space are in the vlookup. Here is the code that i have for just
the vlookup. So what im thinking is that i need to change

' Change this line
Set myR3 = Sheets("Modem").Range("Z2:Z4075")

' Change this line
myR3.FormulaR1C1 = "=VLOOKUP(RC[-24],MGICLPMI!R2C4:R621C14,11,0)"

myR3.Copy
myR3.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
myR3.Replace What:="#N/A", Replacement:="0", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False