Formulas in loops

[Ealglez]

Dear All,

After a lot of effort (I am a brand new in VBA) I have managed to create a macro to do the following:
1. Copy time vs. Volume data (with variable lengths). A18:B18
2. Calculate deltaT and deltaVolume. (A19:B19).
3. For a constant DR located in cell C6 in the same w/s the following is calculated for the entire set of data:
a) From E19 down to last row: Ei = Di-($DR$*Ci)
b) From E19 down to last row: Fi = IF((Fi-1+Ei)<0,0, Fi-1+Ei).
c) and d) other formulas.
4. The block of columns from E to I will be replicated according to the number of constants DR I have.

The problem is that, when I move the formulas across, the formula in Colum E should refer to columns C and D always. At the moment, due to the R1C1 formula, when the block of cells is replicated, the formula is wrong as it will refer to columns H and I.

This is the macro I have crated:

Range("C19").Select
ActiveCell.FormulaR1C1 = "=RC[-2]-R[-1]C[-2]" 'Delta T
Range("D19").Select
ActiveCell.FormulaR1C1 = "=RC[-2]-R[-1]C[-2]" 'Delta V
Range("C19:D19").Select
Selection.AutoFill Destination:=Range("C19:D" & lastrow + 16)
Range("C15:D" & lastrow + 16).Select
' calculation for each DR

Dim myColumn As Integer
Dim Counter As Integer

Counter = 0
myColumn = 1
Do Until (Cells(6, myColumn) = "")
Cells(16, myColumn + 4).Select
Selection.Value = "DeltaV-DR [m3]"
Range(Cells(16, myColumn + 4), Cells(17, myColumn + 4)).Select
Cells(16, myColumn + 5).Select
Selection.Value = "SUM(DeltaV-DR) [m3]"
Range(Cells(16, myColumn + 5), Cells(17, myColumn + 5)).Select

Cells(16, myColumn + 6).Select
Selection.Value = "DeltSurge/DeltT [m3/h]"
Range(Cells(16, myColumn + 6), Cells(17, myColumn + 6)).Select

Cells(16, myColumn + 7).Select
Selection.Value = "Slug Vol. [m3]"
Range(Cells(16, myColumn + 7), Cells(17, myColumn + 7)).Select

Cells(16, myColumn + 8).Select
Selection.Value = "Slug Duration [h]"
Range(Cells(16, myColumn + 8), Cells(17, myColumn + 8)).Select

' DeltaV-Drain Rate = accumulated volume in the separator
Cells(19, myColumn + 4).Select
Selection.FormulaR1C1 = "=RC[-1]-(R6C3*RC[-2])"
Selection.AutoFill Destination:=Range("E19:E" & lastrow + 16).Offset(, 5 * Counter)

'Accumulated volume in the separator (only positive values)
Cells(19, myColumn + 5).Select
Selection.FormulaR1C1 = "=IF((R[-1]C+RC[-1])<0, 0, (R[-1]C+RC[-1]))"
Selection.AutoFill Destination:=Range("F19:F" & lastrow + 16).Offset(, 5 * Counter)

'Delta Surge / Delta time
Cells(19, myColumn + 6).Select
Selection.FormulaR1C1 = "=(RC[-1]-R[-1]C[-1])/RC[-4]"
Selection.AutoFill Destination:=Range("G19:G" & lastrow + 16).Offset(, 5 * Counter)

' Slug Volume
Cells(19, myColumn + 7).Select
Selection.FormulaR1C1 = "=IF(AND(RC[-2]>0, RC[-1]>0), R[-1]C+RC[-4],0)"
Selection.AutoFill Destination:=Range("H19:H" & lastrow + 16).Offset(, 5 * Counter)

'Slug Duration
Cells(19, myColumn + 8).Select
Selection.FormulaR1C1 = "=IF(RC[-1]=0,0, R[-1]C+RC[-6])"
Selection.AutoFill Destination:=Range("I19:I" & lastrow + 16).Offset(, 5 * Counter)

myColumn = myColumn + 5
Counter = Counter + 1
Loop

End Sub


Can anyone please let me know how can I change the formula
FormulaR1C1 = "=RC[-1]-(R6C3*RC[-2])"
so when the loop copies across the set of columns:
a) this formula still refers to columns C and D. And
b) it takes the correct constant that will be now in D6 instead of C6 (for loop 2 in E6 and so).

