[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 281
  • Last Modified:

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

0
sarahstanshall
Asked:
sarahstanshall
  • 5
  • 4
  • 3
  • +1
2 Solutions
 
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
 
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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
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
 
byundtCommented:
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
 
byundtCommented:
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
 
byundtCommented:
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
 
byundtCommented:
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
 
byundtCommented:
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

Featured Post

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

  • 5
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now