Excel VBA: Selection object stuck in first iteration of for each loop, doesn't update?

Question

The first line of the IF statement appears to be caught in the first iteration of the loop. The selection object doesn't seem to update with each iteration. The other lines do appear to update.

Is there a problem using the FIND method inside of a FOR EACH loop? I am thinking that the selection object needs to be deactivated somehow before the end of each iteration.

Any thoughts?


Sub findTotalCosts()


Dim c As Range


For Each c In Range("G:G")
If c.Value Like "TASK*" Then
Columns(2).Find(what:="TOTAL COSTS").Select
'Selection.Offset(0, 7).Select
Cells(c.Row - 2, 11).Value = Selection.Offset(0, 7).Value
Cells(c.Row - 2, 12).Value = c.Offset(-2, -3).Value
End If
Next c

End Sub

Answer 1

A couple of ideas...



Your .Find code will always find the 1st "TOTAL COSTS" in column B...

Columns(2).Find(what:="TOTAL COSTS").Select

Since it always finds the same cell, the next two lines always offset to the same cells



I'm not exactly sure what you want to do in your macro but here's a guess

Range("B" & c.Row, "B" & Rows.Count).Find(What:="TOTAL COSTS").Select

This finds the 1st "Total Costs" in column B starting from the current c.row to the last cell in column B.



This is also a problem...

For Each c In Range("G:G")

You are looping through every cell in column G. That's over 65,000 cells. This will be slow. I doubt you have data in every cell in column G. Try something like this. It loops from G1 to the last used cell in column G

For Each c In Range("G1", Range("G" & Rows.Count).End(xlUp))





Here's your modified macro...





Sub findTotalCosts()



Dim c As Range, TCost As Range



For Each c In Range("G1", Range("G" & Rows.Count).End(xlUp))

If c.Value Like "TASK*" Then

Set TCost = Range("B" & c.Row, "B" & Rows.Count).Find(What:="TOTAL COSTS")

'Check if "TOTAL COSTS" was found

If Not TCost Is Nothing Then

Cells(c.Row - 2, "K").Value = TCost.Offset(0, 7).Value

Cells(c.Row - 2, "L").Value = c.Offset(-2, -3).Value

End If: End If: Next c



End Sub

Answer 2

1

Answer 3

you many times save an identical offset, so it is going to constantly take an identical value of findTotalCost.Offset(0, 19).value i think of this is what you advise and is inflicting your situation. So use an identical theory as you're doing with the r variable to make it dynamic.