Thank you very much for your help.

E

 

[jasonb75]

If you had set the correct columns as relative or absolute before recording the macro then you would have had the formula you needed.

RC[-1] is a relative reference, the same as using something like =A1. (no number between R and C means use the same row that the formula is in. [-1] after the C means 1 column left of the formula cell). This reference type will change as you copy the formula to different rows and columns.

R6C3 is an absolute reference, the same as $C$6, or Row 6, Column 3. This reference type will stay the same as you copy to other rows and columns.

See if that gets you going on the right track.

Please post back if you need more help.

 

[Ealglez]

Hi,

Thanks for the reply.

I understand what you mean. let me explain myself better:

I have this formula in column E19:E: FormulaR1C1 = "=RC[-1]-(R6C3*RC[-2])"
that works fine for the first constant in cell C6.

Now, I need to replicate the formulas in columns E to I for every constant in C6:C.

The problem is that, when the loop go through the second time, column E is replicated in column J
Due to the relative reference R1C1, the formulas in column J refers to columns H and I, which is wrong because it should be columns D and C.
Due to the absolute reference R6C3, the constant is wrong, since it is still C6 instead of D6.
I have tried to change the formula to something like
FormulaR1C1 = "=R$C[-1]-(R6C3*R$C[-2])"
But it is not working.
I was thinking maybe the R1C1 is not good for this, but I cannot find the way to define variables in excel such as
A = Range("C19:C" & lastrow + 16)
B = Range("D19:D" & lastrow + 16)
C = Range("C6:C6").Offset(, Counter2)
And try to replicate the formula Ok in the loop

Thanks very much for your help!
E

 

[jasonb75]

Try

FormulaR1C1 = "=RC4-(R6C[-3]*RC3)"

Not sure if I'm following what you need on the C6 to D6 part.

 

[Ealglez]

Ok, I have tried
FormulaR1C1 = "=RC4-(R6C3*RC3)"
first, and it works for the time and the volume (RC3 and RC4 respectively). Now when the loop goes the second time, in column J the reference are column D and C as it should be.

The problem is the constant. When the loop start, this constant is in cell C6 (in a table). Hence the absolute reference R6C3 when I recorded the macro.
However, for the loop number 2, 3, 4, ... this constant is in cells D6, E6, F6...
I cannot figure out how I can change the formula so this constant ALSO changes whit the loop

Oh! I have tried yours as well, but the constant is incorrect as it will take H6 in the second loop instead of D6

this is the table I have:
Min DR DR 1 DR 2 DR3 DR 4 DR 5 DR 6 DR 7 DR 8
Drain rate DR (m3/h) = 154.67 200 210 220 230 240 250 300 400 ---> row6
--------------------------------------------------------------
Column -- C D E F G H I J K

I hope this helps

Thanks again!

 

[jasonb75]

Now I understand

FormulaR1C1 = "=RC4-(R6C" & 3 + counter & "*RC3)"

 

[Ealglez]

Great - it does work!!

Thank you so so much!!
E

 

[jasonb75]

A little tidy up for you, this should run a bit quicker.

Option Explicit
Sub test()
Dim mycolumn As Long, counter As Long, lastrow As Long
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
counter = 0
mycolumn = 1
With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual

With Range("C15:D" & lastrow + 16)
    .FormulaR1C1 = "=RC[-2]-R[-1]C[-2]"
End With

Do Until (Cells(6, mycolumn) = "")
    Cells(16, mycolumn + 4).Value = "DeltaV-DR [m3]"
    Cells(16, mycolumn + 5).Value = "SUM(DeltaV-DR) [m3]"
    Cells(16, mycolumn + 6).Value = "DeltSurge/DeltT [m3/h]"
    Cells(16, mycolumn + 7).Value = "Slug Vol. [m3]"
    Cells(16, mycolumn + 8).Value = "Slug Duration [h]"
 
 ' DeltaV-Drain Rate = accumulated volume in the separator
    Cells(19, mycolumn + 4).Resize(lastrow - 3).FormulaR1C1 = "=RC4-(R6C" & 3 + counter & "*RC3)"
 
 'Accumulated volume in the separator (only positive values)
    Cells(19, mycolumn + 5).Resize(lastrow - 3).FormulaR1C1 = "=IF((R[-1]C+RC[-1])<0, 0, (R[-1]C+RC[-1]))"
 
 'Delta Surge / Delta time
    Cells(19, mycolumn + 6).Resize(lastrow - 3).FormulaR1C1 = "=(RC[-1]-R[-1]C[-1])/RC[-4]"
 
 ' Slug Volume
    Cells(19, mycolumn + 7).Resize(lastrow - 3).FormulaR1C1 = "=IF(AND(RC[-2]>0, RC[-1]>0), R[-1]C+RC[-4],0)"
 
 'Slug Duration
    Cells(19, mycolumn + 8).Resize(lastrow - 3).FormulaR1C1 = "=IF(RC[-1]=0,0, R[-1]C+RC[-6])"
 
    counter = counter + 1
    mycolumn = mycolumn + 5
