2 problems - 1st is most urgent!!

[nrage21]

Problem 1

Run time error '-2147417848(800....
I get the following error when macro is invoked tru commandbutton...

Run Time Error '-2147417848(80010108)':
Automation Error
The object invoked has disconnected from its clients.

then my computer appears to freeze up i can navigate tru the sheets bu
i cannot select or enter info in cells.

This is my macro...

'10:00
Private Sub CmBLI1_1000_1_Click()
If Application.WorksheetFunction.CountA(Range("E7:G7")) < _
Range("E7:G7").Cells.Count Then
MsgBox "Complete ALL Fields"
Else
Range("C7:J7").Copy
Set rng = ActiveSheet.Range("C7:J7")
Workbooks.Open _
("C:\My Documents\Test Folder\Workbook2.xls")
Set rng1 = ActiveSheet.Range("A65536").End(xlUp).Offset(1, 0)
rng.Copy Destination:=rng1
End If
End Sub


when I press debug the following line gets hightlighted...
rng.Copy Destination:=rng1

range C7:J7 are plain cells with the exception of D7 in which I have
combobox from the control bar.

Any ideas why I am getting this error.????



Problem 2

Where do I enter...?? what cell?? How?? Do I do a fill down??
Range1 =
INDIRECT(+ADDRESS(4,3,,,"Sheet1")&":"&ADDRESS(+COUNTA(Sheet1!$c:$c),4))

Could someone please give me some instructions??

Is it..??
=IF(COUNTIF(INDIRECT(+ADDRESS(4,3,,,"Sheet1")&":"&ADDRESS(+COUNTA(Sheet1!$C:$D),4))>1,"Duplicate","")

Can this function be written as a macro, so I can add...

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
'.... Call Macro
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic


- Larry - (puzzled face)
VBA Amateu

 

[Guest]

I am not an expert though....but i also landed in similar problem.....Try out the below solution. If it works fine for you

Copy the whole code. Delete the particular module etc. Re insert the module and paste the same code. (does it look like a stupid suggestion...i know!!!1.....but no harm in trying it out...it worked fine for me. )

 

[nrage21]

Interesting proposition...

After doing what you suggested the first range works!
but the other code still returns the same error... I'm looking at th
code and it's exactly the same... what in h*** is going on??

'10:00
Private Sub CmBLI1_1000_1_Click()
If Application.WorksheetFunction.CountA(Range("E7:G7")) < _
Range("E7:G7").Cells.Count Then
MsgBox "Complete ALL Fields"
Else
Range("C7:J7").Copy
Set rng = ActiveSheet.Range("C7:J7")
Workbooks.Open _
("C:\My Documents\Workbook2.xls")
Set rng1 = ActiveSheet.Range("A65536").End(xlUp).Offset(1, 0)
rng.Copy Destination:=rng1
End If
End Sub

This one works!!
___________________________
'10:30
Private Sub CmBLI1_1030_1_Click()
If Application.WorksheetFunction.CountA(Range("E9:G9")) < _
Range("E9:G9").Cells.Count Then
MsgBox "Complete ALL Fields"
Else
Range("C9:J9").Copy
Set rng = ActiveSheet.Range("C9:J9")
Workbooks.Open _
("C:\My Documents\Workbook2.xls")
Set rng1 = ActiveSheet.Range("A65536").End(xlUp).Offset(1, 0)
rng.Copy Destination:=rng1

End If
End Sub

This one doesn't !! :

 

[nrage21]

Ok, got it working finally...
I did the following...

I copy the code in the module to the notepad

Then I cleared the code from the module

I went back to the sheet which contained the commandbuttons and wen
into "design mode" then I began double clicking the commandbuttons an
inserting the code from the notepad back to the module.

When I was done, I saved in the VB Editor and save the workbook.

I tested it and it worked!! whewww

Now on to tackle my 2nd problem.

- Larry