Modify cells in every row

[Ignition1]

Hi
I have a lonnnnng list.

I want to create a module within VBE (as I will "Call" it during a longer macro) which will look at each row and do -

A1 = Start Date
G1 = Today's date
Q1 = Income
J1 = Duration (single figure - months).

 

[MrKowz]

Something like this?

Public Sub RearrangeData()
Dim i   As Long, _
    LR  As Long
    
With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LR
    Range("Q" & i).Value = Range("A" & i).Value
    Range("A" & i).Value = Range("G" & i).Value
Next i
With Application
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
End With
End Sub

 

[Ignition1]

If A1 < G1 Then ( Q1 / J1) * ( DATED IF(A1,G1,"m")

Return that new figure into Q1 AND replace A1 with the date in G1.

So basically modifying the value in Q1 and replacing A1. But doing the check for every row (about a thousand rows) and only modifying the rows where A1<G1.

Sorry for posting in 2 messages - for some reason the post wouldn't display properly

 

[Ignition1]

Once again - sorry, I can't post formulas as it doesn't like that. So I've typed out in plain ol' English what I'm trying to do -

If the Start Date (A) is less than Date (G) then in that row, divide Income (Q) by Duration (J) and multiply that amount by the difference in months between the dates in A and G. Then replace the value in Q with this new modified figure, and also replace the date in A with the date in G for that row only.

Skip any row where A is NOT less than G.

 

[MrKowz]

That's a much better explanation. Try:

Public Sub RearrangeData()
Dim i   As Long, _
    LR  As Long
    
With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LR
    If Range("A" & i) < Range("G" & i) Then
        Range("Q" & i).Value = (Range("Q" & i).Value / Range("J" & i).Value) * Evaluate("DATEDIF(" & Range("A" & i).Value & "," & Range("G" & i).Value & ", ""m"")")
Next i
With Application
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
End With
End Sub

 

[Ignition1]

That looks like the one - thanks very much!

I did have a problem on Excel (using the function) with the DATEDIF returning a value which wasn't recognised a number. I'll give this a bash.

I also suppose if I want to replace the date in A then it would be -

If Range("A" & i) < Range("G" & i) Then
        Range("Q" & i).Value = (Range("Q" & i).Value / Range("J" & i).Value) * Evaluate("DATEDIF(" & Range("A" & i).Value & "," & Range("G" & i).Value & ", ""m"")")
        Range("A" & i).Value = (Range("G1").Value