How to Loop through 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

Please Login or Register  to view this content.

Im sure theres loads of different ways, heres my thoughts.

1. To cycle through column A looking for zeros:

Public currRow As Integer

Please Login or Register  to view this content.

The Id have a userform (in this example called frmAction) with 3 buttons and the following code

Please Login or Register  to view this content.

You'd obviousy change the msgbox line to whatever actions you need

I tried to upload a basic example but the forums playing up

Hi,

That was a helpful start. I added DIM statements for the wsCell and currRow to make it mostly work. Some code was needed to identify if there are no cells with the value of 0. If that is the case, the code stops and produces a message like the one shown below. Finally, if a 0 is found, code was added to scroll to the cell with the 0 value so that row can be seen so that the user knows which option to select (i.e., which CommandButton to click) on the UserForm.

However, there are a few things that still need to be solved. The values of the cells are for some reason not getting updated. Also, since one of the user's options on the UserForm is to not modify the value of 0, the code needs to exit once all of the records have been reviewed.

I have attached my modified code to give you an idea of where I'm trying to go, but it's not producing the desired results. Any more direction you can provide will be greatly appreciated!

Please Login or Register  to view this content.