creating variable within Sub to run a Function

[RSa]

I have a spreadsheet in which I have already created and ran a macro containing a vlookup for column K. My next step is to Filter column K to any #N/A and do another vlookup back to column F. Problem is the starting cell for the vlookup in column K will be different each time I run the macro. I think I can accomplish getting the vlookup to start in the first visible cell in column K using the following FUNCTION...

Function AllVisibleCells() As Range 'finds 1st visible cell in filtered column in order to do vlookup
Set AllVisibleCells = Range("K6:K" & Cells(Rows.Count, "K").End(xlUp).Row).SpecialCells(xlCellTypeVisible)

End Function

but apparently this will not run within the Sub without having a variable -

Need help in creating this variable please.

After doing the vlookup for column K, will also have to use this same procedures to do vlookups in columns N, Q, T, W, Z

Thanks

[mikeTRON]

Why not change your original macro to use an IFERROR vlookup formula?

Please Login or Register  to view this content.

All in I don't see why you cant solve this with formula logic instead of VBA logic.

[6StringJazzer]

How are you trying to use it within the Sub? Please show the code in the Sub. Are you getting an error message? If so, what is the message?

[RSa]

Thanks for the help - used a version of the IFERROR instead of the Function & got it to work

[mikeTRON]

The simplest solutions are my favorite.