Results 1 to 9 of 9
  1. #1
    Lounger
    Join Date
    Mar 2002
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Wrong Syntax (97 sr2)

    I am working with a 12 column database and trying to use the following bit of code to plug 4 formulas into 4 cells after the end of each row.

    Unfortunately each FormulaR1C1 = statement is filling 12 cells instead of only 1.

    Here is the code:
    Range("A3").Select ' First Column Header is A3.

    For Each rw In Selection.CurrentRegion.Rows

    ' Calc RunSize
    rw.Offset(0, 21).FormulaR1C1 = "=( RC2 - RC1 ) + 1"
    ' Calc GapSize
    rw.Offset(0, 22).FormulaR1C1 = "=( RC1 - R[-1]C2 ) - 1"
    ' Test RunSize
    rw.Offset(0, 23).FormulaR1C1 = "=not( RC22 > 0 )"
    ' Test GapSize
    rw.Offset(0, 24).FormulaR1C1 = "=not( RC23 = 0 )"
    Next


    What is wrong with my syntax?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Wrong Syntax (97 sr2)

    The variable rw refers to a whole row of the region you work with. You stated that your region has 12 columns; therefore rw contains 12 cells.
    You can use something like

    rw.Cells(1,22).FormulaR1C1 = ...
    rw.Cells(1,23).FormulaR1C1 = ...
    etc.

    to refer to 4 single cells.

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Wrong Syntax (97 sr2)

    If the table has 12 columns, then that For Each statement is going to step accross all 12 columns, not just down the rows.

    The code below should be close to what you need. It may need to be tweeked a little if the row above A3 is not empty.

    <pre>Dim lLastRow As Long, I As Long
    lLastRow = ActiveSheet.Range("A3").CurrentRegion.Rows.Count - 1
    With ActiveSheet.Range("A3")
    For I = 0 To lLastRow
    ' Calc RunSize
    .Offset(I, 21).FormulaR1C1 = "=( RC2 - RC1 ) + 1"
    ' Calc GapSize
    .Offset(I, 22).FormulaR1C1 = "=( RC1 - R[-1]C2 ) - 1"
    ' Test RunSize
    .Offset(I, 23).FormulaR1C1 = "=not( RC22 > 0 )"
    ' Test GapSize
    .Offset(I, 24).FormulaR1C1 = "=not( RC23 = 0 )"
    Next I
    End With
    </pre>

    Legare Coleman

  4. #4
    Lounger
    Join Date
    Mar 2002
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Wrong Syntax (97 sr2)

    That did the trick Hans... Thank you! <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Wrong Syntax (97 sr2)

    Hans solution will "solve the problem." However, if you step through that code, I think that you will find that it is rather inefficient. It is going to loop through the same 4 cells in each row 12 times.
    Legare Coleman

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Wrong Syntax (97 sr2)

    Legare is right. I just pointed out why the original code went wrong. I didn't have time to suggest an efficient solution - Legare provided that. If your table is small, it won't make much difference, but if it gets larger, you might notice a delay when using the code with entire rows.

  7. #7
    New Lounger
    Join Date
    Apr 2002
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Wrong Syntax (97 sr2)

    I'm a little new here but I think you both are wrong on this.

    I created 5 rows of test data (with 36 columns each) plus a headers row, then...

    I tried HansV's code because it looked like it might solve a problem that I am working on. When I step thru the code it only loops through each statement once per row (5 loops).

    I then tried the original code with "Offset()" instead of "Cells()" and it looped thru the statements also only once per row (5 loops) but generated a multi-cell-range copy instead of a single-cell-range copy.

    Apparently the "For Each myrow In Range(myrange).Rows" syntax understands that it is supposed to do only one loop per row, but then Offset() and Cells() do not behave the same at all - and I thought they fairly much were supposed to.

    I wonder what other differences they have?

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Wrong Syntax (97 sr2)

    Upload your workbook and we will tell you what is happening. I can tell you that Hans code does loop twelve times for each row with this sheet, and my code does not do any copy at all, much less a "multi-cell-range copy".
    Legare Coleman

  9. #9
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Wrong Syntax (97 sr2)

    I had to do something very similar to this to enter four formulas per row on a datafile imported from a G/L package monthly. I write the formulas in once and then select the range they are in and copy / paste it down the used range in the s/sheet instead of iterating from the starting row to the last used row in the range, typically about row 1500-1700 or so. I never thought of stepping through row by row - but on a big s/sheet I suspect copy / paste will be faster than iteration.

Posting Permissions

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