VBA - Enter value, count value, enter count, clear first value, repeat until end of data.

[Luisftv]

I have looked at/watch many tutorials and samples but none address my particular dilemma.

I need VBA code that when I push a button would do the following:

1. Enter value “X” in column S, cell (S1)
2. Count values (how many “YES”, or whatever is there) in a range (BA59:CO59)
3. Input count in column EA, cell EA1, corresponding to the row for S1,
4. Clear first value entered in S1
5. Repeat on next column cell (S2) until the end of the column (S29). Place “X” in S2, etc.

In other words:

1. STEP ONE. In column S, cell S1, enter “X”.
2. STEP TWO. Then, in another part of the sheet (range BA59:CO59), count how many times “YES” is found in that range (in this particular example, the range is made of nine cells). As of now, there are 4 “YES” in this range. I can be anything aside from “YES” too.
3. STEP THREE. Then input that value (4) into cell EA1, which corresponds to the same row as where the “X” was entered in cell S1.
4. STEP FOUR. Clear the “X” from S1, in column S.
5. STEP FIVE. Enter “X” on S2 of column S and repeat the process… until end of column data (S29). Then the code should stop. Of course, pressing the button again will repeat the whole process each time.

NOTE (just for reference): in step two, the range of nine cells where the “YES” are found is a dynamic range in my actual workbook. The “YES” count will change dynamically according to which row will have the “X” in column S. In other words, each time the “X” is moved to another row in column S, that will automatically trigger a new calculation (formulas are in place to do so in my actual worksheet) which will give a different “YES” count.

In the attached file, the dynamic calculation from where the “YES” count is derived is not included because I was not able to successfully copy that area into this sample file (the formulas in place require a lot of manual adjustment since they are using INDIRECT and VBA code and such to link to other sheets.

In other words, the VBA code in this sample file will output a 4 count for each row in column S (unless the “YES” cells are manually modified). But it should automatically find the right count once I use it in the actual workbook.

Also, I do have data in between columns S and EA and I do have other VBA code in place. I am hoping that the VBA solution provided will be a module or sheet code that will not interfere with other modules or subs. The attached file has no VBA.

Thanks and I truly appreciate the help given.

[Joske920]

this should do it

Please Login or Register  to view this content.

[Luisftv]

Yes it did!!!

Brilliant!!! Amazing!!!

I changed the code you provided to use COUNTIF instead of COUNTA because with COUNTA it was only counting if the range had text (the "YES") and it was also counting blank cells when there was no text in them. In reality, in that range I have an IF formula and the COUNTA didn't work with that. That's my fault. I didn't think of that. Sorry.

Also I added a line of code to FIRST start by clearing the destination range (EA). After running the code I realized that (in the actual workbook) there were left overs from the prior run (in my actual workbook, sometimes the range goes to 23 or 50 or 100, etc).

Thank you so much.

NOTE: I will mark "SOLVED" on this post after a few days because for some reason my posts magically "vanish" when I mark them solved on the same day.

I include here the code with the trivial changes I made and the working file for future reference.

Please Login or Register  to view this content.