Help with code please?
 

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

Help with code please?
 

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

Help with code please?
 

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

---
cheers
JulieD


"Greg" wrote in message
...
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

Help with code please?
 

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

Thanks Julie it works Perfectly

Greg

Help with code please?
 

Thanks OJ

Greg

Help with code please?
 

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


"Greg" wrote in message
...
Yeah Thanks Julie, Slept Well for how little I got, It is very hot here
today.

Thanks Julie it works Perfectly

Greg

Help with code please?
 

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(Text Box1.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

"Greg" wrote:

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