Need help with VBA code

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

You can use www.ee-stuff.com


-Daniel

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

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

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 :)

Kevin, you are God!

Thank you very much indeed.

Steve :)

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 :)

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

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 :)

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

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

You guys rock, thank you so much!  It's a shame I can't give out any more points.

Steve :)