Enter Data With 3 Way-Lookup

[Dtex20]

Hi, this is my current code.

Sub test()

Dim col, ro, col2 As Double
Dim sCellVal As String
col = 0
col2 = 0
ro = 0
Dim cel As Range


sCellVal = Range("C9").Value


If sCellVal Like "Night" Then


For Each cel In Worksheets("Service").Range("C41:NR41")


If cel.Value = Range("B9").Value Then
col = cel.Column
Exit For
End If


Next cel


If col = 0 Then
MsgBox "Date Not Found"
Exit Sub
End If


For Each cel In Worksheets("Service").Range("A40:A80")


If cel.Value = Range("E17").Value Then
ro = cel.Row
Exit For
End If


Next cel


If ro = 0 Then
MsgBox "Name Not Found"
Exit Sub
End If


Worksheets("Service").Cells(ro, col).Value = Range("W2").Value


MsgBox "Record added (Night Pay)"


Exit Sub
End If


For Each cel In Worksheets("Service").Range("C1:NR1")


If cel.Value = Range("B9").Value Then
col = cel.Column


ElseIf cel.Value = Range("C9").Value Then
col2 = cel.Column
Exit For
End If


Next cel


If col = 0 Then
MsgBox "Date Not Found"
Exit Sub
End If


For Each cel In Worksheets("Service").Range("A1:A38")


If cel.Value = Range("F17").Value Then
ro = cel.Row
Exit For
End If


Next cel


If ro = 0 Then
MsgBox "Name Not Found"
Exit Sub
End If


Worksheets("Service").Cells(ro, col).Value = Range("W2").Value


MsgBox "Record added!"


End Sub

I have a two way lookup that checks for the values in cell B9 & F17, Then adds the value from W2 to where the column number and row number intersect.

I'm trying to add a 3 way lookup now, where two columns are selected and 1 row, this then adds the value in W2 where the range is. So col = 1, col2 = 3, row = 1 = Range (A1:C1) would have the W2 value put into them.

The values stored in B9 & C9 is a date range. It searches for that date in (CR1:NR1).

ElseIf cel.Value = Range("C9").Value Then
col2 = cel.Column
Exit For
End If

I've got it to stored it, but i do not know how to turn 3 integers into coordinates with VBA.

Thanks!

 

[Fluff]

Cross posted https://www.excelforum.com/excel-pr...-3-way-for-each-lookup-finding-the-range.html

Cross-Posting
While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[Dtex20]

Cross posted https://www.excelforum.com/excel-pr...-3-way-for-each-lookup-finding-the-range.html

Cross-Posting
While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

My apologies, i thought this thread got lost as was not being tracked anymore!

Thanks!