LinkBack URL
About LinkBacks
cvI have a spreadsheet I am working on to look up a zip code amongst multiple preset zip code ranges, and return the rate of a service from multiple providers (for analysis/comparison use) to that zip code. The kicker for me, was that I have 4 different points of origin, and I don't think I figured out the variables correctly, and 2 of these origins are supposed to look up different data from 2 different tabs. So it's supposed to work like this:
I kind of have two questions.
If origin = a, then matrix lookup zip code within column A of tab 1, and return all rates for specified providers of this tab elseif origin = b, then matrix lookup zip code within column A of tab 2, and return all rates for specified providers of this tab elseif origin = c, then matrix lookup zip code within column A of tab 3 and tab 4, and return all rates for specified providers of these tabs elseif origin = d, then matrix lookup zip code within column A of tab 3 and tab 4, and return all rates for specified providers of these tabs elseif zip code isn't found within the specified ranges, then rates = "SPOT"
1) What am I missing to make the code below work correctly? (I can provide my mockup file if need be)
2) How would I do this looking up the providers dynamically, and not hard coded like I have below?
I apologize for all the comments, that was a co-worker and myself trying out different ideas..
Sub PopulateRates() '******************************* Dim wsTarget As Worksheet '******************************* Dim lr As Long 'last row of postal codes Dim lrLookup As Long 'last row of lookup values ' Dim lrLookup1(0 To 5) As Long 'test Dim i As Long Dim j As Long Dim k As Long 'test Dim char5 As String 'chars of postal code Dim origin As String 'point of origin Dim currLB As String 'lower bound of range in lookup table Dim currUB As String 'upper bound in lookup table Dim foundMatch As Boolean Dim namelist() As String 'test Dim namestring As String Dim searchtarget As Worksheet Dim activelookup As Worksheet '**************************************************************************** Set wsTarget = ActiveWorkbook.Worksheets("Target and Actual table") '************************************************************************ namestring = "t5,t12,t22,t22b,t30,t30b" namelist = Split(namestring, ",") k = 5 lr = wsTarget.Range("e" & Rows.Count).End(xlUp).Row 'changed lrLookup = ThisWorkbook.Sheets(namelist(k)).Range("a" & Rows.Count).End(xlUp).Row 'changed For i = 3 To lr foundMatch = False char5 = wsTarget.Range("e" & i).Value origin = wsTarget.Range("b" & i).Value For j = 4 To lrLookup currLB = Left(ThisWorkbook.Sheets(namelist(k)).Range("a" & j).Value, 5) currUB = Right(ThisWorkbook.Sheets(namelist(k)).Range("a" & j).Value, 5) If char5 >= currLB And char5 <= currUB Then If origin = "5" Then If k = 0 Then wsTarget.Range("bg" & i).Value = ThisWorkbook.Sheets(namelist(k)).Range("f" & j).Value End If ElseIf origin = "12" Then If k = 1 Then wsTarget.Range("h" & i).Value = ThisWorkbook.Sheets(namelist(k)).Range("f" & j).Value End If ElseIf origin = "22" Then If k = 2 Then wsTarget.Range("g" & i).Value = ThisWorkbook.Sheets(namelist(k)).Range("f" & j).Value wsTarget.Range("i" & i).Value = ThisWorkbook.Sheets(namelist(k)).Range("g" & j).Value wsTarget.Range("k" & i).Value = ThisWorkbook.Sheets(namelist(k)).Range("h" & j).Value wsTarget.Range("m" & i).Value = ThisWorkbook.Sheets(namelist(k)).Range("i" & j).Value wsTarget.Range("o" & i).Value = ThisWorkbook.Sheets(namelist(k)).Range("j" & j).Value wsTarget.Range("q" & i).Value = ThisWorkbook.Sheets(namelist(k)).Range("k" & j).Value wsTarget.Range("s" & i).Value = ThisWorkbook.Sheets(namelist(k)).Range("l" & j).Value wsTarget.Range("u" & i).Value = ThisWorkbook.Sheets(namelist(k)).Range("m" & j).Value wsTarget.Range("w" & i).Value = ThisWorkbook.Sheets(namelist(k)).Range("n" & j).Value wsTarget.Range("y" & i).Value = ThisWorkbook.Sheets(namelist(k)).Range("o" & j).Value End If ElseIf origin = "22" Then If k = 3 Then wsTarget.Range("as" & i).Value = ThisWorkbook.Sheets(namelist(k)).Range("f" & j).Value wsTarget.Range("au" & i).Value = ThisWorkbook.Sheets(namelist(k)).Range("g" & j).Value wsTarget.Range("aw" & i).Value = ThisWorkbook.Sheets(namelist(k)).Range("h" & j).Value wsTarget.Range("ay" & i).Value = ThisWorkbook.Sheets(namelist(k)).Range("i" & j).Value wsTarget.Range("ba" & i).Value = ThisWorkbook.Sheets(namelist(k)).Range("j" & j).Value wsTarget.Range("bc" & i).Value = ThisWorkbook.Sheets(namelist(k)).Range("k" & j).Value wsTarget.Range("be" & i).Value = ThisWorkbook.Sheets(namelist(k)).Range("l" & j).Value End If ElseIf origin = "30" Then If k = 4 Then wsTarget.Range("g" & i).Value = ThisWorkbook.Sheets(namelist(k)).Range("f" & j).Value wsTarget.Range("i" & i).Value = ThisWorkbook.Sheets(namelist(k)).Range("g" & j).Value wsTarget.Range("k" & i).Value = ThisWorkbook.Sheets(namelist(k)).Range("h" & j).Value wsTarget.Range("m" & i).Value = ThisWorkbook.Sheets(namelist(k)).Range("i" & j).Value wsTarget.Range("o" & i).Value = ThisWorkbook.Sheets(namelist(k)).Range("j" & j).Value wsTarget.Range("q" & i).Value = ThisWorkbook.Sheets(namelist(k)).Range("k" & j).Value wsTarget.Range("s" & i).Value = ThisWorkbook.Sheets(namelist(k)).Range("l" & j).Value wsTarget.Range("u" & i).Value = ThisWorkbook.Sheets(namelist(k)).Range("m" & j).Value wsTarget.Range("w" & i).Value = ThisWorkbook.Sheets(namelist(k)).Range("n" & j).Value wsTarget.Range("y" & i).Value = ThisWorkbook.Sheets(namelist(k)).Range("o" & j).Value End If ElseIf origin = "30" Then If k = 5 Then wsTarget.Range("as" & i).Value = ThisWorkbook.Sheets(namelist(k)).Range("f" & j).Value wsTarget.Range("au" & i).Value = ThisWorkbook.Sheets(namelist(k)).Range("g" & j).Value wsTarget.Range("aw" & i).Value = ThisWorkbook.Sheets(namelist(k)).Range("h" & j).Value wsTarget.Range("ay" & i).Value = ThisWorkbook.Sheets(namelist(k)).Range("i" & j).Value wsTarget.Range("ba" & i).Value = ThisWorkbook.Sheets(namelist(k)).Range("j" & j).Value wsTarget.Range("bc" & i).Value = ThisWorkbook.Sheets(namelist(k)).Range("k" & j).Value wsTarget.Range("be" & i).Value = ThisWorkbook.Sheets(namelist(k)).Range("l" & j).Value End If Else End If j = lrLookup foundMatch = True End If Next j If foundMatch = False Then 'Doesn't fully work wsTarget.Range("g" & i).Value = "******" End If Next i Set wsTarget = Nothing End Sub
Register To Reply
Bookmarks