Results 1 to 10 of 10
  1. #1
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Fill in of Cell with Macro (Excel 97)

    I have the below columns:
    The data is entered manually, however the column cH is calculated.
    cA cB cC cD cE cF cG cH
    11/5/02 Deposit Fundraiser (Fall) $2,167.50 $28,388.28
    11/6/02 Deposit Fundraiser (Fall) $534.00 $28,922.28

    The below macro inserts additional rows. I would like the macro to automatically fill the column cH with the calculation.
    the calc is : =IF(AND(G67="",F67=""),"",SUM($G$3:G67)-SUM($F$3:F67))

    Sub insert_lines_GF()
    '
    ' Insert additional rows above the Balance cell for
    ' the General Fund so that balance continues to work properly
    '
    Application.Goto Reference:="Balance_GF"
    ActiveCell.Rows("1:5").EntireRow.Select
    Selection.Insert Shift:=xlDown
    Selection.borders(xlDiagonalDown).LineStyle = xlNone
    Selection.borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.borders(xlEdgeLeft)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = 48
    End With
    With Selection.borders(xlEdgeTop)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = 48
    End With
    With Selection.borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = 48
    End With
    With Selection.borders(xlEdgeRight)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = 48
    End With
    With Selection.borders(xlInsideVertical)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = 48
    End With
    With Selection.borders(xlInsideHorizontal)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = 48
    End With
    Application.Goto Reference:="Balance_GF"

    ' ActiveCell.FormulaR1C1 = "=SUM(R[-23]C[2]:R[-1]C[2])-SUM(R[-23]C[1]:R[-1]C[1])"
    ActiveCell.Offset(-5, -3).Select

    End Sub

    Thanks,
    Brad

  2. #2
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Fill in of Cell with Macro (Excel 97)

    It's tough figuring out the R1C1 translation of the formula that you want. One easy way is to record a macro and enter the formula. When you look at the macro recorded, it will be translated into R1C1 syntax. If you just want to fill the column, then use the AutoFill method. Again, recording a macro of an fill (drag the lower-right corner of the cell down) will show you the code to uses. HTH --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  3. #3
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Long Beach, California, USA
    Posts
    233
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Fill in of Cell with Macro (Excel 97)

    Brad,

    You might play with something like this to clean up the formatting portion of your sub. The range would of course be the additional rows that are inserted.

    <pre>Sub TestBorders()
    Dim i

    Range("B515").Select
    With Selection
    For Each i In Array(xlEdgeLeft, xlEdgeTop, xlEdgeBottom, xlEdgeRight, _
    xlInsideVertical, xlInsideHorizontal)
    .Borders().LineStyle = xlContinuous
    .Borders().Weight = xlThin
    .Borders().ColorIndex = 48
    Next i
    End With
    End Sub
    </pre>


  4. #4
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Fill in of Cell with Macro (Excel 97)

    Actually, the single line
    <pre> Selection.BorderAround ColorIndex:=48</pre>

    does the same thing!
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  5. #5
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Long Beach, California, USA
    Posts
    233
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Fill in of Cell with Macro (Excel 97)

    I just love this place. That is awesome Sam...Thanks! (cleans up my stuff even more)

  6. #6
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Fill in of Cell with Macro (Excel 97)

    Yep, I figured that the team who wrote the macro recorder gets a commission based on the number of lines of code that it produces. <img src=/S/bash.gif border=0 alt=bash width=35 height=39>
    I was severely irritated at them when I discovered BorderAround.
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  7. #7
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Fill in of Cell with Macro (Excel 97)

    Hi Sammy,

    Translating the formula to R1C1 syntax is actually quite easy. Using the example given:
    =IF(AND(G67="",F67=""),"",SUM($G$3:G67)-SUM($F$3:F67))
    key this into, say, A1, then go into Tools|Options|General and check 'R1C1 Reference Style'. Click OK. Back in the worksheet, the formula has become:
    =IF(AND(R[66]C[6]="",R[66]C[5]=""),"",SUM(R3C7:R[66]C[6])-SUM(R3C6:R[66]C[5]))

    The difficulty I'm having turning the formula into a macro for Brad is not so much the reference style, but knowing how this mixture of absolute and relative referencing is meant to be used in the context of
    <hr>automatically fill the column cH<hr>
    Just how one does this depends somewhat on which row in the column being filled in the example refers to. If, for example, it's in row 3, one could just paste it in once and copy it down as far as is needed. The question that then needs to be answered is:
    How is the macro meant to know where to stop filling the column? Presumably this is by reference to the contents of another cell/row/column.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  8. #8
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Fill in of Cell with Macro (Excel 97)

    Hi Brad,
    Please seem my post to Sammy about
    <hr>automatically fill the column cH<hr>
    If you could answer that, we should be put a macro together to do that.
    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  9. #9
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Fill in of Cell with Macro (Excel 97)

    Right and the tools|options is simpler! I'm usually recording a macro to see what it looks like, so I copy the contents of the address bar , start the recorder, select the cell and paste the formula into the address bar. Hopefully, Brad is happy. If not, we need more info, Brad.
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  10. #10
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Fill in of Cell with Macro (Excel 97)

    All,
    I am trying to digest all of the replies to my question. As soon as I can edit my macro, I will reply.

    Thanks to all so far! i will atempt to answer the questions about the auto fill shortly.

    Brad

Posting Permissions

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