Excel Word VBA Problem

Matt

Board Regular
Joined
Feb 16, 2002
Messages
212
I'm calling an excel macro from a word macro, everything was going swimmingly until I adapted the excel macro to encompass

Set myrange = Sheets("Master").Range("K2:K" & lastrow)

I have declared the variable, the excel object library has been referenced I have created the excel object

Dim Xl As Object
Set Xl = CreateObject("Excel.Application")

and I'm using a

With Xl.... ...end with piece of code

Like I say everything was working OK until I introduced the 'Set myrange...' element.

I now get an error 'Type Mismatch'

Any ideas, hope someone can help

thanks in advance

Matt
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Matt

Board Regular
Joined
Feb 16, 2002
Messages
212
it's declared as an integer data type within the 'With XL' code, the variable is set just above the myrange variable as below.


Sheets("Master").Select

lastrow = Range("A65536").End(xlUp).Row

Have stepped through the code and it is picking up the correct value for lastrow.
This message was edited by Matt on 2002-10-24 12:37
 

KniteMare

Board Regular
Joined
Mar 4, 2002
Messages
238
Just using the snippit that is the problem child I get the expected result of K2:K5 being selected.

When your Word macro runs; does it Select/Activate the "Master" Sheet so that the reference is to the Excel Application and not the Word Application it was called from?

I'm on my way home. I've been teaching all day with out my partner so to wind down I am going to get in an hour run or so and I'll look back at this and try to duplicate the calls. It might be fun to try to solve and may end up addressing some areas I have been thinking of exploring anyway.

Yours in EXCELent Frustration

KniteMare

I set this as a test bed to start from.

'Code Follows:

'Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
lastrow = 5

Set myrange = Sheets("Master").Range("K2:K" & lastrow)
myrange.Select
'this selects K2 throught K5 when you click anywhere on the sheet

End Sub

'Code Ends:
 

Forum statistics

Threads
1,144,329
Messages
5,723,737
Members
422,512
Latest member
MHau5

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top