Script problem - Help please

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

*** Sent via Developersdex http://www.developersdex.com ***
 
B

Barb Reinhardt

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))
 
V

Vacation's Over

replace
.Value = .Value
with
If .Value = 0 then .value = ""
.Value = .Value
 
B

Bob Phillips

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top