declaring statements

ermccarthy

Board Regular
Joined
Feb 15, 2002
Messages
224
I have declared the following two statements in a macro I have created, unfortunatly I get an error #438:Object doesn't support this object or method, and it is always the second line that is highlighted...


workbk1 = Range("FileName").Value
acct = Workbooks(workbk1).ActiveCell.Value


here is the macro, or at least the first part of it.

path1 = Range("path1").Value
path2 = Range("path2").Value
path3 = Range("path3").Value
path4 = Range("path4").Value
Sht1 = Range("Sheet1").Value
Sht2 = Range("Sheet2").Value
Sht3 = Range("Sheet3").Value
Sht4 = Range("Sheet4").Value
workbk1 = Range("FileName").Value
acct = Workbooks(workbk1).ActiveCell.Value

Worksheets("matrix").Select
Range("D4").Select
If ActiveCell.Value <> "" Then
Workbooks.Open FileName:=path1
Worksheets(Sht1).Select
Range("F11").Select
Do Until ActiveCell = acct.Value
ActiveCell.Offset(0, 1).Select
Loop
ActiveCell.Offset(100, 0).Select
Do Until ActiveCell <> ""
ActiveCell.Offset(-1, 0).Select
Loop
Selection.Copy
Workbooks(workbk1).Active
ActiveCell.Offset(0, 3).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
ActiveCell.Offset(1, -3).Select
Else: End If


Please help me-----there may be more errors in this, but I just can't get past this so far......
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Hi
Maybe try this:

Change:

acct = Workbooks(workbk1).ActiveCell.Value

To:

acct = Workbooks(workbk1).ActiveSheet.ActiveCell.Value

Tom
 
Upvote 0
Sorry TSTom but that won't work either. Activecell is a property of either the entire Application or a Window.

ermccarthy, if there is an active cell then this line will do the trick:-

acct = ActiveCell.Value

If there is an active cell then there's no need (and you can't) qualify it with a workbook name.

HTH,
Dan
 
Upvote 0
That seemed to work until I got a little further down in the macro, the problem is I need to look on the new workbook I open for the value of the activecell on book1. When I open the second workbook, that changes the value of acct to the activecell of the newly opened active cell.

How do I work around that?
 
Upvote 0
Can you please post the full code so I can have a look on my machine?

Cheers,
Dan
 
Upvote 0
the above is the full code so far, I will repeat the steps four times using the other decleartions (path2, sht2, etc.)
 
Upvote 0

Forum statistics

Threads
1,213,496
Messages
6,113,995
Members
448,539
Latest member
alex78

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
Back
Top