Vlookup in VBA

Resolved · Medium Priority · Version 2016

Tue 26 Nov 2019, 21:57 Delegate 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:48 Trainer 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:09 Delegate 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:19 Trainer 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 courses

	98.7 % recommend us
	More live stats...

Training information:

Active questions
Advanced/consultancy questions
Today's questions
Unanswered questions
Popular forum topics

Excel tip:

##### displaying in Excel

When 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.

This is a common misconception - what this actually means is that the cell is not wide enough to fully display the content of the cell.

All you need to do to see what is actually in the cell is to widen the column that the cell is in.

View all Excel hints and tips