Guru Help needed - offset cell problem

Posted by benny the boy on December 20, 2001 12:48 AM

Could someone help me with the following problem? When the following Find code runs, why will it only copy the first offset value and not any others? I've tried loads of things and nothing's worked. It's really annoying me now. Thanks

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
With Worksheets("Carcus_Data").Range("a1:a6000")
Set c = .Find(TextBox1.Value, LookIn:=xlValues, LookAt:= _
xlWhole)
If Not c Is Nothing Then
TextBox2.Value = c.Offset(0, 1).Value
TextBox3.Value = c.Offset(0, 2).Value
TextBox4.Value = c.Offset(0, 2).Value
Do

firstAddress = c.Address
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address &LT;&GT; firstAddress
End If
End With
End Sub

Posted by Colo on December 20, 2001 1:38 AM

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
With Worksheets(1).Range("a1:a6000")
Set c = .Find(TextBox1.Value, LookIn:=xlValues, LookAt:=xlWhole)
If Not c Is Nothing Then
firstAddress = c.Address
Do
Set c = .FindNext(c)
TextBox2.Value = c.Offset(0, 1).Value
TextBox3.Value = c.Offset(0, 2).Value
TextBox4.Value = c.Offset(0, 2).Value
Loop While c.Address &LT;&GT; firstAddress
End If
End With
End Sub

Posted by benny the boy on December 20, 2001 2:03 AM

Thanks, Colo, but it still doesn't work. Am I missing a reference or an add-in or something because I can't see why it doesn't work

Posted by Colo on December 20, 2001 4:03 PM

Hi. Sorry I mistook.Please try this.

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim c As Range, firstAddress As String
With Worksheets("Carcus_Data").Range("a1:a6000")
Set c = .Find(TextBox1.Value, LookIn:=xlValues, LookAt:=xlWhole)
If Not c Is Nothing Then
firstAddress = c.Address
TextBox2.Value = c.Offset(0, 1).Value
TextBox3.Value = c.Offset(0, 2).Value
TextBox4.Value = c.Offset(0, 2).Value

Do
If MsgBox("Do you continue Fine other value?", vbYesNo) = vbNo Then Exit Sub
Set c = .FindNext(c)
TextBox2.Value = c.Offset(0, 1).Value
TextBox3.Value = c.Offset(0, 2).Value
TextBox4.Value = c.Offset(0, 2).Value
Loop While Not c Is Nothing And c.Address &LT;&GT; firstAddress

End If
End With
End Sub

Posted by benny the boy on December 21, 2001 12:53 AM

Thanks Colo

Thanks Colo, this works a treat. And has preserved my sanity!

Happy Christmas. Philip