Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
input box loop
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
input box loop
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 -- Jacob "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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
input box loop
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. 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 -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
input box loop
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. "Jacob Skaria" wrote: 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 -- Jacob "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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
input box loop
See my post.
-- Cheers, Ryan "tpeter" wrote: 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. "Jacob Skaria" wrote: 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 -- Jacob "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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
input box loop
Ryan, worked like a charm. Thank you both so much for your help. It will make
my life a lot easier on this project. "Ryan H" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SQL loop of input values | Excel Programming | |||
Urgent help with input box & loop | Excel Programming | |||
Input box with a loop | Excel Programming | |||
endless loop - using input box | Excel Programming | |||
Loop through a range, allow user input during... | Excel Programming |