VBA to enter formulas in defined range on multiple sheets

[CutterSoilMixing]

Hello everyone!

I have excel files with multiple sheets and would need a VBA to enter formulas in defined ranges on each of the sheets in the book. To make it all a little trickier the formulas would have to be entered from sheet 3 onwards (sheet 1 and 2 have to be left untouched). If that makes it massively more complicated I could work with something that just adds the formulas on all sheets in the book. These are the formulas and ranges for each sheet:

range(AV66:AV30000), =ROUND(A66,2) -> A66 would have to be A67, A68...A30000

range(AW66), =TIMEVALUE(B37)

range(AV67:AV30000), =IF(AR67<>"",AW66+1/86400,"") ->again, the cell references in the formula would have to be progressive, i.e.: AR68, AR69...AR30000 and AW67, AW68...AW30000

range(AX66), =0

range(AX67:AX30000), =IF(AW67<>"",AX66+1/60,"") -> progressive cell references required here as well

I have as good as no experience in VBA and have previously relied on finding code online to use but I can't seem anything that does what I need here. Any piece of code/advice is much appreciated!

Thanks!

 

[Peter_SSs]

ranges for each sheet:

range(AV66:AV30000), =ROUND(A66,2) -> A66 would have to be A67, A68...A30000

range(AW66), =TIMEVALUE(B37)

range(AV67:AV30000), =IF(AR67<>"",AW66+1/86400,"")

Do you have the ranges correct?
Otherwise I am wondering why put a formula in 29,935 cell per the red range only to replace 29,934 of those formulas with a different formula per the blue range?

 

[CutterSoilMixing]

Thanks for your response Peter_SSs! You were right, I messed up the ranges. Apologies for the confusion! I spent some more time on this and was able to put together code that does what I want. The VBA loops through all sheets in the workbook starting on WS #3 and adds all the formulas.

As you can see all my ranges extend to 30,000. That's because the length of data is different in every sheet and I want to make sure none of it is missed. There must be a more elegant way to do is though. How would I go about doing a row count and then using the outcome of that count in the ranges?

Thanks in advance!

Sub AddFormulas()

    Dim ws_count As Integer
    Dim i As Integer
    Dim StartTime As Double
    Dim SecondsElapsed As Double
    
    ws_count = ActiveWorkbook.Worksheets.Count
    
    StartTime = Timer
    
    For i = 3 To ws_count
    
    Sheets(i).Range("AT58").Value = "depth [m]"
    Sheets(i).Range("AU58").Value = "time [h:min:s]"
    Sheets(i).Range("AV58").Value = "duration [min]"

    Sheets(i).Range("AT59").Formula = "=ROUND(A59,2)"
    Sheets(i).Range("AT59:AT30000").FillDown
    
    Sheets(i).Range("AU59").Formula = "=TIMEVALUE(B30)"
    Sheets(i).Range("AU60").Formula = "=IF(AR60<>"""",AU59+1/86400,"""")"
    Sheets(i).Range("AU60:AU30000").FillDown
    
    Sheets(i).Range("AV59").Value = "0"
    Sheets(i).Range("AV60").Formula = "=IF(AU60<>"""",AV59+1/60,"""")"
    Sheets(i).Range("AV60:AV30000").FillDown
    
    Next i
    
    MinutesElapsed = Format((Timer - StartTime) / 86400, "hh:mm:ss")
    MsgBox "This code ran successfully in " & MinutesElapsed & " minutes", vbInformation
                    
End Sub

 

[Peter_SSs]

There must be a more elegant way to do is though. How would I go about doing a row count and then using the outcome of that count in the ranges?

I have assumed that column A can be used to determine the last row that the formulas need to go down to.
Give this a try with a copy of your workbook.

