Excel Solution that I don't understand

  • Thread starter Thread starter Gary Thomson
  • Start date Start date
G

Gary Thomson

I posed the following question yesterday:

I have the following strings in the following cells:

A B C D E F G H
1 1-Feb 2-Feb 3-Feb 4-Feb 5-Feb 6-Feb 7-Feb 8-Feb
2 a b bc bd be bcd bd d


For each day, I want to know how many consecutive days one
of the letters has appeared in that cell (where two
letters appear, we take the maximum duration of the two).

The answers would be displayed in the range J2:Q2 as
follows:

A B C D E F G H
1 1-Feb 2-Feb 3-Feb 4-Feb 5-Feb 6-Feb 7-Feb 8-Feb
2 1 1 2 3 4 5 6 3

Since:

On the 1st feb, "a" has appeared in 1 consecutive cell

On the 2nd Feb, "b" has appeared in one consecutive cell

On the 3rd feb, "b" has appeared in 2 consecutive
cells, "c" has appeared in 1 consecutive cell, so we take
the maximum

and so on......
How can I do this?

And Bernie Deitrick gave me the following answer:

Gary,

If you don't mind using a User-Defined-Function, then copy
the code below
and paste it into a regular code module in the workbook,
then enter

=maxApp($A$2:A2)

in cell A3, and copy as far right as you have data.

HTH,
Bernie
MS Excel MVP

Function MaxApp(inRange As Range) As Integer
Dim myVal As String
Dim i As Integer
Dim j As Integer
Dim TempMax As Integer
Dim TempVal As Integer

MaxApp = 1

myVal = inRange(inRange.Cells.Count).Value
For i = 1 To Len(myVal)
TempVal = 1
For j = inRange.Cells.Count - 1 To 1 Step -1
If InStr(1, inRange(j).Value, Mid(myVal, i, 1)) Then
TempVal = TempVal + 1
Else
Exit For
End If
MaxApp = Application.Max(MaxApp, TempVal)
Next j
Next i

End Function


However, I dont understand the steps (i.e. what is a User-
defined-function, what module do I paste it into, etc)

Could someone explain this to me please? Or is there a
formula I can put in the cell which will calculate it?
 
Gary,

A UDF is a worksheet function that is not built-in to Excel, someone has
added it.

Bernie's code is complete, all you need to do is add it.

Go into the VB IDE (Alt-F11)
Select your workbook from the list in the top left window (all open work
books will be there, and maybe a couple of other you won't recognise. Got
menu Insert>Module, and then paste Bernie's code into the window that pops
up.

To use, put

=maxApp($A$2:A2)

in J2, and copy across.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
THanx Bob,

I have added this to the workbook.

But when I put maxApp into J2 and copy across I dont get:

A B C D E F G H
1 1-Feb 2-Feb 3-Feb 4-Feb 5-Feb 6-Feb 7-Feb 8-Feb
2 1 1 2 3 4 5 6 3

instead I get:

A B C D E F G H
1 1-Feb 2-Feb 3-Feb 4-Feb 5-Feb 6-Feb 7-Feb 8-Feb
2 1 1 1 1 1 1 1 1

What has went wrong???
 
Gary,

I think you have added the formula incorrectly.

If I enter =maxApp(A2:A2), I get the same results as you.

If I enter, =maxApp($A$2:A2), I get the correct results. Not the $, they are
critical.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
My Mistake!!!

It is now correct, except that where there is no data in a
cell, there should be "0" in the corresponding cell, but
it is always giving me a "1" (say if there was no "b" in
B2, I would want "0" in cell K2, but this formula is
giving me a "1"). What do I need to put in to get this?
 
Gary,

You could modify the code to do it, but it is simpler to amend the formula
to

=IF(A2="",0,maxapp($A$2:A2))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Of course!!!

That is so simple - I was looking for this difficult
solution and there it was, staring me right in front of my
eyes.

Thank you very much.
 
Back
Top