Hi All,

I need some help figuring this out. I have data that looks like this in the worksheet:

Column 1 Column 2
aaa xxx
bbb xxx
ccc xxx
ddd xxx
eee xxx
aaa yyy
bbb yyy
ccc yyy
ddd yyy
eee yyy
...

Please note that the number of times this patten repeats is variable and is generated dynamically just before I am attempting to make the calculation below.

Here is what I need to do:

I need to find the range that contains xxx in column 2. Then, within that range I need to find the row that contains "bbb" and get the value from column 3.
Then I need to find the row that contains ccc and put the value in column 3 = bbb row value * 0.746.
Then I need to repeat this for yyy and so on.

I have kind of pseudo-coded this out, but it is not working, I think because I am missing some of the finer points of finding within a range and have not found much by googling.

A couple notes about the code:
  • I have an array NameArray that contains xxx, yyy, etc.
  • Please see the comment that indicates where it is currently erroring and what the message is.


Here is my code:
If wksheet = "Systems" Then
        With Sheets(wksheet)

            For j = 0 To UBound(NameArray, 1)
 
                    Debug.Print NameArray(j, 1)
                    
                    '****Currently the code is erroring HERE with an "overflow" error.
                    Set LastCell = Range("D:D").Cells(.Cells.Count)
                    
                    Set FoundCell = Range("D:D").Find(what:=NameArray(j, 1), after:=LastCell)
                    
                    If Not FoundCell Is Nothing Then
                        FirstAddr = FoundCell.Address
                    End If
                    
                    Do Until FoundCell Is Nothing
                        Debug.Print FoundCell.Address
                        Set FoundCell = .Cells.Find(what:=NameArray(j, 1), after:=LastCell)
                        If FoundCell = FirstAddr Then
                            Exit Do
                        End If
                    Loop
                    
                    'NOW we have a range of cells that contain NameArray(j,1) - right???
                    
                    For Each n In FoundCell
                        If n.Offset(0, -1) = "ccc" Then
                            kWValue = FoundCell.Find(what:="bbb").Offset(0, 3).Value
                            .Cells(row, aa + 4).Value = kWValue * 0.746
                        End If
                    Next
                    
            Next j
    End With
End If
Any help will be greatly appreciated.

Thanks,
brl8