Need help with VBA code

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 :)
LVL 9
sda100Asked:
Who is Participating?
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.

GarrettyCommented:
Can you upload an example online, im not 100% sure what you are trying to do.

You can use www.ee-stuff.com


-Daniel
zorvek (Kevin Jones)ConsultantCommented:
Try this formula:

=SUMPRODUCT(NOT(ISERROR(SEARCH("X",C2:I2)))*MOD(COLUMN(C2:I2),2))

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
zorvek (Kevin Jones)ConsultantCommented:
More specifically, cell B2 would contain this formula:

   =100/SUMPRODUCT(NOT(ISERROR(SEARCH("X",C2:I2)))*MOD(COLUMN(C2:I2),2))

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

Kevin
Exploring SQL Server 2016: Fundamentals

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

byundtMechanical EngineerCommented:
Steve,
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
sda100Author Commented:
zorvek,

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 :)
zorvek (Kevin Jones)ConsultantCommented:
The SUMPRODUCT formula works like an array formula: it processes each cell in the range provided individually and then sums the results. So it first looks at cell C2:

   NOT(ISERROR(SEARCH("X",C2)))*MOD(COLUMN(C2),2))

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
sda100Author Commented:
Kevin, you are God!

Thank you very much indeed.

Steve :)
sda100Author Commented:
Sorry Kevin, one last thing... the formula only works when entered in an odd-numbered column.  I can't figure out if/where/how to use the column() function to take this into account....

Thanks again,
Steve :)
zorvek (Kevin Jones)ConsultantCommented:
The formula is designed to work in any column but the references may be shifting. This version locks the columns down:

   =100/SUMPRODUCT(NOT(ISERROR(SEARCH("X",$C2:$I2)))*MOD(COLUMN($C2:$I2),2))

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

Kevin
sda100Author Commented:
Sorry for dragging this out Kevin.  I've identified the problem as the MOD part of the formula.  When the range (which may be 9 cells wide) starts on an odd-numbered column, the MOD part works on the 1st column, as MOD(<odd_number>,2)=1.  But if the range starts on an even number, the first MOD result will be 0.  I might add/remove columns before the range, so the formula may work/not work accordingly.  Somehow I need to make the 1st column always appear as an odd-numbered column... Hmm... how to do that...

Thanks again,
Steve :)
byundtMechanical EngineerCommented:
Steve,
Try modifying Kevin's formula to:
=100/SUMPRODUCT(NOT(ISERROR(SEARCH("X",$C2:$I2)))*MOD(COLUMN($C2:$I2)-COLUMN($C2)+1,2))
The modification assumes that the first column in the range C2:I2 should always be counted, as is every other one after that.

Brad
zorvek (Kevin Jones)ConsultantCommented:
Steve,

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
sda100Author Commented:
You guys rock, thank you so much!  It's a shame I can't give out any more points.

Steve :)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.