Loops, ranges and VLookup

[FurRelKT]

Hello guru's,
I need some help to figure out a solution to my issue. I have a list on
sheet 2 (named ProjectList) and on sheet 1, a column that needs to be
filled with the result from sheet 2 that is held in the third column.
The list contains:
primary, alias and type. I am trying to use the Vlookup for the type,
but the value that i am searching for could be either in the primary or
alias columns.... I can loop though the columns in the list to get the
right match, however the problem is i don't know if it will be in the
primary or alias column, so the vlookup will only work for the values
that match in the 1st column (primary). the question is, how do i
search one column and then the next, but add the vlookup to use both
columns... I have also tried naming the 2nd and third columns in the
list to AliasList, then using

' Cells(x, 6).Formula = "=VLOOKUP(" & Cells(x, 4).Address & _
' ", AliasList,2,True)"

but how can i use the loop or if's to check both lists??? I hope that i
have explained this correctly. Thanks for any insight someone can
provide. As always, thank you very much, and your help is greatly
appreciated.

Keri~

Code i have now:

Dim x As Integer
Dim FinalRow As Long
Dim w As Worksheet
Dim rw As Long
Dim r, s

FinalRow = Cells(Rows.Count, 4).End(xlUp).Row
rw = Sheets("Project").Cells(65500, 1).End(xlUp).Row
Set w = Sheets("Project") 'this is where the list resides

For x = 2 To FinalRow

s = Cells(x, 4).Value
If Cells(x, 6).Value = "" Then
For Each r In w.Range("A2:B" & rw)
If r.Value = s Then
Cells(x, 6).Formula = "=VLOOKUP(" & Cells(x, 4).Address &
_
", ProjectList,3,True)"
Exit For
End If
Next r
End If
Next x

 

[Bernie Deitrick]

FurRelKT,

Try a formula like:

=IF(ISERROR(VLOOKUP(Value,FirstColumn:ThirdColumn,3,False)),VLOOKUP(Value,SecondColumn:ThirdColumn,2,False)),VLOOKUP(Value,FirstColumn:ThirdColumn,3,False)))

HTH,
Bernie
MS Excel MVP

 

[FurRelKT]

Bernie, thank you for your reply. So you mean this??

=IF(ISERROR(VLOOKUP(Value,FirstColumn:ThirdColumn,3,False)),VLOOKUP(Value,S­econdColumn:ThirdColumn,2,False)),VLOOKUP(Value,FirstColumn:ThirdColumn,3,F­alse)))

Cells(x, 6).Formula = _
"=IF(ISERROR(VLOOKUP(" & Cells(x, 4).Address & _
",ProductList,3,False)),VLOOKUP("
& _
Cells(x, 4).Address &
",AliasList,2,False)),VLOOKUP(" & _
Cells(x, 4).Address &
",ProductList,3,False)))"

I get an Run-time error, application-defined or object-defined
error....
Did i do this wrong???

thanks for your help.

Keri~

 

[Bernie Deitrick]

Keri,

You have an extra ) - and, no, that's not a smiley, winky, etc ;-)

Here's the correct code:

Cells(x, 6).Formula = _
"=IF(ISERROR(VLOOKUP(" & Cells(x, 4).Address & _
",ProductList,3,False)),VLOOKUP(" & _
Cells(x, 4).Address & ",AliasList,2,False),VLOOKUP(" & _
Cells(x, 4).Address & ",ProductList,3,False))"


works for me, as long as Product list is 3 columns wide, and AliasList is two columns wide.

HTH,
Bernie
MS Excel MVP



Bernie, thank you for your reply. So you mean this??

=IF(ISERROR(VLOOKUP(Value,FirstColumn:ThirdColumn,3,False)),VLOOKUP(Value,S­econdColumn:ThirdColumn,2,False)),VLOOKUP(Value,FirstColumn:ThirdColumn,3,F­alse)))

Cells(x, 6).Formula = _
"=IF(ISERROR(VLOOKUP(" & Cells(x, 4).Address & _
",ProductList,3,False)),VLOOKUP("
& _
Cells(x, 4).Address &
",AliasList,2,False)),VLOOKUP(" & _
Cells(x, 4).Address &
",ProductList,3,False)))"

I get an Run-time error, application-defined or object-defined
error....
Did i do this wrong???

thanks for your help.

Keri~

 

[FurRelKT]

Bernie, thanks so much for the reply, i have this...
Dim x As Integer
Dim FinalRow As Long
FinalRow = Cells(Rows.Count, 4).End(xlUp).Row
For x = 2 To FinalRow

If Cells(x, 6).Value = "" Then
Cells(x, 6).Formula = "=IF(ISERROR(VLOOKUP(" & Cells(x,
4).Address & _
",ProjectList,3,False)),VLOOKUP(" & Cells(x, 4).Address &
_
",AliasList,2,False),VLOOKUP(" & Cells(x, 4).Address & _
",ProjectList,3,False))"
End If

Next x

However, i am getting #N/A in some of the fields and I know for a fact
that there are matches in the vlookup ranges..
It seems like this is such the better solution, but i was also working
on this one too... out of pieces of code i found...

I am using this and it works... but would like the shorter solution,
like yours...
'////////////////////////////////////////////////////////////////
Dim x, FinalRow, rw As Long
Dim w As Worksheet
Dim s, r
Dim rng As Range
FinalRow = Cells(Rows.Count, 4).End(xlUp).Row
rw = Sheets("Project").Cells(65500, 1).End(xlUp).Row
Set w = Sheets("Project")
Dim MyColumn As String, Here As String
Set rng = w.Range("A2:B" & rw)
For x = 2 To FinalRow
Cells(x, 6).Select
s = Cells(x, 4).Value
If Cells(x, 6).Value = "" Then
For Each r In rng
If r.Value = s Then
'Cells(x, 6).Value = r.Value
Cells(x, 7).Value = r.Address
Cells(x, 6).Formula = "=VLOOKUP(" & Cells(x, 4).Address &
_
", ProjectList,3,True)"
Here = r.Address
MyColumn = Mid(Here, InStr(Here, "$") + 1, InStr(2, Here,
"$") - 2)
If MyColumn = "B" Then
'MsgBox "my column = B"
Cells(x, 4).Clear
Cells(x, 4).Value = r.Offset(0, -1).Value
Cells(x, 6).Formula = "=VLOOKUP(" & Cells(x, 4).Address
& _
", ProjectList,3,True)"
End If
Exit For
End If
Next r
End If
Next x

'///////////////////////////////////////////////////////////////