Power Query Course in Spanish
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: declaring statements

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Seattle Area, WA
    Posts
    217
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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......
    Russell

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi
    Maybe try this:

    Change:

    acct = Workbooks(workbk1).ActiveCell.Value

    To:

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

    Tom


  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Seattle Area, WA
    Posts
    217
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Didn't work, still got the same error.
    Russell

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sydney, Australia
    Posts
    2,940
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    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


  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    Seattle Area, WA
    Posts
    217
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?
    Russell

  6. #6
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sydney, Australia
    Posts
    2,940
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    Can you please post the full code so I can have a look on my machine?

    Cheers,
    Dan

  7. #7
    Board Regular
    Join Date
    Feb 2002
    Location
    Seattle Area, WA
    Posts
    217
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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


    Russell

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •