VBA to loop code to the next row

[ENicklin]

Hey everyone. Very new to coding. I have a spreadsheet that we use to track employee stats each week and we keep a running total of their performance from previous weeks along with their current perfomance on each sheet. Typically we used to just copy the last sheet and then manually add the totals from the previous week into this weeks total and then manually delete everything but I found that quite tedious so I have figured out some code to copy over their total from the previous week and add it to their running total and then reset the data for this week but I need to figure out how to loop it to the next row. My only issue is employees are grouped by type (full time, part time, etc.) so there are some merged rows that would need to be skipped. The code I have so far is as follows:

Range("J3").Value = Range("C3").Value + Range("G3").Value
Range("j3").Select
Selection.Cut Range("G3")
Range("K3").Value = Range("D3").Value + Range("H3").Value
Range("K3").Select
Selection.Cut Range("H3")
Range("I3").Value = Range("H3").Value / Range("G3").Value
Range("C3:D3").SpecialCells(xlCellTypeConstants, 23).Clear

but that only works for row 3 and I need it to go to row 34 currently. Also I need it to be able to skip over blank cells as sometimes empoloyees don't work and so won't have a weekly target and currently it returns an error on the last line (when it clears) if the cell is already blank.

Much thanks for your help.

 

[Michael M]

Maybe this, but why are you cutting cells

Sub MM1()
Dim r As Long
For r = 3 To 34
    Range("J" & r) = Range("C" & r) + Range("G" & r)
    Range("J" & r).Cut Range("G" & r)
    Range("K" & r) = Range("D" & r) + Range("H" & r)
    Range("K" & r).Cut Range("H" & r)
    Range("I" & r) = Range("H" & r) / Range("G" & r)
    Range("C" & r & ":D" & r).SpecialCells(xlCellTypeConstants, 23).Clear
Next r
End Sub

 

[Osvaldo Palmeiro]

Try:

Sub Test()
 Dim k As Long
  For k = 3 To 34
   Cells(k, "G") = Cells(k, "C") + Cells(k, "G")
   Cells(k, "H") = Cells(k, "D") + Cells(k, "H")
   Cells(k, "I") = Cells(k, "H") / Cells(k, "G")
   Cells(k, "C").Resize(, 2) = ""
  Next k
End Sub

 

[Marc L]

Hi, according to Excel basics no need to loop like in this VBA demonstration as a beginner starter :​

Sub Demo1()
    [G3:G34] = [C3:C34+G3:G34]
    [H3:H34] = [D3:D34+H3:H34]
    [I3:I34] = [H3:H34/G3:G34]
    [C3:D34].Clear
End Sub

 

[ENicklin]

Maybe this, but why are you cutting cells

Sub MM1()
Dim r As Long
For r = 3 To 34
    Range("J" & r) = Range("C" & r) + Range("G" & r)
    Range("J" & r).Cut Range("G" & r)
    Range("K" & r) = Range("D" & r) + Range("H" & r)
    Range("K" & r).Cut Range("H" & r)
    Range("I" & r) = Range("H" & r) / Range("G" & r)
    Range("C" & r & ":D" & r).SpecialCells(xlCellTypeConstants, 23).Clear
Next r
End Sub

Thanks,

I'll give this a try.

I am cutting cells because I need to add the previous weeks total to the historical total and then replace that value back into the cell with the historical total and I could not find a way to add those values together without copying both values into a new cells and then cutting and pasting it back into the previous cell.

Here's a screenshot to better show you what I am doing:

So ai am moving the values in the Target and Achieved columns into the Total Target and Total Achieved columns respectively and then resetting the values for the next week.

Thanks again

 

[ENicklin]

Thanks,

I'll give this a try.

I am cutting cells because I need to add the previous weeks total to the historical total and then replace that value back into the cell with the historical total and I could not find a way to add those values together without copying both values into a new cells and then cutting and pasting it back into the previous cell.

Here's a screenshot to better show you what I am doing:

View attachment 48258

So ai am moving the values in the Target and Achieved columns into the Total Target and Total Achieved columns respectively and then resetting the values for the next week.

Thanks again

Also this works except if I run into a blank cell I get an error on the last line and also once I hit row 13 (the first merged row) I get an error stating it will cause some merged cells to unmerge. Is there a way to prevent this?

I mostly need to it be able to ignore blank cells as some employees may not have values if they did not work for a given week.

Thanks again

 

[Michael M]

Merged cells and VBA are not friends !
Merged cells will always give you issues using code.
I would suggest you Unmerge those rows and instead use...Highlight the row from "A:I">>Format Cells>>Alignent>>Horizontal>>"center across selection"
Try

Sub MM1()
Dim r As Long
For r = 3 To 34
 If Range("H" & r)<> "" then
   Range("J" & r) = Range("C" & r) + Range("G" & r)
    Range("J" & r).Cut Range("G" & r)
    Range("K" & r) = Range("D" & r) + Range("H" & r)
    Range("K" & r).Cut Range("H" & r)
    Range("I" & r) = Range("H" & r) / Range("G" & r)
    Range("C" & r & ":D" & r).SpecialCells(xlCellTypeConstants, 23).Clear
Next r
end if
End Sub

 

[ENicklin]

Merged cells and VBA are not friends !
Merged cells will always give you issues using code.
I would suggest you Unmerge those rows and instead use...Highlight the row from "A:I">>Format Cells>>Alignent>>Horizontal>>"center across selection"
Try

Sub MM1()
Dim r As Long
For r = 3 To 34
 If Range("H" & r)<> "" then
   Range("J" & r) = Range("C" & r) + Range("G" & r)
    Range("J" & r).Cut Range("G" & r)
    Range("K" & r) = Range("D" & r) + Range("H" & r)
    Range("K" & r).Cut Range("H" & r)
    Range("I" & r) = Range("H" & r) / Range("G" & r)
    Range("C" & r & ":D" & r).SpecialCells(xlCellTypeConstants, 23).Clear
Next r
end if
End Sub

Thanks. I'll do that and that should fix my issues. I greatly appreciate the help. Cheers!

 

[Michael M]

Glad to help...