Help with code please?

[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

 

[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

 

[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!"

 

[Greg]

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

Thanks Julie it works Perfectly

Greg

 

[Greg]

Thanks OJ

Greg

 

[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

 

[Guest]

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