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

sarahstanshallAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

RunriggerCommented:
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
0
Rory ArchibaldCommented:
It would be more efficient to use Match rather than looking up the same value repeatedly:

Private Sub CTNTxtBox_Change()
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim rng As Range
    Dim CTNumber As Variant, varMatch 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
    ' 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)
        ' not sure what the logic is for Test??
        'Test = rng.Cells(varMatch, 21)
    End If
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sarahstanshallAuthor Commented:
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
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

Rory ArchibaldCommented:
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.
0
sarahstanshallAuthor Commented:
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
0
RunriggerCommented:
Sarah,

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

Cheers
Dave
0
byundtMechanical EngineerCommented:
To elaborate on Dave's comment, you need to be counting both the columns that are visible as well as those that are hidden when using .Cells to return a value.

In your posted workbook, columns G and H were hidden. They furthermore contained blanks. So:
PhoneTxtBox.Value = rng.Cells(varMatch, 7)       'Returns a value from column G, which is blank
TextBox1.Value = rng.Cells(varMatch, 8)             'Returns a value from column H, which is also blank

You should have been using:
PhoneTxtBox.Value = rng.Cells(varMatch, 10)       'Returns a value from column J (Home number)
TextBox1.Value = rng.Cells(varMatch, 11)             'Returns a value from column K (Region)
0
byundtMechanical EngineerCommented:
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
0
RunriggerCommented:
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!
0
byundtMechanical EngineerCommented:
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
0
Rory ArchibaldCommented:
Dave,
When Brad says "I got quite good at the intricacies of Excel after a while", it's something of an understatement!! :)
Rory
0
RunriggerCommented:
lol, I get that too, my only regret is not finding you guys earlier!

As in EE that is.
0
sarahstanshallAuthor Commented:
I am definitely on "Team Brad"; thanks again for the lesson :)
0
byundtMechanical EngineerCommented:
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
0
byundtMechanical EngineerCommented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.