Do while loop -problem with blank cells

[1021557]

Hi, Can anyone help me with this. I want to write a formula in column AB if the cell in AA is not blank. The macro below works fine until it hits a blank, the data I have has some blank rows in column AA with more data
below, then another blank and more data. The problem is that it works perfectly until it hits the first blank row and then it stops. Is there a way to loop through the entire column of AA such that the loop that puts a formula in AB will continue until the last non blank cell in AA. Sincere thanks for any help offered.

Code is as follows:

' Margin Macro

Dim i As Integer

i = 4
    
    
    
Range("AB4").Select
 
Do While Not IsEmpty(ActiveCell.Offset(0, -1))

    ActiveCell.FormulaR1C1 = _
        "=(-RC[-6]/SUM(RC[-24]+RC[-22]+RC[-20]+RC[-18]+RC[-16]+RC[-14]+RC[-12]+RC[-10]+RC[-8]))-1"
    ActiveCell.Offset(1, 0).Select
    
    
    i = i + 1
Loop
    
End Sub

 

[gravanoc]

Do you mean the entire column as in all 1048576 rows? Or do you have a natural endpoint in mind?

 

[1021557]

Hi, I mean all as the report will run each month and sometimes it will be longer than others. However, it would never be longer than 2,000 rows.

 

[gravanoc]

Please try this:

Sub Tests()

    Dim ws As Worksheet
    Dim rng As Range
    Dim startRng As Range
    Dim lRow As Long
    Dim i As Long
    Dim lastCell As Range
    
    Set ws = Sheets("Sheet1")
    Set rng = ws.Range("AA4:AA1048566")
    Set startRng = ws.Range("AA4")
    
    Set lastCell = rng.Find(What:="*", After:=startRng, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, _
        SearchDirection:=xlPrevious, MatchCase:=False)
    
    lRow = lastCell.Row
    
    For j = 0 To lRow - 1
        If startRng.Offset(j, 0) <> "" Then
        
            startRng.Offset(j, 1).FormulaR1C1 = _
                "=(-RC[-6]/SUM(RC[-24]+RC[-22]+RC[-20]+RC[-18]+RC[-16]+RC[-14]+RC[-12]+RC[-10]+RC[-8]))-1"
                
        End If
        
    Next
    
End Sub

 

[1021557]

Hi, That worked, all I can say is Wow!
Can you tell me if After, Lookin, etc,. are VBA commands as I've not come across them before. I'm trying to understand what's going on in the code.
Again, thanks so much for your help, I really appreciate it .

 

[gravanoc]

Hi, That worked, all I can say is Wow!
Can you tell me if After, Lookin, etc,. are VBA commands as I've not come across them before. I'm trying to understand what's going on in the code.
Again, thanks so much for your help, I really appreciate it .

They are just part of the Find method. When you press Ctrl + F while in normal Excel, those are additional options that you'd see if you clicked on the Options button.

 

[1021557]

Super, thanks again I really appreciate it.