Help with fixing For Loop

Question

I have a sheet that has this structure and I am trying to do a sum the miles based on values in the B column being true and putting the sum in F in the row that the first mile entry is

The VB is and it is putting the sum in all cells.

Sub CompareCells()

'

Dim ct As Long 

Dim lr As Long 

Dim cr As Long 

ct = 1 

'cr = 1 

lr = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row 

For ct = ct To lr

'If [b2] = [b3] Then 

If ["b" & ct] = ["b" & ct+1] Then 

Range("F" & ct) = WorksheetFunction.Sum(Range("C" & ct & ":C" & ct + 1)) 

Else 

    '[g2] = "no" 

End If 

Next ct 

End Sub

Answer 1

You can't do formulas inside square brackets. Use the Range method:

If Range("b" & ct) = Range("b" & ct+1) Then

Kevin

Answer 2

Hi Don

According to the picture and description.

It seems the values in column A follow a pattern OWNER>>LOADED>>EMPTY

If so we can try the following macro.

Sub CompileData()

Dim myRange As Range

Dim rng As Range

Dim W

With Sheets("Sheet1")

Set myRange = .Range(Cells(1, "A"), Cells(Rows.Count, "A").End(xlUp)) 

For Each rng In myRange

W = InStr(rng.Value, "OWNER")

If W > 0 Then

.Cells(rng.Row, "F").Value = .Cells(rng.Row + 1, "C").Value 

.Cells(rng.Row + 1, "F").Value = WorksheetFunction.Sum(.Cells(rng.Row + 1, "C"), .Cells(rng.Row + 2, "C")) 

.Cells(rng.Row + 2, "F").Value = .Cells(rng.Row + 2, "C").Value 

End If

Next rng

End With

End Sub

I hope this helps you and gives a solution to your problem

Regards

Jeovany