Apllying code to every excel sheet

[szymon94]

Hello, I have a small problem. I have created code for myself but I cannot make a loop for all sheets ( every week it vary from 50 to 150 every week) I need to apply this code for all of the worksheets. What it does in every sheet I cave cell 2022 just one and in another column (13 in right) I have amounts so I select them and sum them in AG5. I would like to apply to all of the worksheets.

Sub macro1()



Dim I As Range


Dim cell As Range

Set I = Range("O1:O50")


For Each cell In I.Cells

If cell.Value <> "2022.00" Then

cell.Offset(, 13).Resize(4).Select

Else

Range("AG5") = Application.Sum(Range(Selection.Address))

End If

Next


End Sub

 

[offthelip]

Try this code, but note the check is looking for "2022.00" as TEXT, so it will not detect 2022 if it is entered as number ( i.e is the data sitting left justified or right justified. ) if you want to check for either change the line to one I have commented out

Sub test()
For i = 1 To Worksheets.Count
With Worksheets(i)
 colo = .Range(.Cells(1, 15), .Cells(50, 15)) ' all the data from column O
 ColAB = .Range(.Cells(1, 28), .Cells(50, 28)) ' all the data from column AB
Sumc = 0
 For j = 1 To 50
  If colo(j, 1) = "2022.00" Then
'  If colo(j, 1) = 2022 Then
   Sumc = Sumc + ColAB(j, 1)
  End If
 Next j
 .Range(.Cells(5, 33), .Cells(5, 33)) = Sumc
 End With
 Next i
 
 
End Sub

 

[szymon94]

Your code is actually briliant but I have one issue It does not sums up the values but I do not know where to change it.

appreciate your help

 

[offthelip]

Have you tried using the line which is commented out?? i.e

Sub test()
For i = 1 To Worksheets.Count
With Worksheets(i)
 colo = .Range(.Cells(1, 15), .Cells(50, 15)) ' all the data from column O
 ColAB = .Range(.Cells(1, 28), .Cells(50, 28)) ' all the data from column AB
Sumc = 0
 For j = 1 To 50
'  If colo(j, 1) = "2022.00" Then
  If colo(j, 1) = 2022 Then
   Sumc = Sumc + ColAB(j, 1)
  End If
 Next j
 .Range(.Cells(5, 33), .Cells(5, 33)) = Sumc
 End With
 Next i
 
 
End Sub

 

[szymon94]

Yeah same output it is just take the value from one cell, somehow it does not sum them

 

[offthelip]

Looking at the plot you have got the code does what I am expecting . Are you expecting it to sum 1404.43 + 3.13+ -1700.17 + 1730.45 or just sum the rows that have 2022 in column O, because currently you have one value of 2022 in column O so the code put that value in AG3 , which is what you asked for. Have I completely misunderstood what you are asking for

 

[offthelip]

What are you expecting to happen when the value in column O is blank??

 

[szymon94]

Hmm I my try to explain by example.

Column o value 2022 ( lets say it would be O10(it is different from sheet to sheet) then in the same row but different column amount it need to summs up (it is usually 2-5 values) so lets say it goes to AB10 and sums up them in column (AG5)
Sorry for this childish screen, it is just need to sums up value for 2022 what is above is for past years

 

[offthelip]

I think I understand what you want so try this code:

Sub test()
For i = 1 To Worksheets.Count
With Worksheets(i)
 colo = .Range(.Cells(1, 15), .Cells(50, 15)) ' all the data from column O
 colab = .Range(.Cells(1, 28), .Cells(50, 28)) ' all the data from column AB
Sumc = 0
startf = False
 For j = 1 To 50
'  If colo(j, 1) = "2022.00" Then
  If colo(j, 1) = 2022 Or (startf And (colo(j, 1) = "")) Then
    startf = True
    If colab(j, 1) = "" Then
     startf = False
    Else
     Sumc = Sumc + colab(j, 1)
    End If
  Else
  startf = False
  End If
 Next j
 .Range(.Cells(5, 33), .Cells(5, 33)) = Sumc
 End With
 Next i
 
 
End Sub

 

[szymon94]

I love it ! it works perfectly but do you know how to stop rounding ? for example sum is 32.98 it round to 33