Copy value of an offset variable cell to another offset variable cell

[jcuoco78]

Here is what I am trying to do. I am searching through column D of a spreadsheet for a value in cell L8. There may be multiple instances of the value in column D. When I find an instance, I need to check if column I of the same row is not blank. If it is not then I need to get the value from column A of the row and copy it into cell L13. I will need to repeat this through column AF, and then down to L16 and so on so dynamic code is necessary. I can not figure out how to do the offset from the current Search variable cell to load the value from column A. I added the InDiameter variable and tried to give it the value of Search but it does not seem to like that. I tried to declare it as Range and get errrors. This is just the beginning of a much more elaborate set of code but it is hanging me up from moving forward.

Sub Production_Plan()

   'Find incoming diameter

    Dim FoundCell As Range
    Dim Search As String
    Dim InDiameter As String

    Search = Sheets("Inventory").Range("$L$8")
    InDiameter = Search
    Set FoundCell = Sheets("Inventory").Columns(4).Find(Search, LookIn:=xlValues, LookAt:=xlWhole)
   
   'Check Coil Status and assign to machine
    If Not FoundCell Is Nothing Then
        FoundCell.Activate
        If (ActiveCell.Offset(0, 5) = "") Then
        Range("InDiameter").Offset(5, 0) = ActiveCell.Offset(0, -3)
        End If

    End If

End Sub

Please help!
John

 

[steve the fish]

You would keep overwriting L13 everytime the conditions were met. Is that what you want?

 

[jcuoco78]

No. I want to write to L13 once and then move on to M13, etc all the way to AF13, and then down to check L16 and write to L21 and continue across.

 

[steve the fish]

Whoa. Say that again more clearly. What would make it use M13? What conditions are met?

 

[jcuoco78]

I only set the range to $L$8 initially to try to get it to work for one cell before I attempted to make it dynamic.

 

[jcuoco78]

Sorry. I check L8 and then write to L13, then check M8 and write to M13 and so on. Reading it back I see where I was confusing it.

 

[steve the fish]

So at all times you are checking column D

 

[jcuoco78]

Yes the check column is always D

 

[steve the fish]

Ok something like this. Test on a copy of your workbook first!

Dim myFind As Range
Dim c As Range
Dim sh As Worksheet

Set sh = Sheets("Inventory")

For Each myFind In sh.Range("L8:AF8")
    With sh.Columns(4)
        Set c = .Find(myFind, LookIn:=xlValues, LookAt:=xlWhole)
        If Not c Is Nothing Then
            If Len(sh.Cells(c.Row, "I").Value) = 0 Then
                myFind.Offset(5, 0) = c.Offset(0, -3)
            End If
        End If
    End With
Next

 

[steve the fish]

Actually this:

For Each myFind In sh.Range("L8:AF8,L16:AF16")