ExcelBanter - View Single Post - autofill macro has error when there is nothing to fill.

Dave Peterson[_3_]

first, I wouldn't use a string variable to represent the row number:

Option Explicit
Sub testme()

Dim myRow As Long

Range("K2").FormulaR1C1 = _
"=IF(ISNA(VLOOKUP(RC[-9],bast,1,0)),"""",""YES"")"
Range("L2").FormulaR1C1 = _
"=IF(ISNA(VLOOKUP(RC[-10],bast,1,0)),"""",VLOOKUP(RC[-10],bast,7,0))"
Range("M2").FormulaR1C1 = _
"=IF(ISNA(VLOOKUP(RC[-11],ban,15,0)),""""," _
& "IF(VLOOKUP(RC[-11],ban,15,0)<=0,""Past""," _
& "IF(VLOOKUP(RC[-11],bast,15,0)5,""On"",""Yep"")))"

myRow = Range("b1").End(xlDown).Row

If myRow 2 Then
Range("K2:m2").AutoFill _
Destination:=Range("K2:M" & myRow)
End If

End Sub

Just check to see what that row is. If it's not bigger than 2, then don't do
it.

"RichardO <" wrote:

Hello all:

I need your help with the following:

Part of my macro includes the portion below. The macro works fine when
I have more than 1 line of data i.e. I have items in row 2, 3 (row 1 is
a heading) e.t.c. But today, my data only includes information for 1
account, i.e. I only have info in row 2.

When I ran the macro, I was getting an error, and when I pressed debug
the "Selection.AutoFill Destination:=Range("K2:M" & ro$)" was
highlighted in my code. So I thought maybe because I only have 1 row
of data in row 2 and the code does not have anywhere else to autofill,
what do I add to this code so that when there is only 1 line of data
i.e. in row 2 only, it should not autofill? Do you think that will
solve the problem?

Thanks you so much for helping me.

Range("K2").Select
ActiveCell.FormulaR1C1 =
"=IF(ISNA(VLOOKUP(RC[-9],bast,1,0)),"""",""YES"")"
Range("L2").Select
ActiveCell.FormulaR1C1 = _

"=IF(ISNA(VLOOKUP(RC[-10],bast,1,0)),"""",VLOOKUP(RC[-10],bast,7,0))"
Range("M2").Select
ActiveCell.FormulaR1C1 = _

"=IF(ISNA(VLOOKUP(RC[-11],ban,15,0)),"""",IF(VLOOKUP(RC[-11],ban,15,0)<=0,""Past"",IF(VLOOKUP(RC[-11],bast,15,0)5,""On"",""Yep"")))"
Range("b1").Select
Selection.End(xlDown).Select
ro$ = ActiveCell.Row
Range("K2:M2").Select
Selection.AutoFill Destination:=Range("K2:M" & ro$)

---
Message posted from http://www.ExcelForum.com/

--

Dave Peterson