98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Vlookup in VBA
Vlookup in VBA
Resolved · Medium Priority · Version 2016
Tue 26 Nov 2019, 21:57Delegate Yiota said...
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?
Wed 27 Nov 2019, 11:48Trainer Richard said...
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
Wed 27 Nov 2019, 14:09Delegate Yiota said...
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
Mon 2 Dec 2019, 09:19Trainer Jens said...
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
Mon 9 Dec 2019: Automatically marked as resolved.
Training information:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:##### displaying in ExcelWhen you get a series of hash symbols (####) appearing in some of your cells in a spreadsheet, this can make you think that you've make some kind of mistake. |
