Adding in a formula (Excel 2002)

Topic

New Reply

Hey guys,

Here is my challenge. I am taking a CSV from one program manipulating it and uploading into another program. Main difference is format and some required fields for the second program.
I have pasted below part of the macro I am struggling with. I need to add the numbers sequentially to this formula (i.e. 1,2,3,4,5……) I wanted to do y=y+1 in the formula but it keeps blowing up on me.

Anyone got an idea so I can get the number sequential in this column??

For x = 1 To 686

ActiveCell.Offset(0, 0).Range(“A1”).Select
ActiveCell.FormulaR1C1 = “RUN1”
ActiveCell.Offset(0, 1).Range(“A1”).Select
ActiveCell.Formula = *********** <—————–Need help here
ActiveCell.Offset(0, 2).Range("A1").Select
ActiveCell.FormulaR1C1 = "100"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=MID(RC[16],2,8)"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=MID(RC[15],11,5)"
ActiveCell.Offset(0, 10).Range("A1").Select
ActiveCell.FormulaR1C1 = "GL"
ActiveCell.Offset(0, 3).Range("A1").Select
ActiveCell.FormulaR1C1 = "=RIGHT(RC[1],4)&LEFT(RC[1],4)"
ActiveCell.Offset(0, -6).Range("A1").Select
ActiveCell.FormulaR1C1 = "=IF(RC[10]=""D"",RC[9],""-""&RC[9])"
ActiveCell.Offset(1, -11).Range("A1").Select

Next x

Thanks for any help.

Dan

Reply | Quote

Replies

Hi Dan,
I’m not entirely sure what you mean. Do you just want sequential numbers in that column? If so, can you not just use x? I.e.:

For x = 1 To 686

with ActiveCell
    .FormulaR1C1 = "RUN1"
    .Offset(0, 1).Formula = x
    .Offset(0, 3).Formula = "100"
    .Offset(0, 4).FormulaR1C1 = "=MID(RC[16],2,8)"
    .Offset(0, 5).FormulaR1C1 = "=MID(RC[15],11,5)"
    .Offset(0, 15).Formula = "GL"
    .Offset(0, 18).FormulaR1C1 = "=RIGHT(RC[1],4)&LEFT(RC[1],4)"
    .Offset(0, 12).FormulaR1C1 = "=IF(RC[10]=""D"",RC[9],""-""&RC[9])"
    .Offset(1,1).Select
End With

Next x

Note: I may have got some of the columns wrong when removing all the cell selecting code so you’ll need to check it! Also, you should be able to avoid the ActiveCell and Select methods, but I don’t know the relevant locations, so I can’t replace that bit.
Does that help?

Reply | Quote

Works perfect!! Thank you so much.

Dan……………….. who will one day get the hang of VBA

Reply | Quote