Darren Smith
Well-known Member
- Joined
- Nov 23, 2020
- Messages
- 631
- Office Version
- 2019
- Platform
- Windows
I am having a very bad day trying to make this work.
Column K has quite a few cells which are merged together I am trying to skip over them but keep coming up against a brick wall.
The excel sheet cannot be altered as it is a template.
Thanks in advance
Column K has quite a few cells which are merged together I am trying to skip over them but keep coming up against a brick wall.
The excel sheet cannot be altered as it is a template.
Thanks in advance
VBA Code:
Private Sub AllocHours()
TurnOff
Dim iRow As Integer
Dim LRow As Long, i As Long
Dim ws As Worksheet
Dim RangeRow As Range, Rng As Range, MyCell As Range, Rrg As Range
Set ws = ThisWorkbook.Worksheets("Job Card Master")
Set RangeRow = ws.Range("A13:A" & ws.UsedRange.Rows.Count).Find("TOTAL BODY SHOP HOURS", LookIn:=xlValues, LookAt:=xlWhole)
LRow = ws.Range("A" & Rows.Count).End(xlUp).Row
If Not RangeRow Is Nothing Then
iRow = RangeRow.Row
End If
If ws.Range("K13:K" & LRow).MergeCells = True Then
Set Rng = ws.Range("K13:K" & LRow)
End If
For Each Rng In ws.Range("K13:K" & LRow)
For i = 13 To Rng.Rows.Count
Set Rrg = Rng.Rows(i)
Set MyCell = ws.Cells(iRow, 11)
MyCell.Value = WorksheetFunction.Sum(Range("K13:K" & Rrg.Value))
Next i
Next
TurnOn
End Sub