input box loop

[tpeter]

I have a large spreadsheet in excel 2003 that I need to find a number and
offset it to input new data. I have this code working:

Sub findbadge()
'
' findbadge Macro
' Macro recorded 2/9/2010 by tpeter
'
strBadge = InputBox("Enter Badge Number", "Badge Number")
Range("B15").Select
Cells.Find(What:=strBadge, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
With ActiveCell
.Offset(, 20).Select
End With

End Sub

There are 4 pieces of data that I need to put in (this adjusted me over 4
columns). When I put the last number in (column "Y") I want it to repeat the
above macro. any help would be great, and thank you for your help.

Tim Peter

 

[Jacob Skaria]

Check out the VBA help on .FindNext method

Example (from help)
This example finds all cells in the range A1:A500 that contain the value 2
and changes their values to 5.

With Worksheets(1).Range("a1:a500")
Set c = .Find(2, lookin:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Value = 5
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With

 

[Dave Peterson]

This manual technique may not work for you--but when I'm doing data entry, I
find it one of the easier ways.

I'd select the range that could have the badge numbers (all of column B???).
Then apply data|filter|autofilter

Then filter to show the badge number you want.

Then only those rows that have have the badge number are visible.

If the cells that I need to change are pretty far away from column B, I'll
(temporarily) hide the intermediate columns.

Do all my data entry (all 4 columns), show the columns, and show all the rows.

 

[Ryan H]

Put this code in your worksheet module. To do this, right click the
worksheet tab at the bottom, click "View Code", then paste code below in the
sheet module. Then run your macro and see what happens. Hope this helps!
If so, let me know, click "YES" below.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("Y:Y")) Is Nothing Then
Call FindBadge
End If

End Sub

Private Sub FindBadge()

Dim strBadge As String
Dim rngBadge As Range

strBadge = InputBox("Enter Badge Number", "Badge Number")

If strBadge <> "" Then
Set rngBadge = Cells.Find(What:=strBadge, _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

If Not rngBadge Is Nothing Then
rngBadge.Offset(, 20).Select
Else
MsgBox "Badge # " & strBadge & " was not found."
End If
End If

End Sub

 

[tpeter]

Jocob thank you for your help. I am a little confused. The macro I created
works the only thing I need to do is some type of trigger for the macro to
run again. Once the number is found in column b then it offsets 20 columns
and stops. That is great then I enter my numbers accordingly an example would
be the macro found info in cell c14 it then offsets to v14, I enter
information in cells v14,w14,x14v and y14. When I exit y14 I want the macro
to run again.

 

[Ryan H]

See my post.
--
Cheers,
Ryan

Jocob thank you for your help. I am a little confused. The macro I created
works the only thing I need to do is some type of trigger for the macro to
run again. Once the number is found in column b then it offsets 20 columns
and stops. That is great then I enter my numbers accordingly an example would
be the macro found info in cell c14 it then offsets to v14, I enter
information in cells v14,w14,x14v and y14. When I exit y14 I want the macro
to run again.

 

[tpeter]

Ryan, worked like a charm. Thank you both so much for your help. It will make
my life a lot easier on this project.