asked on

Running Multiple IF statements with VLOOKUP

I am trying to run multiple IF statements but the last 2 with variants PN & test do not run. Can someone help me? I do not get any error messages.
Private Sub CTNTxtBox_Change()
Dim wb As Workbook
Dim ws As Worksheet
Dim rng As Range
Dim CTNumber As Variant, BN As Variant, FN As Variant, LN As Variant, PN As Variant, Test As Variant
CTNumber = CTNTxtBox.Value
Set rng = Worksheets("Feb252010_TM_Cycle1").Range("A1:ao300")
FNameTxtBox.Enabled = True
FNameTxtBox.BackColor = &H8080FF
LNameTxtBox.Enabled = True
LNameTxtBox.BackColor = &H8080FF
PhoneTxtBox.Enabled = True
PhoneTxtBox.BackColor = &H8080FF
TextBox1.Enabled = True
TextBox1.BackColor = &H8080FF

BN = Application.VLookup(Val(CTNTxtBox.Value), rng, 4, False) 'Treat CTNTxtBox.Value as a number
FN = Application.VLookup(Val(CTNTxtBox.Value), rng, 5, False) 'Treat CTNTxtBox.Value as a number
LN = Application.VLookup(Val(CTNTxtBox.Value), rng, 6, False) 'Treat CTNTxtBox.Value as a number
PN = Application.VLookup(Val(CTNTxtBox.Value), rng, 7, False)
Test = Application.VLookup(Val(CTNTxtBox.Value), rng, 21, False)
If IsError(BN) Then BN = Application.VLookup(CTNTxtBox.Value, rng, 4, False) 'Treat CTNTxtBox.Value as text
If Not IsError(BN) Then BANTxtBox.Value = BN
If IsError(FN) Then FN = Application.VLookup(CTNTxtBox.Value, rng, 5, False) 'Treat CTNTxtBox.Value as text
If Not IsError(FN) Then FNameTxtBox.Value = FN
If IsError(LN) Then LN = Application.VLookup(CTNTxtBox.Value, rng, 6, False) 'Treat CTNTxtBox.Value as text
If Not IsError(LN) Then LNameTxtBox.Value = LN
PN = Application.VLookup(Val(CTNTxtBox.Value), rng, 5, False) 'Treat CTNTxtBox.Value as a number
Test = Application.VLookup(Val(CTNTxtBox.Value), rng, 21, False) 'Treat CTNTxtBox.Value as a number
'Code below doesn't work; seems to reach a ceiling
If IsError(PN) Then PN = Application.VLookup(CTNTxtBox.Value, rng, 7, False) 'Treat CTNTxtBox.Value as text
If Not IsError(PN) Then PhoneTxtBox.Value = PN
If IsError(Test) Then Test = Application.VLookup(CTNTxtBox.Value, rng, 21, False) 'Treat CTNTxtBox.Value as text
If Not IsError(Test) Then PhoneTxtBox.Value = Test
End Sub

Private Sub CTNTxtBox_Change()
Dim wb As Workbook
Dim ws As Worksheet
Dim rng As Range
Dim CTNumber As Variant, BN As Variant, FN As Variant, LN As Variant, PN As Variant, Test As Variant
CTNumber = CTNTxtBox.Value
Set rng = Worksheets("Feb252010_TM_Cycle1").Range("A1:ao300")
FNameTxtBox.Enabled = True
FNameTxtBox.BackColor = &H8080FF
LNameTxtBox.Enabled = True
LNameTxtBox.BackColor = &H8080FF
PhoneTxtBox.Enabled = True
PhoneTxtBox.BackColor = &H8080FF
TextBox1.Enabled = True
TextBox1.BackColor = &H8080FF

BN = Application.VLookup(Val(CTNTxtBox.Value), rng, 4, False)                'Treat CTNTxtBox.Value as a number
FN = Application.VLookup(Val(CTNTxtBox.Value), rng, 5, False)                'Treat CTNTxtBox.Value as a number
LN = Application.VLookup(Val(CTNTxtBox.Value), rng, 6, False)                'Treat CTNTxtBox.Value as a number
PN = Application.VLookup(Val(CTNTxtBox.Value), rng, 7, False)
Test = Application.VLookup(Val(CTNTxtBox.Value), rng, 21, False)
If IsError(BN) Then BN = Application.VLookup(CTNTxtBox.Value, rng, 4, False) 'Treat CTNTxtBox.Value as text
If Not IsError(BN) Then BANTxtBox.Value = BN
If IsError(FN) Then FN = Application.VLookup(CTNTxtBox.Value, rng, 5, False) 'Treat CTNTxtBox.Value as text
If Not IsError(FN) Then FNameTxtBox.Value = FN
If IsError(LN) Then LN = Application.VLookup(CTNTxtBox.Value, rng, 6, False) 'Treat CTNTxtBox.Value as text
If Not IsError(LN) Then LNameTxtBox.Value = LN
PN = Application.VLookup(Val(CTNTxtBox.Value), rng, 5, False)                'Treat CTNTxtBox.Value as a number
Test = Application.VLookup(Val(CTNTxtBox.Value), rng, 21, False)                'Treat CTNTxtBox.Value as a number
'Code below doesn't work; seems to reach a ceiling
If IsError(PN) Then PN = Application.VLookup(CTNTxtBox.Value, rng, 7, False) 'Treat CTNTxtBox.Value as text
If Not IsError(PN) Then PhoneTxtBox.Value = PN
If IsError(Test) Then Test = Application.VLookup(CTNTxtBox.Value, rng, 21, False) 'Treat CTNTxtBox.Value as text
If Not IsError(Test) Then PhoneTxtBox.Value = Test
End Sub

