Complie error

[Decreenisi]

Hi,

I am having a problem with some code..... It's from a teaching book as
I try to learn VBA. I have written it exactly as in the book but I get
a complie error, any Ideas ?

..Range("D2).Formula = "=COUNT(" & ActiveWindow.Selection.Address & ")"

The error msg is :-

Compile Error
Expected list separator or )

The whole code is :-

Private Sub cmdCalculate_Click()
'__________________________
'Add Formulas for summary stats
'__________________________

With ActiveSheet
'These formulas are entered int the new worksheet.
..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 & ")"

'____________________________
'Add labels and stats
'___________________________

..Range("C2").Value = "Count:"
..Range("C3").Value = "Min:"
..Range("C4").Value = "Max:"
..Range("C5").Value = "Sum:"
..Range("C6").Value = "Average:"
..Range("C7").Value = "Stand Dev:"
..Range("C27").Select

End With

'____________________________

'Format the labels and stats.

'___________________________

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

End Sub

 

[Bob Phillips]

You are missing a closing quote in the range address

..Range("D2").Formula = "=COUNT(" & ActiveWindow.Selection.Address & ")"

^
..................||.......................

(hopefully that works)


--
---
HTH

Bob


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

 

[Niek Otten]

Range("D2").

You missed a quote

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Hi,
|
| I am having a problem with some code..... It's from a teaching book as
| I try to learn VBA. I have written it exactly as in the book but I get
| a complie error, any Ideas ?
|
| .Range("D2).Formula = "=COUNT(" & ActiveWindow.Selection.Address & ")"
|
| The error msg is :-
|
| Compile Error
| Expected list separator or )
|
| The whole code is :-
|
| Private Sub cmdCalculate_Click()
| '__________________________
| 'Add Formulas for summary stats
| '__________________________
|
| With ActiveSheet
| 'These formulas are entered int the new worksheet.
| .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 & ")"
|
| '____________________________
| 'Add labels and stats
| '___________________________
|
| .Range("C2").Value = "Count:"
| .Range("C3").Value = "Min:"
| .Range("C4").Value = "Max:"
| .Range("C5").Value = "Sum:"
| .Range("C6").Value = "Average:"
| .Range("C7").Value = "Stand Dev:"
| .Range("C27").Select
|
| End With
|
| '____________________________
|
| 'Format the labels and stats.
|
| '___________________________
|
| With Selection
| .Font.Size = 16
| .Font.Bold = True
| .Font.Color = vbBlue
| .Font.Name "Arial"
| .Columns.AutoFit
| .Interior.Color = vbGreen
| .Borders.Weight = x1Thick
| .Borders.Color = vbRed
| End With
| Range("A1").Select
|
| End Sub