Vba

[piresmdb]

Hi

Iam trying to do a simple VBA exercise but it is not working
in the second column (D2) it should be a number different from zero it is not considering the formulas

can someone help?

<TABLE style="WIDTH: 141pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=188><COLGROUP><COL style="WIDTH: 82pt; mso-width-source: userset; mso-width-alt: 3986" width=109><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2889" width=79><TBODY><TR style="HEIGHT: 21.75pt" height=29><TD style="BORDER-BOTTOM: red 1.5pt solid; BORDER-LEFT: red 1.5pt solid; BACKGROUND-COLOR: lime; WIDTH: 82pt; HEIGHT: 21.75pt; BORDER-TOP: red 1.5pt solid; BORDER-RIGHT: red 1.5pt solid" class=xl63 height=29 width=109>Count:</TD><TD style="BORDER-BOTTOM: red 1.5pt solid; BORDER-LEFT: red; BACKGROUND-COLOR: lime; WIDTH: 59pt; BORDER-TOP: red 1.5pt solid; BORDER-RIGHT: red 1.5pt solid" class=xl63 width=79 align=right>0</TD></TR><TR style="HEIGHT: 21.75pt" height=29><TD style="BORDER-BOTTOM: red 1.5pt solid; BORDER-LEFT: red 1.5pt solid; BACKGROUND-COLOR: lime; HEIGHT: 21.75pt; BORDER-TOP: red; BORDER-RIGHT: red 1.5pt solid" class=xl63 height=29>Min:</TD><TD style="BORDER-BOTTOM: red 1.5pt solid; BORDER-LEFT: red; BACKGROUND-COLOR: lime; BORDER-TOP: red; BORDER-RIGHT: red 1.5pt solid" class=xl63 align=right>0</TD></TR><TR style="HEIGHT: 21.75pt" height=29><TD style="BORDER-BOTTOM: red 1.5pt solid; BORDER-LEFT: red 1.5pt solid; BACKGROUND-COLOR: lime; HEIGHT: 21.75pt; BORDER-TOP: red; BORDER-RIGHT: red 1.5pt solid" class=xl63 height=29>Max:</TD><TD style="BORDER-BOTTOM: red 1.5pt solid; BORDER-LEFT: red; BACKGROUND-COLOR: lime; BORDER-TOP: red; BORDER-RIGHT: red 1.5pt solid" class=xl63 align=right>0</TD></TR><TR style="HEIGHT: 21.75pt" height=29><TD style="BORDER-BOTTOM: red 1.5pt solid; BORDER-LEFT: red 1.5pt solid; BACKGROUND-COLOR: lime; HEIGHT: 21.75pt; BORDER-TOP: red; BORDER-RIGHT: red 1.5pt solid" class=xl63 height=29>Sum:</TD><TD style="BORDER-BOTTOM: red 1.5pt solid; BORDER-LEFT: red; BACKGROUND-COLOR: lime; BORDER-TOP: red; BORDER-RIGHT: red 1.5pt solid" class=xl63 align=right>0</TD></TR><TR style="HEIGHT: 21.75pt" height=29><TD style="BORDER-BOTTOM: red 1.5pt solid; BORDER-LEFT: red 1.5pt solid; BACKGROUND-COLOR: lime; HEIGHT: 21.75pt; BORDER-TOP: red; BORDER-RIGHT: red 1.5pt solid" class=xl63 height=29>Average:</TD><TD style="BORDER-BOTTOM: red 1.5pt solid; BORDER-LEFT: red; BACKGROUND-COLOR: lime; BORDER-TOP: red; BORDER-RIGHT: red 1.5pt solid" class=xl63 align=middle>#DIV/0!</TD></TR><TR style="HEIGHT: 21.75pt" height=29><TD style="BORDER-BOTTOM: red 1.5pt solid; BORDER-LEFT: red 1.5pt solid; BACKGROUND-COLOR: lime; HEIGHT: 21.75pt; BORDER-TOP: red; BORDER-RIGHT: red 1.5pt solid" class=xl63 height=29>Stan Dev:</TD><TD style="BORDER-BOTTOM: red 1.5pt solid; BORDER-LEFT: red; BACKGROUND-COLOR: lime; BORDER-TOP: red; BORDER-RIGHT: red 1.5pt solid" class=xl63 align=middle>#DIV/0!</TD></TR></TBODY></TABLE>

Private Sub CmdCalculations_Click()

With ActiveSheet

.Range("D2").Formula = "=COUNT(" & ActiveWindow.Selection.Address & ")"
.Range("D3").Formula = "=MIN(" & ActiveWindow.Selection.Address & ")"
.Range("D4").Formula = "=MAX(" & ActiveWindow.Selection.Address & ")"
.Range("D5").Formula = "=SUM(" & ActiveWindow.Selection.Address & ")"
.Range("D6").Formula = "=AVERAGE(" & ActiveWindow.Selection.Address & ")"
.Range("D7").Formula = "=STDEV(" & ActiveWindow.Selection.Address & ")"

.Range("C2").Value = "Count:"
.Range("C3").Value = "Min:"
.Range("C4").Value = "Max:"
.Range("C5").Value = "Sum:"
.Range("C6").Value = "Average:"
.Range("C7").Value = "Stan Dev:"
.Range("C2:D7").Select
End With

With Selection
.Font.Size = 16
.Font.Bold = True
.Font.Color = vbBlue
.Font.Name = "Arial"
.Columns.AutoFit
.Interior.Color = vbGreen
.Borders.Weight = xlThick
.Borders.Color = vbRed
End With
Range("A1").Select
End Sub

 

[AlphaFrog]

Your code uses the address of the selected cells within the formulas.

• What cells do you have selected before you run the code?
• Do you see that address in the formulas?
• Do those selected cells have values?

Forum Tip: Pasting VBA code in the forum editor
It would be best if you surround your VBA code with code tags e.g [CODE]your VBA code here[/CODE]
It makes reading your VBA code much easier.
When you're in the forum editor, highlight your pasted VBA code and then click on the icon with the pound or number sign #.