Excel 2000 and vb6
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 |
Excel 2000 and vb6
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 |
Excel 2000 and vb6
"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 |
Excel 2000 and vb6
thx Orlando :) //Stelu *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
All times are GMT +1. The time now is 11:28 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com