Help with code please?

G

Greg

Hi all I am using this code so when a person puts number into text box a it
gives the details in textbox's 2&3. How can I get this code to look in
column "f" in this column I have a yes/no section. If the corresponding
number is NO I want a pop up to mention this person is not financial.

How do I change the code below to do this?


Private Sub TextBox1_Change()



Dim ans

On Error Resume Next
ans = Application.Match(CLng(TextBox1.Text), Range("A1:A100"), 0)
If Not IsError(ans) Then
TextBox2.Text = Application.Index(Range("B1:B100"), ans)
TextBox3.Text = Application.Index(Range("C1:C100"), ans)
R = 1 'or whatever desired row variable
Sheets("sheet5").Cells(R, 1).Value = UCase(TextBox1.Text)
Else
UserForm2.Show
End If
On Error GoTo 0

End Sub

Thanks again

Greg
 
O

OJ

Hi Greg,
not sure I fully understand but try this...
....
If Not IsError(ans) Then
If cells(ans,6).value = "NO" Then MsgBox "Person is not
Financial!"
TextBox2.Text = Application.Index(Range("B1:B1­00"),
ans)...etc
OJ
 
J

JulieD

Hi Greg

(how's sunny adelaide this morning - did you get any sleep?)

how about:
Private Sub TextBox1_Change()
Dim ans

On Error Resume Next
ans = Application.Match(CLng(TextBox1.Text), Range("A1:A100"), 0)
If Not IsError(ans) Then
TextBox2.Text = Application.Index(Range("B1:B100"), ans)
TextBox3.Text = Application.Index(Range("C1:C100"), ans)
If UCase(Application.Index(Range("F1:F100"), ans)) = "NO" Then _
MsgBox "Warning: This person is not financial!"
 
G

Greg

Yeah Thanks Julie, Slept Well for how little I got, It is very hot here
today.

Thanks Julie it works Perfectly

Greg
 
J

JulieD

you're welcome

we've got rain here today (yipeee, yipee, yay!) .. so you should get it in a
day or two

good luck with the rest of your excel project - it seems quite a major
undertaking
 
A

augustus

maybe:

Private Sub TextBox1_Change()

Dim lngAns As Long

On Error Resume Next
'assuming Sheet1.Range("A1:A100") only have unique value
lngAns = CLng(Application.WorksheetFunction.Match(CLng(TextBox1.Text),
Sheet1.Range("A1:A100"), 0))
If Not IsError(ans) Then
TextBox2.Text = Sheet1.Cells(lngAns, "B").Value
TextBox3.Text = Sheet1.Cells(lngAns, "C").Value
R = 1 'or whatever desired row variable
Sheets("sheet5").Cells(R, 1).Value = UCase(TextBox1.Text)
Else
UserForm2.Show
End If
On Error GoTo 0

End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Auto look up 1
Name look up 0
Please help with code? 2
Help with code please? 2
Help with code please 1
Error control? 2
Help with code please 4
Help Please 0

Top