Newbie Question: Need to autofill a range of cells based on values from another worksheet

[historygirl28]

I'm trying to autofill a range in Sheet1 with an formula that calls data from Guests (another worksheet in the same workbook). But I need it to end at the last row of data from the Guests worksheet. The macro runs currently, but I don't want to have to change the range every time. And this macro will be used on varying numbers of rows.

Sub SpsName()
'
' SpsName Macro
'  
    ActiveCell.FormulaR1C1 = _
        "=IF(AND(ISTEXT(Guests!RC[47]),ISNUMBER(SEARCH(""FALSE"",Guests!RC[48]))),Guests!RC[47],"" "")"
    Range("C2").Select
    Selection.AutoFill Destination:=Range("C2:C231"), Type:=xlFillDefault
        Columns("C:C").EntireColumn.AutoFit
   
End Sub

 

[Michael M]

Maybe this

Sub SpsName()
Dim lr As Long
lr = Sheets("Guests").Cells(Rows.Count, "AV").End(xlUp).Row 'Change column reference to suit
Range("C2:C" & lr).Formula = "=IF(AND(ISTEXT(Guests!AU2),ISNUMBER(SEARCH(""FALSE"",Guests!AV2))),Guests!AU2,"" "")"
Columns("C:C").EntireColumn.AutoFit
End Sub

 

[historygirl28]

Thanks so much! Works perfectly.

 

[Michael M]

pleasure..