Macro - Vlookup function, problem with range if only one cell contains data

Hello friends,

this is my first post here, so let me say hello for all of you.
I can't find topic with similar problem, which I have, so sorry if I overlooked something.

Now, let me explain which problem I have with my Excel file. I am using MS Office 2003.

I have file, where heading is put from cell B7 to U7 and data are loaded from cell "B8" to "T". I need to use Vlookup macro for cell "U8". Please be aware, that this only part of the huge number of macros, this is a reason, why I don't do it manually.

I got following macro, used as Vlookup for data in Sheet Analysis. It works very well if sheet "Analysis" contains more than 2 lines.
However, If I have data only in one row, this range definition doesn't work correctly and into cell "U8" copied cell from Heading (cell "U7") instead Vlookup formula. At the beginning It looked like very simple problem for me, but now I am totally lost without solution. Your help is highly appreciated. Thanks a lot

Sub coding2()
Application.ScreenUpdating = False
Sheets("Analysis").Select
Call filter_check
Dim lw As Long
Dim dtTest As Date
Dim df As String

lw = Range("B" & Rows.Count).End(xlUp).Row

Range("U8").Formula = "=IF(ISBLANK($H8),"""",VLOOKUP($H8,account_map,7,FALSE))"
Range("U8:U" & lw).FillDown
'to identify aging type (content or variance)

End Sub

Try this... use the if statement to tell excel not to use the U8:U8 range, also did you want to keep the formula, or are you ok with just the value, if you only want the value you can use filldown otherwise use autofill destination:= and it will keep the formula

Please Login or Register  to view this content.

Hi Scott,
thank you very much for your advise. Currently it works for one line, but I have opposite problem with more lines.

I see this message text:
Run-time error '1004'
AutoFill method of Range class failed

Scott, please overlook my previous comment about more lines. I already updated it, and current macro works very well. Thanks a lot for your time and help.

Sub coding2()
Application.ScreenUpdating = False
Sheets("Analysis").Select
Call filter_check
Dim lw As Long
Dim dtTest As Date
Dim df As String

lw = Range("B" & Rows.Count).End(xlUp).Row


Range("U8").Formula = "=IF(ISBLANK($H8),"""",VLOOKUP($H8,account_map,7,FALSE))"
If Range(Range("B7"), Range("B" & Rows.Count).End(xlUp)).Count > 2 Then
Range("U8:U" & lw).FillDown
'to identify aging type (content or variance

Else: End If