How to Loop thru Column and Modify Values Based on User Input
Hi,
I have a file where I need a macro to search for a value of 0 in the first column and then do one of the following:
(1) Change the value in the active cell in column A where the 0 was found to a value of 9, copy the value from one row up in column B (Offset(-1,1), and paste that value into column B in the same row where the 0 was found (Offset(0,1).
(2) Change the value in the active cell in column A where the 0 was found to a value of 9, copy the value from one row below in column B (Offset(1,1), and paste that value into column B in the same row where the 0 was found (Offset(0,1).
OR
(3) Choose to leave the value in the active cell in column A as 0.
The macro opens a TXT file, moves some columns, and then searches for the value 0 in column A. The basics of that are below. I would like to add some buttons to the TXT file which would allow the user to click on one button to perform the action in #1 above, to click on a second button to perform the action in #2 above, or to click on a third button to leave the value as is, with any of these buttons clicked then looping to the next found instance of 0 after performing the action. Unfortunately, my looping programming skills are weak and I'm not sure at all how to proceed with creating buttons on the fly to do this. Any help you can provide would be greatly appreciated!
Thanks!
Bryan
Code:
Dim RNGFOUND As Range
Dim Found0 As Long
Range("A2").Select
Set RNGFOUND = ActiveSheet.Columns("A:A").Find(What:="0", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
If Not RNGFOUND Is Nothing Then
ActiveCell.Select
Found0 = RNGFOUND.Row
MsgBox "0 (a new MCP) was found in cell A" & Found0 & ". Change 0 to 9 and copy ERLINKID to new MCP if part of an existing employer.", vbInformation
Else
MsgBox "0 Not Found--No New MCPs"
End If
End Sub
Code:
Sub New_MCP_Is_Part_of_Existing_Employer()
Windows("NEWMCPS3.TXT").Activate
ActiveSheet.OLEObjects.Add(ClassType:="Forms.ToggleButton.1", Link:=False, DisplayAsIcon:=False, Left:=305, Top:=15, Width:=200, Height:=50).Select
Selection.Name = "UseERLINKIDFromPREVIOUSRecord"
Selection.OnAction = "Toggle1"
ActiveSheet.Shapes("New Button").Select
Selection.Characters.Text = "Change 0 to 9 and use ERLINKID from PREVIOUS record (1 row above)"ActiveSheet.OLEObjects.Add(ClassType:="Forms.ToggleButton.1", Link:=False, DisplayAsIcon:=False, Left:=305, Top:=80, Width:=200, Height:=50).Select
Selection.Name = "UseERLINKIDFromNEXTRecord"
Selection.OnAction = "Toggle2"
ActiveSheet.Shapes("New Button").Select
Selection.Characters.Text = "Change 0 to 9 and use ERLINKID from NEXT record (1 row below)"
End Sub
Code:
Sub Toggle1()
ActiveCell.Select = "9"
ActiveCell.Offset(0, 1).Value = ActiveCell.Offset(-1, 1).Value
End Sub
Sub Toggle2()
ActiveCell.Select = "9"
ActiveCell.Offset(0, 1).Value = ActiveCell.Offset(1, 1).Value
End Sub
