input box loop
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
--
Cheers,
Ryan
"tpeter" wrote:
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
|