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

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)



"Decreenisi" <(E-Mail Removed)> wrote in message
news:df4d47de-9381-44fc-b6a7-(E-Mail Removed)...
> 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

Range("D2").

You missed a quote

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Decreenisi" <(E-Mail Removed)> wrote in message news:df4d47de-9381-44fc-b6a7-(E-Mail Removed)...
| 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