Solved: VBA Loop Using FormulaR1C1

[JP2R]

Greetings All -

I was scanning through the information/questions/help on Loops and using FormulaR1C1 - but just can't seem to get my brain around it...

I'm trying my hand at writing a VBA script to automate/massage my data -
In column "AA" I have a Date/Time stamp - In column "AB" I would like to fill down the formula "=INT()" - Where the loop looks to column "AA" - sees if it is empty or if there is something there - if there isn't moves to the next - if there is - then put that date in that cell.

Hope that makes sense...

01 AA AB
02 09/30/2008 13:32:00 =INT(AA2)

This is how it looks now - because I filled the column in manually
with the result in "AB" showing just the date.

I'm sure this is so simple for most of you - but I haven't a clue.

Thanks so much!

-- Monk

[JP2R]

Would something like this work - and if so - what am I doing wrong with it?

Sub ARS_Macro5()
'
' ARS_Macro5 Macro

Do

ActiveCell.FormulaR1C1 = "=INT(RC[2],RC[27])"

ActiveCell.Offset(1, 0).Select

Loop Until IsEmpty(ActiveCell.Offset(0, 1))

End Sub

[Digita]

Hi Monk,

Try:

Sub test()
[AA1].Select
Do
i = i + 1
ActiveCell.Offset(i, 1).FormulaR1C1 = "=INT(RC[-1])"
Loop Until IsEmpty(ActiveCell.Offset(i))
ActiveCell.Offset(i, 1).Delete
End Sub


Hope this helps.


kp

[Bob Phillips]

No need for a loop



Dim LastRow As Long

LastRow = Cells(Rows.Count, "AA").End(xlUp).Row
With Range("AB2").Resize(LastRow - 1)
.FormulaR1C1 = "=INT(RC[-1])"
.NumberFormat = "dd/mm/yyyy"
End With

[JP2R]

Oh Distinguish Lord of VBAX -
Thank you so much - I will try your solution.

I really do appreciate all the help and support from this group - I've learned enough to be frustrated (and confused) - and sure am thankful for those who can show me the way out of a paper bag - LOL

I will post 'solution' - once I can apply this recommendation.

-- Monk