+ Reply to Thread
Results 1 to 11 of 11

Sheet activate

  1. #1
    Registered User
    Join Date
    02-13-2013
    Location
    Maringá, Paraná, Brazil
    MS-Off Ver
    Excel 2007
    Posts
    10

    Angry Sheet activate

    Good afternoon,


    My macro generates a spreadsheet. How do I activate this sheet determines worksheet to check and make value comparisons. I'm using this:

    Application.Workbooks (Spath & filename & ". Xls"). Worksheets ("AF"). Select

    But generates runtime error 9 - Subscript out of range. I thought about closing the spreadsheet the macro after taking her values​​, but also the macro closes well. As macro to show that the cells in question are given sheet of the spreadsheet created and saved by the macro? Another question is also: Cells (1,3). Value also applies when you are in the cell for text? Thank you!

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Sheet activate

    How is the code generating the worksheet?
    If posting code please use code tags, see here.

  3. #3
    Registered User
    Join Date
    02-13-2013
    Location
    Maringá, Paraná, Brazil
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Sheet activate

    The code is:

    Please Login or Register  to view this content.

  4. #4
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Sheet activate

    Why are you creating a new instance of Excel?
    Please Login or Register  to view this content.
    You don't use oApp anywhere in the code and I don't think you actually need it.

    Also, you are creating worksheets but not any with the name 'AF'?

  5. #5
    Registered User
    Join Date
    02-13-2013
    Location
    Maringá, Paraná, Brazil
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Sheet activate

    the worksheet has 4 sheets of macro calls: INC, ES And A.P., A.F., A.Q.. I make a copy to the new worksheet with:
    ThisWorkbook.Sheets("INC").Copy Before:=oWks.Sheets(1)
    ThisWorkbook.Sheets("E.S. E A.P.").Copy Before:=oWks.Sheets("INC")
    ThisWorkbook.Sheets("A.F.").Copy Before:=oWks.Sheets("E.S. E A.P.")
    ThisWorkbook.Sheets("A.Q.").Copy Before:=oWks.Sheets("A.F.")

    if statements inside the for-loop are for this new spreadsheet and copied to the new sheets. So I have to first enable the worksheet and then each sheet in order to value their cell comparable with the variables cd and cc.

  6. #6
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Sheet activate

    You don't need a separate instance of Excel for that.

    Could this be a simple typo?

    The code in the original post is looking for a worksheet called 'AF' but in the code you posted you have a worksheet called 'A.F.'.

  7. #7
    Registered User
    Join Date
    02-13-2013
    Location
    Maringá, Paraná, Brazil
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Sheet activate

    Owww, that is correct! But, i need to compare the value in the variable cb (string) with text a cell on the sheet, i did so:
    If Cells(j, 2).Value = cb Then
    Cells(j, 5).Value = z + quantidade
    End If

    But not is correct, being the value of the cell text. Can i use .Value for obtain the value inside the cell or not?

  8. #8
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Sheet activate

    In what way doesn't it work?

    Using Value shouldn't be a problem.

    Have you checked what the value in the cell is?

  9. #9
    Registered User
    Join Date
    02-13-2013
    Location
    Maringá, Paraná, Brazil
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Sheet activate

    Now i understood. i have 3 merged cells, B, C, and D collumns, but the text is at the B column. The return value is empty, I tested assigning the value of cell for variable auxiliar and to compile the auxiliar values stay empty.

  10. #10
    Registered User
    Join Date
    02-13-2013
    Location
    Maringá, Paraná, Brazil
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Sheet activate

    I declared variable auxiliar like variant.

  11. #11
    Registered User
    Join Date
    02-13-2013
    Location
    Maringá, Paraná, Brazil
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Sheet activate

    Finally, i can! I wasn´t showing which sheet i wanted to work.
    The code stayed:

    auxiliar = Sheets("E.S. E A.P.").Cells(j, 2)


    Thanks so much Norie for your help! I will finish the code and i will post here soon!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1