A "for/next" loop?

[jfcutler]

All --

(See attachment to follow what I'm saying/asking). Initially, Cols. A, B, C and D were imported as a comma-separated text file from a database. Col. F was entered by hand.

DESIRED RESULTS: to manually enter A3, B3, and col C (which will typically be a constant, but the values can vary), plus the list in col. F (this list can also vary).

Calculate the rest of cols A, B, D.

As I see it ---

Step 1 -- Col B contains a set of dates, calculated from Col C (this calc works as-is).

Step 2 -- Calc those cells called FREE in Col D. The concept is: any cell labeled FREE has the same month as the row before it -- see B5:6. Conditional formatting makes the text be red (this works as-is).

Step 3 -- (this is what I need help with). The list of "cleansing types" (however many items it contains) must be cycled in order in col D for all dates that are not labeled FREE. See rows 3 thru 13. The repetition is "regular" through D9, but note that D12 (FREE) 'interrupts' the cycle and "Purge" jumps over FREE to be in D13. The same happens in D18 and D19.

I don't know how to calculate either the "looping" through the "Cleansing types" nor the "jumps" over the FREEs.

All this is easy enough in the database (where these columns were originally generated) because I can use program control containing nested FOR/NEXT loops to cycle the Cleansing types record-by-record and IF logic to make the "jumps" over the FREEs.

Thanks, John

[Leith Ross]

Hello John,

Welcome to the Forum!

It would help to see an after example of table. Can you post one?

[mrice]

Try adding this event driven macro to the Sheet1 tab in the VBA editor (Alt F11).

Please Login or Register  to view this content.

It will fire every time a change to the sheet is made and update the contents of column D.

[jfcutler]

Thanks, Martin -- it works!

I don't know the VBA language, but I can mostly follow the program. I expected that regular formulas within the sheet cells would not be able to do the calcs.

Since the number of items in col F can change (not always 3), is there a way to 'pass a variable' to the VBA routine? Or is this handled by "Set TypeRange = Cells(3, 6).CurrentRegion"?

Can VBA count the filled cells col. F? There are also other places in the code that are now constants, and I presume they would all need to be variables.

The attachment I sent is a simplified sample of sheet 1 from an entire workbook. In the real .xls file, the Cleansing types are in Col J. Does "Cells(3, 6)" have to be changed to "Cells(3, 10)"?

Also, in the real workbook, 4 is the first row of data, so changed to N = 4

{{ I just tried these two changes for the real workbook - the answer is YES and YES }}

So the general question becomes: can the whole VBA be variable-ized? MAYBE I NEED TO LEARN VBA IN MY SPARE TIME....! Ha!

----------------------------------------

When I open the workbook, I get a message about "Security warning..." and I have to click on [ Enable Macros ]. Is there any way to tell Excel that all the macros in this workbook are virus-free and thus bypass this message? Or can the macro be executed once, on command, when the workbook is first created, and then "turned off'?

----------------------------------------

From my original post: "any cell labeled FREE has the same month as the row before it -- see B5:6. Conditional formatting makes the text be red (this works as-is)."

I'm wondering about consolidating the calculations to label "FREE" into your VBA code. Some concept like this (in red):

Please Login or Register  to view this content.

----------------------------------------

I've opened several cans of worms here.... Again, thanks. John

======================= EDIT =====================

Oops -- the logic in my little example is 'upside down'. The IF condition: month of B,row = month of B,row-1 would have to come first; and if that is false, then insert the appropriate Cleansing type...

John

[Marcol]

Hmm?
I think this might be doable with native formula and a dynamic named list

Name:= "CT_List"
Refers to:=

Please Login or Register  to view this content.

Then in D3

Please Login or Register  to view this content.

Drag/Fill Down

Notes
1/. You have a blank row (Row 2) for these formula to work this row must remain blank, at least in ColumnsB, D, and F. (The formulae would have to be adjusted if this row is removed, or if values are added)

2/. The formula in G3

Please Login or Register  to view this content.

might be useful as a helper cell.
You could replace all instances of INDEX(CT_List,COUNTA(CT_List),1) in the main formula with $G$3

3/. Columns I:K are a check with your posted desired results

4/. Columns M:O are for 2007 and above, I left them in because I used 2007 to work this out and it shows the simpler formula offered with IFERROR().
Column N will return #Name? with 2003.

Columns G:O can be deleted.

5/. your C/F formula could be

Please Login or Register  to view this content.

Applies to:=

Please Login or Register  to view this content.

Try Changing the values in Column F, or adding/deleting values.
Just keep the list continuous (no blanks) with the exception of cell F2, which must remain blank. F1 must always contain a header, it can be any text you need.

Hope this might help

[jfcutler]

Martin --

Here's another thought/questions.

A few years back, I used AutoCAD in my job and did some customization to the menus and toolbars. In a nutshell, various macros (VBA, LISP, etc.) were launched as-needed by custom toolbar buttons.

I'm wondering if Excel can have custom toolbar buttons for launching macros. I think it would be useful to have your VBA be an as-needed executable rather than an always-running macro. I say this because (a) the 'template' of this workbook may eventually be cloned and used for additional projects, and (b) this first sheet in the workbook will serve multiple roles for any given user.

For a new user, the first step is to set up the starting values in cols. A3, B3, C3 (and beyond if they are not all 25 days). Formulas within these columns would be copied down to create whatever date range was needed.

Step 2: enter the Cleansing types list (col. F).

Step 3: run the VBA macro - just once (from a toolbar button?) - to fill in col. D.

Step 3: This workbook manages cancer treatments (mine). In daily use, there may be need, from time to time as circumstances require, to make manual changes to col. D -- such as changing or swapping Cleansing names. In that case, the macro would be counter-useful (if it were still active) because it would immediately override the manual changes. Similarly, having native formulas within the cells of col. D (as suggested by Marcol) would conflict with later manual overrides.

John

[Leith Ross]

Hello John,

It obvious you are competent with using other tags when you post. Please enclose code in code tags. It makes it easier to see, copy, and edit.

[mrice]

Quite a few questions here

Yes - Excel can have custom toolbars and buttons and there are plenty of examples out there which will show what the posibilities are.

The challenge with making manual changes will be to identify which data items have been populated automatically and which manually. Possibilities are colour, font etc or an annotation in an adjacent column. The code could then avoid overwriting these and even make use of them in deciding what comes next in the sequence.

[jfcutler]

OK -- too many questions. I'm going to try mods to the original VBA and see what I can do.... To be continued.

John

[jfcutler]

Martin --

Got it (I think....!!!). Following some experimenting, I ended with the addition of 2 more FOR/NEXT loops into your original VBA. The first clears all labels in col. D; the second loop plugs "FREE" where appropriate; the 3nd (your original) loop plugs the Cleansing labels as appropriate. (See attachment v4).

I'm thinking that getting the macro to recalc only rows following manual label swapping in D, etc., might work from a cell reference whose value (eg. "8") replaces N = (constant) in each FOR statement.

I'll play with this later (other things to do now)..... and check out the custom toolbar buttons.

Thanks, John

Please Login or Register  to view this content.