You were right, Tom. I think it's one of those things where I was staring at
it for so long that I wasn't thinking straight. I hadn't trimmed column A.
Once I did that, it worked perfectly. Thanks again for your help.
Phil
"Tom Ogilvy" wrote:
> Before I posted, It was tested and worked for me. I put RD and SPC in the
> rows where there were subtotals in the data. They were excluded from the
> calculations and gave the same results as worksheet functions which were
> applied to the appropriate subsets of the data.
>
> Formula worked both in code and using the formula it produces in a
> worksheet. So no, I wouldn't think it needs to be changed except for
> changing the RD/SPC to match the strings in column A. Possibly those cells
> contain "RD " or " RD" for example which of course do not equal "RD" and
> those rows would not be excluded.
>
>
>
> --
> Regards,
> Tom Ogilvy
>
> "Phil Trumpy" <(E-Mail Removed)> wrote in message
> news:252C5233-8D8A-4308-BA43-(E-Mail Removed)...
> > Thanks for the reply Tom. I can see basically what your code is
> > attempting
> > to do, however, I get the same result as before. I am not sure I get this
> > line:
> >
> > s2 = "If(((" & s1 & "=""RD"")+(" & s1 & "=""SPC""))=0," & s & ")"
> >
> > I noticed that you had written "SPC" instead of "SFC" in your code. Not a
> > big deal, but when I copied your code in, I forgot to change it and got
> > the
> > same result as earlier. After I changed it to "SFC", again, same result.
> > So, I am assuming that this line needs to be changed, but I don't quite
> > understand what to change it too. Thanks again.
> >
> > Phil
> >
> > "Tom Ogilvy" wrote:
> >
> >> Sub CalcDeviations()
> >> Dim m As Long
> >> Dim x As Double
> >> Dim y As Double
> >> Dim myRange As Range
> >> Dim v As Variant, s as String
> >> Dim s1 as String, s2 as String
> >> Set myRange = Range("L5", Range("L5").End(xlDown))
> >> s = myRange.Address(1, 1, xlA1)
> >> s1 = Replace(s, "L", "A")
> >> s2 = "If(((" & s1 & "=""RD"")+(" & s1 & "=""SPC""))=0," & s & ")"
> >> v = Evaluate(s2)
> >> x = Application.AveDev(v)
> >> y = Application.StDev(v)
> >>
> >> For m = Range("A65536").End(xlUp).Row To 1 Step -1
> >> If Range("A" & m).Value = "Average Deviation" Then
> >> Range("L" & m).FormulaR1C1 = x & "%"
> >> Range("L" & m).NumberFormat = "##0.00%_)"
> >> EseIf Range("A" & m).Value = "Standard Deviation" Then
> >> Range("L" & m).FormulaR1C1 = y & "%"
> >> Range("L" & m).NumberFormat = "##0.00%_)"
> >> End If
> >> Range("L" & m).Select
> >> With Selection.Font
> >> .Bold = True
> >> .Name = "Times New Roman"
> >> .Size = 8
> >> End With
> >> Next
> >>
> >> End Sub
> >>
> >> worked for me.
> >>
> >> --
> >> Regards,
> >> Tom Ogilvy
> >>
> >> "Phil Trumpy" wrote:
> >>
> >> > I have an Access app that uses a crystal report and exports to excel.
> >> > I was
> >> > using this function to get the standard and average deviations for a
> >> > specific
> >> > range once the info was in Excel:
> >> >
> >> > Sub CalcDeviations(objXLApp)
> >> > Dim m As Long
> >> > Dim x As Double
> >> > Dim y As Double
> >> > Dim myRange As Range
> >> > Set myRange = Range("L5", Range("L5").End(xlDown))
> >> > x = objXLApp.WorksheetFunction.AveDev(myRange)
> >> > y = objXLApp.WorksheetFunction.StDev(myRange)
> >> >
> >> > For m = Range("A65536").End(xlUp).Row To 1 Step -1
> >> > If Range("A" & m).Value = "Average Deviation" Then
> >> > Range("L" & m).FormulaR1C1 = x & "%"
> >> > Range("L" & m).NumberFormat = "##0.00%_)"
> >> > EseIf Range("A" & m).Value = "Standard Deviation" Then
> >> > Range("L" & m).FormulaR1C1 = y & "%"
> >> > Range("L" & m).NumberFormat = "##0.00%_)"
> >> > End If
> >> > Range("L" & m).Select
> >> > With Selection.Font
> >> > .Bold = True
> >> > .Name = "Times New Roman"
> >> > .Size = 8
> >> > End With
> >> > Next
> >> >
> >> > End Sub
> >> >
> >> > That worked perfect for me. The problem is that the user changed the
> >> > information requested. There are now subtotals in the columns along
> >> > with the
> >> > data to calculate the deviations on. my question is how do I exclude
> >> > those
> >> > subtotals? My condition would be:
> >> >
> >> > If column A contains "RD" or "SFC", do not count those rows when
> >> > calculating the deviations. This is my first post, so I apologize if I
> >> > left
> >> > anything out. Thanks in advance for any help.
> >> >
>
>
>
|
Similar Threads