Sub AddFormulas_v2()
  Dim i As Long
  
  For i = 3 To ActiveWorkbook.Sheets.Count
    With Sheets(i)
      .Range("AT58:AV58").Value = Array("depth [m]", "time [h:min:s]", "duration [min]")
      .Range("AT59:AV" & .Range("A" & Rows.Count).End(xlUp).Row).Formula = _
          Array("=ROUND(A59,2)", "=IF(AR59<>"""",AU58+1/86400,"""")", "=IF(AU59<>"""",AV58+1/60,"""")")
      .Range("AU59:AV59").Formula = Array("=TIMEVALUE(B30)", 0)
    End With
  Next i
End Sub

 

[CutterSoilMixing]

I have assumed that column A can be used to determine the last row that the formulas need to go down to.
Give this a try with a copy of your workbook.

Sub AddFormulas_v2()
  Dim i As Long
 
  For i = 3 To ActiveWorkbook.Sheets.Count
    With Sheets(i)
      .Range("AT58:AV58").Value = Array("depth [m]", "time [h:min:s]", "duration [min]")
      .Range("AT59:AV" & .Range("A" & Rows.Count).End(xlUp).Row).Formula = _
          Array("=ROUND(A59,2)", "=IF(AR59<>"""",AU58+1/86400,"""")", "=IF(AU59<>"""",AV58+1/60,"""")")
      .Range("AU59:AV59").Formula = Array("=TIMEVALUE(B30)", 0)
    End With
  Next i
End Sub

Works perfectly, thank you!

Out of interest: instead of writing the actual formulas into the cells is there a way to get the macro to do the calculations and then only insert the result? So when you click into the cell you would only see a number and not the formula. I'm asking because the data sets I'm working with are pretty big and all the calculations slow down the workbook significantly so hopefully just having the numbers in there would make it smoother.

Thanks!

 

[My Aswer Is This]

Works perfectly, thank you!

Out of interest: instead of writing the actual formulas into the cells is there a way to get the macro to do the calculations and then only insert the result? So when you click into the cell you would only see a number and not the formula. I'm asking because the data sets I'm working with are pretty big and all the calculations slow down the workbook significantly so hopefully just having the numbers in there would make it smoother.

Thanks!

I have been monitoring this post for a day or two and have wondered myself why you need to use formulas instead of having Vba run a script every time you want to see results of the formulas. I'm sure this can be done. I'm sure Peter will be back with a solution.

 

[CutterSoilMixing]

I have been monitoring this post for a day or two and have wondered myself why you need to use formulas instead of having Vba run a script every time you want to see results of the formulas. I'm sure this can be done. I'm sure Peter will be back with a solution.

Yeah, that would have been the way to go from the beginning. I'm a total VBA greenhorn so I'm only now learning about the capabilities of VBA and what I want it to do. Just this thread alone has probably quadrupled my VBA knowledge. It's been a fun journey so far!

 

[Peter_SSs]

I'm asking because the data sets I'm working with are pretty big and all the calculations slow down the workbook significantly ..

Is the slowness problem when the code initially inserts the formulas or is it an ongoing issue of slowness while using the workbook?

 

[CutterSoilMixing]

Is the slowness problem when the code initially inserts the formulas or is it an ongoing issue of slowness while using the workbook?

The code runs pretty quick. The issue is more while using the workbook.

 

[Peter_SSs]

The issue is more while using the workbook.

In that case I would try this code that initially puts the formulas in similar to the previous code, but then replaces the formulas with their values.

Sub AddFormulas_v3()
  Dim i As Long
  
  For i = 3 To ActiveWorkbook.Sheets.Count
    With Sheets(i)
      .Range("AT58:AV58").Value = Array("depth [m]", "time [h:min:s]", "duration [min]")
      With .Range("AT59:AV" & .Range("A" & Rows.Count).End(xlUp).Row)
        .Formula = Array("=ROUND(A59,2)", "=IF(AR59<>"""",AU58+1/86400,"""")", "=IF(AU59<>"""",AV58+1/60,"""")")
        .Cells(1, 2).Resize(, 2).Formula = Array("=TIMEVALUE(B30)", 0)
        .Value = .Value
      End With
    End With
  Next i
End Sub