Pasting formulas into a dynamic range

[DJL]

Hi

I know a little VBA and am ok recording macros but I cannot seem to integrate the two very well. I get sales data on a monthly basis and have recorded a macro which contains a formula to calculate a new parameter. The macro works fine and the relevant section of code is


Range("U2").Select
ActiveCell.FormulaR1C1 = _
"=IF(AND(RC[-8]>=RC[-2],RC[-8]>0),""NEW"",IF(RC[-2]=0,""LOST"",""OK""))"
Selection.Copy
Range("U3:U864").Select
Activesheet.Paste

The next month's data will extend beyond row 864 and I want the macro to take this into account. I know I can use the fuction COUNTIF to calculate the number of rows with data in but I cannot write the code to then identify this range for the paste function. After numerous sites etc I thought a FOR...NEXT loop would work and set up the following code


Range("U2").Select
ActiveCell.FormulaR1C1 = _
"=IF(AND(RC[-8]>=RC[-2],RC[-8]>0),""NEW"",IF(RC[-2]=0,""LOST"",""OK""))"
Selection.Copy
Dim iLoop As Integer
Dim i As Integer
iLoop = WorksheetFunction.Countif(Columns(1), "*")
For i = 3 To iLoop
Range(Columns(21), i).Select
Selection.Copy
Next i

However I get a message Run-time error '1004', Method 'Range of object'_Global failed...which to be honest means nothing to me.

I think that the logic of what I am trying to do is fine but the syntax is flawed. Any help or guidance would be greatly appreciated.

DJL

:banghead:

[Bob Phillips]

Hi

I know a little VBA and am ok recording macros but I cannot seem to integrate the two very well. I get sales data on a monthly basis and have recorded a macro which contains a formula to calculate a new parameter. The macro works fine and the relevant section of code is


Range("U2").Select
ActiveCell.FormulaR1C1 = _
"=IF(AND(RC[-8]>=RC[-2],RC[-8]>0),""NEW"",IF(RC[-2]=0,""LOST"",""OK""))"
Selection.Copy
Range("U3:U864").Select
Activesheet.Paste

The next month's data will extend beyond row 864 and I want the macro to take this into account.

No, the logic is flawed too.

COUNTIF is ued to count items matching some criteria, not the number of cells. For that you would use COUNT or COUNTA.

And looping through is inefficient. Far better to work out the last row and dump all over those cells in one hit, similar to the process that you tried in your first code snippet (although I didn't bother checking whether it worked or not).


With Range("U2")
.FormulaR1C1 = _
"=IF(AND(RC[-8]>=RC[-2],RC[-8]>0),""NEW"",IF(RC[-2]=0,""LOST"",""OK""))"
.AutoFill .Resize(Cells(Rows.Count, "A").End(xlUp).Row - 1)
End With

[DJL]

That is excellent.

many thanks my friend

:beerchug:

[johnske]

Hi DJL, and welcome to VBAX. :hi:

Just a note - When your problem has been solved, it helps everyone if you use the thread tools above to mark the thread as solved.

Regards,
John :beerchug: