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 WorkbookDim ws As WorksheetDim rng As RangeDim CTNumber As Variant, BN As Variant, FN As Variant, LN As Variant, PN As Variant, Test As VariantCTNumber = CTNTxtBox.ValueSet rng = Worksheets("Feb252010_TM_Cycle1").Range("A1:ao300")FNameTxtBox.Enabled = TrueFNameTxtBox.BackColor = &H8080FFLNameTxtBox.Enabled = TrueLNameTxtBox.BackColor = &H8080FFPhoneTxtBox.Enabled = TruePhoneTxtBox.BackColor = &H8080FFTextBox1.Enabled = TrueTextBox1.BackColor = &H8080FFBN = Application.VLookup(Val(CTNTxtBox.Value), rng, 4, False) 'Treat CTNTxtBox.Value as a numberFN = Application.VLookup(Val(CTNTxtBox.Value), rng, 5, False) 'Treat CTNTxtBox.Value as a numberLN = Application.VLookup(Val(CTNTxtBox.Value), rng, 6, False) 'Treat CTNTxtBox.Value as a numberPN = 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 textIf Not IsError(BN) Then BANTxtBox.Value = BNIf IsError(FN) Then FN = Application.VLookup(CTNTxtBox.Value, rng, 5, False) 'Treat CTNTxtBox.Value as textIf Not IsError(FN) Then FNameTxtBox.Value = FNIf IsError(LN) Then LN = Application.VLookup(CTNTxtBox.Value, rng, 6, False) 'Treat CTNTxtBox.Value as textIf Not IsError(LN) Then LNameTxtBox.Value = LNPN = Application.VLookup(Val(CTNTxtBox.Value), rng, 5, False) 'Treat CTNTxtBox.Value as a numberTest = Application.VLookup(Val(CTNTxtBox.Value), rng, 21, False) 'Treat CTNTxtBox.Value as a number'Code below doesn't work; seems to reach a ceilingIf IsError(PN) Then PN = Application.VLookup(CTNTxtBox.Value, rng, 7, False) 'Treat CTNTxtBox.Value as textIf Not IsError(PN) Then PhoneTxtBox.Value = PNIf IsError(Test) Then Test = Application.VLookup(CTNTxtBox.Value, rng, 21, False) 'Treat CTNTxtBox.Value as textIf Not IsError(Test) Then PhoneTxtBox.Value = TestEnd Sub

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

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 IfEnd Sub

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

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.

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

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)

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)

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.

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

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.

"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