Learn the fundamentals of Microsoft SQL Server, a relational database management system that stores and retrieves data when requested by other software applications.

I have a spreadsheet in which I'd like to avoid using hidden columns. Therefore I am looking to use a macro/VBA routine to do a calculation.

If I enter an amount in cell A2, I need a formula to scan a range of alternate cells on the same row (eg. C2, E2, G2, I2), and increment a counter for each cell that *does not* contain an 'x' char.

I then need to use the result of that macro to use in a formula for cell B2. Eg. =100/(result of formula)

Can this be done, and do you want more info?

Many thanks,

Steve :)

If I enter an amount in cell A2, I need a formula to scan a range of alternate cells on the same row (eg. C2, E2, G2, I2), and increment a counter for each cell that *does not* contain an 'x' char.

I then need to use the result of that macro to use in a formula for cell B2. Eg. =100/(result of formula)

Can this be done, and do you want more info?

Many thanks,

Steve :)

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get this solution by purchasing an Individual license!
Start your 7-day free trial.

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

=SUMPRODUCT(NOT(ISERROR(SE

It counts each cell in odd numbered columns (C, E, G, etc) that have an X anywhere in the value.

Kevin

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial=100/SUMPRODUCT(NOT(ISERRO

I'm not sure how you want to use the value in A2 though?

Kevin

The following macro stores a counter in the worksheet cell M1, checks columns C, E, G & I for the letter x in a given row, then place 100/cumulative count in column B. This macro works automatically when you enter data in cell A2. It must be installed in the code pane for the worksheet containing your data.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim x As Variant

Dim i As Long

Dim cel As Range, rgCounter As Range, targ As Range

Set targ = Intersect(Target, [A2]) 'Change the A2 to the range of cells you want to watch

If targ Is Nothing Then Exit Sub

For Each cel In targ.Cells

Set rgCounter = [M1]

For Each x In Array("C", "E", "G", "I")

If Not Cells(cel.Row, x) Like "*x*" Then i = i + 1

Next

rgCounter = rgCounter + i

cel.Offset(0, 1) = 100 / rgCounter

Next

End Sub

To install a sub in the code pane for a worksheet:

1) Right-click the sheet tab for the worksheet

2) Choose View Code from the resulting pop-up

3) Paste the suggested code in the resulting module sheet

4) ALT + F11 to return to the worksheet

If the above procedure doesn't work, then you need to change your macro security setting. To do so, open the Tools...Macro...Security menu item. Choose Medium, then click OK.

Hoping to be helpful,

Brad

Thank you, the formula works a treat, but I can't understand it - even when I try to break it down. Would it be possible for you to explain it to me please?

And thank you for your macro suggestion too, Brad.

Steve :)

NOT(ISERROR(SEARCH("X",C2)

The above does a search for an "X" in cell C2 and, if present, does not generate an error which means the NOT(ISERROR(...)) part returns TRUE. When used in a calculation TRUE is the same as 1. This is multiplied by the result of the function MOD(COLUMN(C2),2) which returns 0 for even columns and 1 for odd columns. C2 is an odd column (3) so MOD(COLUMN(C2),2) returns a 1. TRUE*1 = 1 and we now have the first result to sum. Next SUMPRODUCT looks at D2. But this is not a column you want to evaluate so the MOD(COLUMN(D2),2) returns zero which means the SEARCH part is irrelevant (TRUE*0 = FALSE*0 = 0). We now have a second value to sum (0). Same process with E2, F2, and so one.

After evaluating the array SUMPRODUCT has a result array to sum. Assuming there is an X in C2, an X in E2, and no X in G2 then the intermediate array looks like {1, 0, 1, 0, 0, 0 ...}. (Remember that there are array element results for the even columns as well but they will always be zero.) SUMPRODUCT sums that array and produces a value of 2.

Kevin

Thanks again,

Steve :)

=100/SUMPRODUCT(NOT(ISERRO

Or do you want to check for X's in even columns now like D, F, and H?

Kevin

Thanks again,

Steve :)

Try modifying Kevin's formula to:

=100/SUMPRODUCT(NOT(ISERRO

The modification assumes that the first column in the range C2:I2 should always be counted, as is every other one after that.

Brad

I like Brad's modification. He has changed the formula from "look at every odd column" to "look at the first, third, etc. column". The way the MOD function works is it returns the modulo or remainder after a division by the specified denominator:

MOD(0,2) = 0

MOD(1,2) = 1

MOD(2,2) = 0

By subtracting the starting column from the focus column and then adding one Brad has made the MOD function work in a relative mode versus an absolute mode. As you insert and delete columns before the columns being evaluated, the references in the formula will shift automatically but continue to look at the first, third, etc. column.

Kevin

Microsoft Excel

From novice to tech pro — start learning today.

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get this solution by purchasing an Individual license!
Start your 7-day free trial.

You can use www.ee-stuff.com

-Daniel