Excel programming - scanning values, populating cells - please hel
 

Hello

Please help.

I'm trying to program Excel to run down a column of values, taking values
that are over average(values) + stdev(values) and populating another
worksheet with the adjacent reference for these values.

for example

If I have company registration numbers in one column and their credit risk
rating in another column. I want excel to populate another worksheet with the
company names that have a credit risk rating which is above average(values) +
stdev(values).

I can do this manually, but I want add a button so that my colleagues can
perform the same action with exactly the same result.

Any hints would be much appreciated and I would be will to make a donation

Many thanks in advance, Nick

Excel programming - scanning values, populating cells - please hel
 

Nick,

The best solution would be to insert a column of formulas

=B2(AVERAGE(B:B)+STDEV(B:B))

and then use Data Filter Autofilter and select TRUE for that column.

HTH,
Bernie
MS Excel MVP


"Nick" wrote in message
...
Hello

Please help.

I'm trying to program Excel to run down a column of values, taking values
that are over average(values) + stdev(values) and populating another
worksheet with the adjacent reference for these values.

for example

If I have company registration numbers in one column and their credit risk
rating in another column. I want excel to populate another worksheet with the
company names that have a credit risk rating which is above average(values) +
stdev(values).

I can do this manually, but I want add a button so that my colleagues can
perform the same action with exactly the same result.

Any hints would be much appreciated and I would be will to make a donation

Many thanks in advance, Nick

Excel programming - scanning values, populating cells - please
 

Thanks for this Bernie, however, I'm looking to code a button that will do
this automatically, then return the values into a separte worksheet without
having to touch the source data. I have over 6000 entries so I'm worried that
autofilter may crop some values or lead to other data handling problems.
Essentially I have to present the workbook in such a way that a total novice
can filter the values. Any ideas at all?

Much appreciated, Nick

"Bernie Deitrick" wrote:

Nick,

The best solution would be to insert a column of formulas

=B2(AVERAGE(B:B)+STDEV(B:B))

and then use Data Filter Autofilter and select TRUE for that column.

HTH,
Bernie
MS Excel MVP


"Nick" wrote in message
...
Hello

Please help.

I'm trying to program Excel to run down a column of values, taking values
that are over average(values) + stdev(values) and populating another
worksheet with the adjacent reference for these values.

for example

If I have company registration numbers in one column and their credit risk
rating in another column. I want excel to populate another worksheet with the
company names that have a credit risk rating which is above average(values) +
stdev(values).

I can do this manually, but I want add a button so that my colleagues can
perform the same action with exactly the same result.

Any hints would be much appreciated and I would be will to make a donation

Many thanks in advance, Nick

Excel programming - scanning values, populating cells - please hel
 

Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long
Dim j As Long
Dim iLastRow As Long
Dim nAve As Double
Dim nStdev As Double

nAve = Application.Average(Worksheets("Data").Columns(2))
nStdev = Application.StDev(Worksheets("Data").Columns(2))
With Worksheets("Anomalies")
.Range("A1").Value = "Company"
.Range("B1").Value = "Rating"
.Range("C1").Value = "Above Ave?" & vbLf & "(" & Format(nAve,
"#,##0.00") & ")"
.Range("D1").Value = "Above STDev?" & vbLf & "(" & Format(nStdev,
"#,##0.00") & ")"
End With

j = 1
With Worksheets("Data")

iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = 2 To iLastRow 'iLastRow to 1 Step -1
If .Cells(i, "B").Value nAve Or .Cells(i, "B").Value nStdev
Then
j = j + 1
Worksheets("Anomalies").Cells(j, "A").Value = .Cells(i,
TEST_COLUMN).Value
Worksheets("Anomalies").Cells(j, "B").Value = .Cells(i,
"B").Value
End If
If .Cells(i, "B").Value nAve Then
Worksheets("Anomalies").Cells(j, "C").Value = "Y"
End If
If .Cells(i, "B").Value nStdev Then
Worksheets("Anomalies").Cells(j, "D").Value = "Y"
End If

Next i

End With

End Sub

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Nick" wrote in message
...
Hello

Please help.

I'm trying to program Excel to run down a column of values, taking values
that are over average(values) + stdev(values) and populating another
worksheet with the adjacent reference for these values.

for example

If I have company registration numbers in one column and their credit risk
rating in another column. I want excel to populate another worksheet with
the
company names that have a credit risk rating which is above
average(values) +
stdev(values).

I can do this manually, but I want add a button so that my colleagues can
perform the same action with exactly the same result.

Any hints would be much appreciated and I would be will to make a donation

Many thanks in advance, Nick

Excel programming - scanning values, populating cells - please
 

Nick,

You could use the same formula, and then use a Pivot Table based on the data set but on another
sheet, and use the column with the formula as the first row field, set to TRUE - otherwise, try Bob
Philip's macro.

HTH,
Bernie
MS Excel MVP


"Nick" wrote in message
...
Thanks for this Bernie, however, I'm looking to code a button that will do
this automatically, then return the values into a separte worksheet without
having to touch the source data. I have over 6000 entries so I'm worried that
autofilter may crop some values or lead to other data handling problems.
Essentially I have to present the workbook in such a way that a total novice
can filter the values. Any ideas at all?

Much appreciated, Nick

"Bernie Deitrick" wrote:

Nick,

The best solution would be to insert a column of formulas

=B2(AVERAGE(B:B)+STDEV(B:B))

and then use Data Filter Autofilter and select TRUE for that column.

HTH,
Bernie
MS Excel MVP


"Nick" wrote in message
...
Hello

Please help.

I'm trying to program Excel to run down a column of values, taking values
that are over average(values) + stdev(values) and populating another
worksheet with the adjacent reference for these values.

for example

If I have company registration numbers in one column and their credit risk
rating in another column. I want excel to populate another worksheet with the
company names that have a credit risk rating which is above average(values) +
stdev(values).

I can do this manually, but I want add a button so that my colleagues can
perform the same action with exactly the same result.

Any hints would be much appreciated and I would be will to make a donation

Many thanks in advance, Nick