Open in new window

Runrigger

There appears to be a conflict with the value of your PN variant, 1st set at row 20, returning the value of column 7, then set again at row 28, but this time returning column 5, whereas your IsError test at row 31, reverts back to column 7??

"Test" maybe a reserved word, so try declaring a variable vTest instead at row 5 and propagating that change to rows 33 and 34

Cheers
Dave

ASKER CERTIFIED SOLUTION

Rory Archibald

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sarahstanshall

ASKER

Thanks to both of you; unfortunately my PhoneTxtBox & TextBox1 still does not populate:
Please see my code below:

Private Sub CTNTxtBox_Change()
Dim wb As Workbook
Dim ws As Worksheet
Dim rng As Range
Dim CTNumber As Variant, varMatch As Variant

CTNumber = CTNTxtBox.Value
Set rng = Worksheets("Feb252010_TM_Cycle1").Range("A1:z10")
FNameTxtBox.Enabled = True
FNameTxtBox.BackColor = &H8080FF
LNameTxtBox.Enabled = True
LNameTxtBox.BackColor = &H8080FF
PhoneTxtBox.Enabled = True
PhoneTxtBox.BackColor = &H8080FF
TextBox1.Enabled = True
TextBox1.BackColor = &H8080FF
' try matching as a number
varMatch = Application.Match(Val(CTNumber), rng.Columns(1), 0)
' if error, then try matching as text
If IsError(varMatch) Then varMatch = Application.Match(CStr(CTNumber), rng.Columns(1), 0)
' if there's a match, fill in data
If Not IsError(varMatch) Then
BANTxtBox.Value = rng.Cells(varMatch, 4)
FNameTxtBox.Value = rng.Cells(varMatch, 5)
LNameTxtBox.Value = rng.Cells(varMatch, 6)
PhoneTxtBox.Value = rng.Cells(varMatch, 7)

TextBox1.Value = rng.Cells(varMatch, 8)
End If
End Sub

Rory Archibald

Are you sure there is data in those columns then? If the others work, that's the only thing I can see that would cause that.

sarahstanshall

ASKER

yes there is data; I have attached the file. The code is in the ENTER CTN NUMBER box.
Thanks in advance for your help!!!
Winback-Zaven-1-.DerSahaguian5.xls

Runrigger

Sarah,

The values in both column 7 (which is hidden) and column 21 are both blank in the attached file.

Cheers
Dave

SOLUTION

byundt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

byundt

If it makes the code easier for you to understand, it is acceptable to use letters in the column position. Just remember that this trick only works correctly if the range variable starts with column A (which rng does in your code).
PhoneTxtBox.Value = rng.Cells(varMatch, "J") 'Returns a value from column J (Home number)
TextBox1.Value = rng.Cells(varMatch, "K") 'Returns a value from column K (Region)

Brad

Runrigger

Brad, I love this tip, any day is wasted if you don't learn at least one thing.

I have long been considered a so called "expert/guru" within my isolated workplace and I love being proven wrong by learning new technics.

Author, take note, this guy is a smart cookie.

Brad, perhaps this could be extended to ranges not starting at column A by using the offset/match functionality?

I'm exploring and not really relevant to this thread!

byundt

Dave,
.Cells uses addressing relative to the top left cell, just like VLOOKUP and INDEX in worksheet formulas--and index value (1, 1) refers to that top left cell. OFFSET and .Offset are slightly different in that (0, 0) refers to the parent cell.

The .Cells property is able to accept letters instead of numbers as its second parameter. These letters are interpreted as relative to the top left cell of a range object
Range("A5").Cells(1,"B").Address 'Returns $B$5, which is one column to the right of A5
Range("A5").Cells(1, 2).Address 'Returns $B$5, which is one column to the right of A5
Range("C5").Cells(1,"B").Address 'Returns $D$5, which is one column to the right of C5
Range("C5").Cells(1, 2).Address 'Returns $D$5, which is one column to the right of C5

Using the letter with a relative address will thus be somewhat confusing if your range doesn't start in column A.

The .Cells property also applies to worksheet objects, in which case using a letter provides an absolute reference to a particular column and no confusion occurs:
ActiveSheet.Cells(5, "C").Address 'Returns $C$5
ActiveSheet.Cells(5, 3).Address 'Returns $C$5

Because letter referencing can be somewhat confusing if a range variable doesn't start in column A, I use only numbers with the .Cells property in those circumstances. But when the range variable does start in column A, I often recommend that approach (letter referencing) because it is easier to read the code.

FWIW, if you click on the "byundt" at the top of this Comment and read my member profile, you'll see that I have been answering questions on this forum for several years. Like a number of other Experts who hang out in the Excel Zone, I got quite good at the intricacies of Excel after a while. If you'd like to join the "Excel regulars", we hang out in a series of threads like this one: http:/Excel/Q_25194799.html You strike me as being a kindred spirit, and we'd be glad to have you join us.

Brad

Rory Archibald

Dave,
When Brad says "I got quite good at the intricacies of Excel after a while", it's something of an understatement!! :)
Rory

Runrigger

lol, I get that too, my only regret is not finding you guys earlier!

As in EE that is.

sarahstanshall

ASKER

I am definitely on "Team Brad"; thanks again for the lesson :)

byundt

sarahstanshall,
I see that you changed your EEple after my teasing on the last thread. Thanks for being such a good sport!

May I assume that you took over this project from Rocelyn?

Brad

byundt

This points on this question ought to be split. Rory's Comment suggesting use of MATCH function in http:#28339622 is very much a part of the final solution.