CODEWITHSUNDEEP
excelfor-loopvba
Jamsandwich
Jamsandwich

Reputation: 634

Excel VBA - Perform Formula on Sheet Based on Cell Values of Looped-Through Worksheets

I currently have this code -

For Each ws In ActiveWorkbook.Worksheets
If ws.Name <> "Uptime" Then
   Worksheets("Uptime").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0) = ws.Name

This loops through all of the sheets and adds the sheetName into Col.A.

What I'm now struggling with is the formula that I want in Col.B. All of my sheets have a calculated value in cell ET40.

I want to take that value, apply formula =(ET40/60)/24 and then place it in the corresponding cell in Col.B on my "Uptime" sheet. I'm struggling with this one.

I've tried - Worksheets("Uptime").Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).Formula = "=(ET40/60)/24" But I think that just tries to look for ET40 on "Uptime" which is empty.

I'm not sure how I get the formula in that line of code to look at the current Worksheet on the loop.

Edit -

Full code written with Rory's help

Sub Uptime()

Dim ws As Worksheet

Application.ScreenUpdating = False

For Each ws In ActiveWorkbook.Worksheets
    If ws.Name <> "Uptime" Then
       With Worksheets("Uptime").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
        .Value2 = ws.Name
        .Offset(, 1).Formula = "=('" & ws.Name & "'!ET40/60)/24"
       End With
    End If
Next ws


End Sub

Upvotes: 0

Views: 57

Answers (1)

Rory
Rory

Reputation: 34045

You just need to add in the sheet name:

If ws.Name <> "Uptime" Then
   With Worksheets("Uptime").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
     .Value2 = ws.Name
     .Offset(, 1).Formula = "=('" & ws.name & "'!ET40/60)/24"
   End With

Upvotes: 1

Related Questions