DJL
07-11-2005, 01:03 PM
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:
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: