Script problem - Help please

  • Thread starter Thread starter Les Stout
  • Start date Start date
L

Les Stout

Hi, Tom Ogilvy kindly gave me the code below, which works fantastic. All
i need is that if it does not find a value it must leave the cell that
it is copying to blank, at the moment it is putting in a zero. So in
other words i only need it to copy the cells with a value in.
Could somebody please give some advice how i can change the code?


Sub LookupsAA()

Dim myLookUpRng As Range
Dim i As Long
Dim NumRows As Long
Dim LastRow As Long
Range("D4").Select
With Workbooks(SuppFileNameC).Worksheets(SheetName)
Set myLookUpRng = .Range("D:N")
End With
LastRow = Cells(Rows.Count, "D").End(xlUp).Row
NumRows = LastRow - 3
With Cells(4,L).Resize(NumRows)
.Formula = "=Vlookup(D4," & _
myLookupRng.Address(1,1,xlA1,True) & ",9,0)"
.Value = .Value
End With
With Cells(4,M).Resize(NumRows)
.Formula = "=Vlookup(D4," & _
myLookupRng.Address(1,1,xlA1,True) & ",10,0)"
.Value = .Value
End with
With Cells(4,"L").Resize(NumRows)
.Font.ColorIndex = 3
.Font.bold = True
End With
Range("A4").Select
CloseForm2
End Sub


Les Stout
 
I've not done this with VBA, but I've done it with the regular EXCEL
formulas. Maybe you can figure out how to get it to work in VBA.

This formula will fill in a "" if there is nothing found.

=IF(ISNA(VLOOKUP(D4,LookupRange,2,FALSE)),"",VLOOKUP(D4,LookupRange,2,FALSE))
 
Just add this line after all the main work has been done

Cells(4, l).Resize(NumRows, 2).Replace "0", ""


--

HTH

RP
(remove nothere from the email address if mailing direct)
 
Thanks all for the input. Bob, rather like your suggestion and shall try
it out at work tomorrow.

best regards,

Les Stout
 
Back
Top