Yiota has attended:
Access Introduction course
Access Intermediate course
Excel VBA Introduction course
Excel VBA Intermediate course
Vlookup in VBA
Hello, how can I do vlookup for blank cells only in Excel VBA?
RE: vlookup in VBA
Hi Yiota,
Senior VBA Trainers are currently Training so can I check that you've got the foundation steps covered.
Can I check that you are happy with the Vlookup code already?
https://www.stl-training.co.uk/b/use-vlookup-function-excel-vba/
and the commands for blank cells
https://www.stl-training.co.uk/b/specialcells-in-vba/
Let me know if you require further support and that you are happy with both of those pieces
Kind regards
Richard
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
RE: vlookup in VBA
Hi Richard
I've been using this code which works
sub xx()
.range("F4").value=worksheetfunction.volookup(range("e4").value,range("table1"),2,0)
end sub
This works when I tried this one below for blanks it does not give me the correct data, it's not in sync for some reason
Range("E3:E13").SpecialCells(xlCellTypeBlanks).Formula = WorksheetFunction.VLookup(Range("a3:a13").Value, Range("table2"), 2, 0)
any ideas?
Thanks
Yiota
RE: vlookup in VBA
Hi Yiota,
Try:
For Each cl In Range("e1:e13").SpecialCells(xlCellTypeBlanks)
cl= WorksheetFunction.VLookup(cl.offset(0,-4), Range("table2"), 2, 0)
Next cl
Please let me know if it is not working for you.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
