Hi

I have made a program in vb 6 thats open excel and create a worksheet
etc
When a press my Command1 button . it works perfect, But when i close
excel and press my button again i gott this error

"Object variable or With block variable not set"

so can someone help me ?
This is this code it dosen't like

ActiveCell.FormulaR1C1 = "=COUNTIF(RC[-2]:R[4]C[-2],RC[-1])"
.Range("C2").Select
Selection.AutoFill Destination:=.Range("C2:C5"),
Type:=xlFillDefault

CODE

Private Sub Command1_Click()
Dim objXL As Excel.Application
Dim objWB As Excel.Workbook
Dim objWS As Excel.Worksheet

Set objXL = New Excel.Application
Set objWB = objXL.Workbooks.Add
Set objWS = objWB.Worksheets.Application.ActiveSheet

With objWS
.Cells(1, 1) = "HEJ"
.Cells(2, 1) = "1"
.Cells(3, 1) = "1"
.Cells(4, 1) = "4"
.Cells(5, 1) = "7"
.Cells(6, 1) = "3"

.Range("A1:A6").Select
.Range("A1:A6").AdvancedFilter Action:=xlFilterCopy,
CopyToRange:=.Range("B1"), Unique:=True
.Range("C2").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(RC[-2]:R[4]C[-2],RC[-1])"
.Range("C2").Select
Selection.AutoFill Destination:=.Range("C2:C5"),
Type:=xlFillDefault
End With
objXL.Visible = True

Set objXL = Nothing
Set objWB = Nothing
Set objWS = Nothing

End Sub

//Stelu

Hi Stelu

ActiveCell and Selection are not VB6 objects, so they need to be prefixed
with objWS. or in this case just a .

--
HTH. Best wishes Harald
Excel MVP
Followup to newsgroup only please

"Stelu" skrev i melding
om...
Hi

I have made a program in vb 6 thats open excel and create a worksheet
etc
When a press my Command1 button . it works perfect, But when i close
excel and press my button again i gott this error

"Object variable or With block variable not set"

so can someone help me ?
This is this code it dosen't like

ActiveCell.FormulaR1C1 = "=COUNTIF(RC[-2]:R[4]C[-2],RC[-1])"
.Range("C2").Select
Selection.AutoFill Destination:=.Range("C2:C5"),
Type:=xlFillDefault

CODE

Private Sub Command1_Click()
Dim objXL As Excel.Application
Dim objWB As Excel.Workbook
Dim objWS As Excel.Worksheet

Set objXL = New Excel.Application
Set objWB = objXL.Workbooks.Add
Set objWS = objWB.Worksheets.Application.ActiveSheet

With objWS
.Cells(1, 1) = "HEJ"
.Cells(2, 1) = "1"
.Cells(3, 1) = "1"
.Cells(4, 1) = "4"
.Cells(5, 1) = "7"
.Cells(6, 1) = "3"

.Range("A1:A6").Select
.Range("A1:A6").AdvancedFilter Action:=xlFilterCopy,
CopyToRange:=.Range("B1"), Unique:=True
.Range("C2").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(RC[-2]:R[4]C[-2],RC[-1])"
.Range("C2").Select
Selection.AutoFill Destination:=.Range("C2:C5"),
Type:=xlFillDefault
End With
objXL.Visible = True

Set objXL = Nothing
Set objWB = Nothing
Set objWS = Nothing

End Sub

//Stelu

"Orlando Magalhães Filho" skrev i melding
...

ActiveSheet, ActiveCell and
Selection are Excel application properties, not workbook or worksheet
properties.

Oops. Right you are, my mistake, sorry.

Best wishes Harald
Excel MVP
Followup to newsgroup only please

thx Orlando :)

//Stelu

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Help shows activesheet as a property of either application or workbook.

And activecell and selection can belong to a window.

Option Explicit
Sub testme()

Dim wkbk As Workbook
Dim wdw As Window
Dim iCtr As Long

For iCtr = 1 To 2
Workbooks.Add
Worksheets.Add
Range("a1:b99").Select
Worksheets.Add
Range("x5:z7").Select
Next iCtr

For Each wkbk In Application.Workbooks
MsgBox wkbk.ActiveSheet.Name
For Each wdw In wkbk.Windows
MsgBox wdw.ActiveCell.Address
MsgBox wdw.Selection.Address
Next wdw
Next wkbk

End Sub

(No, I've never used them except as properties of Application--except in the
newsgroup).


Harald Staff wrote:

"Orlando Magalhães Filho" skrev i melding
...

ActiveSheet, ActiveCell and
Selection are Excel application properties, not workbook or worksheet
properties.

Oops. Right you are, my mistake, sorry.

Best wishes Harald
Excel MVP
Followup to newsgroup only please

--

Dave Peterson