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]

Good to see that you solved your other problem.

A few tips / pointers for you.

First of all, could you please use code tags when you post code to the forum, it makes it far easier to read the post. see http://www.mrexcel.com/forum/board-announcements/515787-forum-posting-guidelines.html#post2545972 for more info.

When you declare variables, Long is preferable to Integer, the maximum value permitted by an Integer type variable is very easy to exceed, in fact your output table code would exceed it and cause an error if there was no data below A4.

Similarly if you need to use decimal values as variables instead of integers, it is preferable to declare them as Double rather than Single. As with Integer and Long, both types will work, but Single is limited to a much smaller range of values.

You don't need to "Select" ranges or sheets every time you want to do something with them. The macro recorder will produce code that is selecting cells because it mimics every action you take while recording, even those that are not required. If you look at the code that I provided for you earlier you will see that I have used "With Range(..)" rather than Selecting the range, and then working with the selection. This is one of the simplest ways to speed up your code.

Another way to speed things up is to use

Sub MyMarco()
With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual

' code to do some stuff goes here

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

The first line stops the code from updating what you see on screen continuously, and just shows you the final result when it is all finished, the second stops formula from being recalculated after each change made by the code.

As you're new to vba I would suggest making sure that your code works before adding this in as you would have to re-enable both manually if you experience any errors in the code.

Lastly, "Option Explicit" at the top of the code module will prevent any hard to trace errors caused by typing errors in variable names, for example

Sub example()
Dim myrow as long
Do Until myrow = 50
myrow = 1
myrw = myrow + 1
Loop
End Sub

In this example, the typo "myrw" will cause the code to run in an infinite loop because "myrow" will never reach 50, using "Option Explicit" will flag an error before the code runs because "myrw" has not been declared as a variable.

I've done a quick re-write of your output table code for you, but I won't post it yet, I thought maybe you would like to use these examples to try and do it yourself first.

Don't be afraid to ask if you're not sure of anything, we all had to start somewhere.

 

[Ealglez]

Hello!

Thanks very much for all that!

OK, I have added some of the thing to the table code. It is much faster and I only see the results after the code in done... although I am sure there are still plenty of lines that can be removed...

here is my attempt:

Sub Output_table()
'
' Macro2 Macro
'
    
 Sheets("Calc Sheet").Select
 
 Dim counter As Long
 Dim Mycell As Range, myrange As Range
 Set myrange = Sheets("Cases_Input").Range("A4")
 Set myrange = Range(myrange, myrange.End(xlDown))
 
 With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
 
 counter = 0
 For Each Mycell In myrange
    
    
    Range("B21").OFFSET(6 * counter, 0).Select
    Selection.Value = Mycell
    Range(ActiveCell, ActiveCell.OFFSET(0, 1)).Select
    With Selection
        .Font.Bold = True
        .MergeCells = True
        .HorizontalAlignment = xlLeft
    End With
       
    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
    
    Range("B23").OFFSET(6 * counter, 0).Select
    ActiveCell.FormulaR1C1 = "Max. Surge Vol., m3 ="
    Range(ActiveCell, ActiveCell.OFFSET(0, 1)).Select
    With Selection
        .MergeCells = True
        .HorizontalAlignment = xlLeft
    End With
    
    Range("B24").OFFSET(6 * counter, 0).Select
    ActiveCell.FormulaR1C1 = "Max. Slug Vol., m3 = "
    Range(ActiveCell, ActiveCell.OFFSET(0, 1)).Select
    With Selection
        .MergeCells = True
        .HorizontalAlignment = xlLeft
    End With
    
    Range("B25").OFFSET(6 * counter, 0).Select
    ActiveCell.FormulaR1C1 = "Max. Slug duration, h = "
    Range(ActiveCell, ActiveCell.OFFSET(0, 1)).Select
    With Selection
        .MergeCells = True
        .HorizontalAlignment = xlLeft
    End With
        
    Range("B26").OFFSET(6 * counter, 0).Select
    ActiveCell.FormulaR1C1 = "Max. Slug duration, "" = "
    Range(ActiveCell, ActiveCell.OFFSET(0, 1)).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
    Range(ActiveCell, ActiveCell.OFFSET(0, 12)).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
    Range(ActiveCell, ActiveCell.OFFSET(5, 14)).Select
    Selection.Font.Bold = True
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .Weight = xlThin
    End With
    
       
    Range("E22:Q22").OFFSET(6 * counter, 0).Select
    Range(ActiveCell, ActiveCell.OFFSET(0, 12)).Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
    End With

    counter = counter + 1

Next Mycell

.ScreenUpdating = True
.Calculation = xlCalculationAutomatic

End With

End Sub

Thank a lot again!
E