for each cell / vlookup

[Hlatigo]

Good morning everyone!

I have a vlookup that is based on two variables one in column B and the other in column D.

In column B i am looking for 79, 80, 81, 142, and 143 but there are many duplicates and so once it scrolls down and finds one of the above it then needs to compare to column D for the current date. Once this has been done a formula needs to be placed in a cell three columns over. this lookup will be based on column B and D and there there is a set range from O16:R20 that has the 5 numbers listed above and the needed number that needs to go into that cell.

I dont know if that all makes since but here is the code i have so far...but it isn't going to well. It says I cant do multiple "for each cell in range"


I hope someone is out the to help out with this. thanks!!

Sub price_updte()

 Dim cell As Range
 
 
 For Each cell In Range("B1:B1000")
        If cell.Value = 79 Then
            For Each cell In Range("D1:D1000")
                If cell.Value = Now() Then
                    cell.Value.Offset(0, 3) = WorksheetFunction.VLookup("formulaR7C15, _
                    sheets(1).Range("O16:R20"), 2, False)
                  End If
            Next cell
         End If
    Next cell

End Sub

Thanks for anyone help!!!

 

[Andrew Poulsom]

You need 2 variables, eg:

Sub price_updte() 
Dim cell1 As Range 
Dim cell2 as Range
For Each cell1 In Range("B1:B1000") 
   If cell1.Value = 79 Then 
      For Each cell2 In Range("D1:D1000") 
         If cell2.Value = Now() Then 
'           Some code
         End If 
      Next cell2
   End If 
Next cell1 
End Sub

 

[Hlatigo]

Thanks so very much. since i have 79, 80, 81, 142 and 143..would that mean I would have 5 variables and should do
cell
cell2
cell3
cell4
cell5

Thanks!

 

[Andrew Poulsom]

More like:

Sub price_updte() 
   Dim cell As Range 
   For Each cell In Range("B1:B1000") 
      Select Case cell.Value
         Case 79 To 81, 142 To 143
               If cell.Offset(0, 2).Value = Now() Then 
'                 Some code 
               End If 
      End Select
   Next cell 
End Sub