Loop

    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
End With
End Sub

 

[Ealglez]

Hey, thanks very much!!!

It does go much faster!!

Maybe you can help me in something else - much smaller problem though!

I have created a further macro (same workbook) for a table that has to the replicated according to a list of cases.
I have put the offset function (duplicate cells in the same column and 6 rows below) and it does work OK to name the cells, but for some reason, when it comes to the format merge cells (ex "B22:C22" ) the offset seems to not pick up the range it has to be. Any idea why? I have used the same thing before and it has worked OK...

This is the code:

Sub Output_table()
'
' Macro2 Macro
'

Sheets("Calc Sheet").Select

Dim counter As Integer
Dim myCell As Range, myrange As Range
Set myrange = Sheets("Cases_Input").Range("A4")
Set myrange = Range(myrange, myrange.End(xlDown))

Sheets("Cases_Input").Select

counter = 0
For Each myCell In myrange

Sheets("Calc Sheet").Select
Range("B21").OFFSET(6 * counter, 0).Select
Selection.Value = myCell
Range("B21:C21").OFFSET(6 * counter, 0).Select
With Selection
.MergeCells = True
.HorizontalAlignment = xlLeft
End With

Range("B22").OFFSET(6 * counter, 0).Select
ActiveCell.FormulaR1C1 = "Drain rates, m3/h"
Range("B22:C22").OFFSET(6 * counter, 0).Select
With Selection
.MergeCells = True
.HorizontalAlignment = xlLeft
End With

Range("B23").OFFSET(6 * counter, 0).Select
ActiveCell.FormulaR1C1 = "Max. Surge Vol., m3 ="
Range("B23:C23").OFFSET(6 * counter, 0).Select
With Selection
.MergeCells = True
.HorizontalAlignment = xlLeft
End With

Range("B24").OFFSET(6 * counter, 0).Select
ActiveCell.FormulaR1C1 = "Max. Slug Vol., m3 = "
Range("B24:C24").OFFSET(6 * counter, 0).Select
With Selection
.MergeCells = True
.HorizontalAlignment = xlLeft
End With


Range("B25").OFFSET(6 * counter, 0).Select
ActiveCell.FormulaR1C1 = "Max. Slug duration, h = "
Range("B25:C25").OFFSET(6 * counter, 0).Select
With Selection
.MergeCells = True
.HorizontalAlignment = xlLeft
End With

Range("B26").OFFSET(6 * counter, 0).Select
ActiveCell.FormulaR1C1 = "Max. Slug duration, "" = "
Range("B26:C26").OFFSET(6 * counter, 0).Select
With Selection.OFFSET(6 * counter, 0)
.MergeCells = True
.HorizontalAlignment = xlLeft
End With

Range("D21").OFFSET(6 * counter, 0).Select
ActiveCell.FormulaR1C1 = "Min. DR, m3/h"
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
End With

Range("E21:Q21").OFFSET(6 * counter, 0).Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.MergeCells = True
End With
Selection.Value = "Drain Rates (user specified), m3/h"

Range("B21:Q26").OFFSET(6 * counter, 0).Select
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With


Range("E22:Q22").OFFSET(6 * counter, 0).Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With

counter = counter + 1

Next myCell


End Sub


Thanks again for all your help!

E

 

[Ealglez]

hey,

I have manage to solve it by using:
Range("B22").OFFSET(6 * counter, 0).Select
ActiveCell.FormulaR1C1 = "Drain rates, m3/h"
Range(ActiveCell, ActiveCell.OFFSET(0, 1)).Select With Selection
.MergeCells = True
.HorizontalAlignment = xlLeft
End With

For each case. Thanks anyway!

E