Imagine the following:
A6 = 8006-001
A7 = 9080-002
A8 = xxxx
Schedule K - 2008 Final
D12 = 8009-001
B31 = TOTAL COSTS
I31 = $9,000
The above portion in bold on Schedule K repeats but the different values in the first and last cells.
The middle cell will always say 'TOTAL COSTS.' This is where I am getting stuck on.
In short, I am starting with
cell A6 on Schedule I (this is the 'project' object variable in the first for each loop), taking that cell and going to
Schedule K and running it down column D (using 'b' as the object variable for the second for each loop) to compare for a
match. If a match is found inside of this second/nested for each loop, then I want to use the FIND method and move over 2
columns to the left, and then down that column (B) to locate the cell that contains the next occurence of the letters 'TOTAL
COSTS.' Then from 'TOTAL COSTS' I want to move over 7 cells, get the value in that cell, and drop it in a cell on the first
tab, Schedule I.
The problem is that on the first iteration of the nested for each loop, the program is getting
stuck on the object variable 'findTotalCost.' Then comes across and extracts $9,000 and pastes it on the Schedule I. This
is ok for the first iteration. However the $9,000 value gets pasted again in the next cell below, again and again with each
So the program is stuck on the first occurence of 'TOTAL COSTS.' I need to somehow clear the value of
'TOTAL COSTS' at the of each iteration. Al least that's what I think. That way each occurence of 'TOTAL COSTS' will be
picked up not just the first one.
Dim b As Range
Dim project As Range
Dim findTotalCost As Range
r = 6
For Each project In Worksheets("Schedule I").Range("A:A")
For Each b In Worksheets("Schedule K - 2008 Final").Range("D:D")
If b.Value = project.Value Then
Set findTotalCost = Columns(2).Find(what:="TOTAL COSTS")
Worksheets("Schedule I").Cells(r, 28).Value = findTotalCost.Offset(0, 19).Value
r = r + 1