VBA to edit formulas in column AA -edit value in cell and move down one row

[1021557]

Hi, I have data form another system each month and it's in column AA, the number of rows will vary each month. What I need to do is start in cell AA2 -take the value in the cell divide it by 100 and multiply by minus 1, move done to AA3 and do the same, etc. until the last value is reached. I've got this code that does something similar but it's taking the values in AA2 and creating a new column in AB2. I think I'm pretty close to getting the answer but can't get the RC values correct. Do I need another variable like:

Dim Num as Long

'AA value Num = num/100*-1
Next value etc.?

Any help or advice greatly appreciated.
=============

Sub Margin()
'
' Margin Macro

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("Month")
    Set rng = ws.Range("AA2:AA1048566")
    Set startRng = ws.Range("AA2")
    
    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[-1]/100)*-1)"
                
        End If
        
    Next

End Sub

 

[jkpieterse]

This code uses the trick to copy a number and then do a paste-special, divide over your current data:

    With Worksheets("Month")
        .Range("AB1").Value = -100
        .Range("AB1").Copy
        .Range(.Range("AA2"), .Range("AA" & .Rows.Count).End(xlUp)).PasteSpecial Paste:=xlPasteValues, Operation:=xlDivide, _
                                                                                 SkipBlanks:=False, Transpose:=False
    End With
    Application.CutCopyMode = False

 

[1021557]

Hi, That worked, thanks so much, it looks so easy! Does the
.Range(.Range("AA2"), .Range("AA" & .Rows.Count).End(xlUp)) line just use paste special divide, is that how it works? I'm juts trying to learn a bit more as I go along.

Again thanks a million, I really appreciate it.

 

[Peter_SSs]

Here is another way that doesn't require the helper cell. If there are any blanks among the column AA range, this will leave them blank rather than fill them with a zero.

With Worksheets("Month")
  With .Range("AA2", .Range("AA" & .Rows.Count).End(xlUp))
    .Value = Evaluate(Replace("if(#="""","""",-#/100)", "#", .Address))
  End With
End With

If you do decide to stick with the post #2 code then you may want to add .Range("AB1").ClearContents just before the "End With" statement so that the helper cell is cleared.