Simple Macro Req. to replace Fill Down

[thequiff]

I have an equation in B1 ie "=A1-95"

I want to fill down as far as Column A is filled out.

Initially I was selecting A so that the whole column was selected and then edit > Fill down.

This method starts to make the sheet very large.

How can I can write a macro that will do this? I would need to work on some sheets and not others. So sheet2 sheet4 sheet 9 etc.

it would also need to automatically happen as opposed to a button to run the macro.

Many Thanks

Matt

[jraj1106]

Hi

Try this:

Sub FORMULADATA()
    Dim rcnt As Long
    
    rcnt = Range("A" & Rows.Count).End(xlUp).Row
    
    For i = 1 To rcnt
        If Range("A" & i).Value <> "" Then
            Range("B" & i).Value = Range("A" & i).Value - 95
        End If
    Next
End Sub

[thequiff]

2 things? (very new to this stuff)

How do I assign the macro to a worksheet (not whole workbook, and without buttons so that runs automatically)

also the equation I want to use looks like this

=HYPERLINK(CONCATENATE("file:///\\uranus\dckgen\Brands\Zoom\Brand - Zoom\Upload Photos\2012\week",A1,"\BHS_WK",A1,"_LR\",C1,".jpg"))