Wrong Syntax (97 sr2)

Topic

New Reply

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?

Reply | Quote

Replies

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.

Reply | Quote

That did the trick Hans… Thank you!

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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?

Reply | Quote

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”.

Reply | Quote

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.

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

Reply | Quote

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.

Reply | Quote