Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Mar 2002
    Location
    Ventura, California, USA
    Posts
    56
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Adding in a formula (Excel 2002)

    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

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Adding in a formula (Excel 2002)

    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.:
    <pre>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
    </pre>

    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?
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Star Lounger
    Join Date
    Mar 2002
    Location
    Ventura, California, USA
    Posts
    56
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding in a formula (Excel 2002)

    Works perfect!! Thank you so much.

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •