+ Reply to Thread
Results 1 to 10 of 10

Variant defined value gets erased after elseif statement

  1. #1
    Registered User
    Join Date
    02-14-2019
    Location
    Argentina
    MS-Off Ver
    2018
    Posts
    5

    Variant defined value gets erased after elseif statement

    Hello, im trying to make a macro that will show a row resume on another worksheet vertically wich is the part that works. But i tried to expand it to be able to change te original row from the resume and thats where it gets sticky. The msgbox displaying iRow shows the right value the first time, but when trying to use the second part of the macro after elseif the variable is set to empty!
    I appreciate any help!
    Please Login or Register  to view this content.
    Last edited by alenieto97; 02-14-2019 at 09:05 AM.

  2. #2
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: Variant defined value gets erased after elseif statement

    Please use code tags when posting code.

    If you need the variable to maintain its value between each trigger of the event, you should declare it as Static, not just Dim.
    Rory

  3. #3
    Registered User
    Join Date
    02-14-2019
    Location
    Argentina
    MS-Off Ver
    2018
    Posts
    5

    Re: Variant defined value gets erased after elseif statement

    I had to change my code because of a small mistake and i keep having the same problem, Now it looks like this

    Please Login or Register  to view this content.
    Again, The variable iRow is empty when i need it. Appreciate help a lot.
    Also i dont know how to label the thread as solved.

  4. #4
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: Variant defined value gets erased after elseif statement

    You're now using two separate routines, so you'll have to declare the variable at module level, like this:

    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    02-14-2019
    Location
    Argentina
    MS-Off Ver
    2018
    Posts
    5

    Re: Variant defined value gets erased after elseif statement

    I still get the same problem, tried with dim and static at module level but it gets erased

    Please Login or Register  to view this content.

  6. #6
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: Variant defined value gets erased after elseif statement

    That's not possible with that code. If you see iRow as 0, it wasn't set in the first place, or you have some other code interfering.

  7. #7
    Registered User
    Join Date
    02-14-2019
    Location
    Argentina
    MS-Off Ver
    2018
    Posts
    5

    Re: Variant defined value gets erased after elseif statement

    I know it was set in the first place because of the first msgbox, i dont see it as 0 but as empty when i place my mouse over the word irow on Activesheet.cells(irow, orow) and when the second msgbox pops. All other code i have is

    Please Login or Register  to view this content.
    and on other module

    Please Login or Register  to view this content.
    Isnt it weird?

  8. #8
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: Variant defined value gets erased after elseif statement

    It can't be Empty because it's declared as a Long, not a Variant. It can only be a number.

    Also, there is no point enabling events in the Workbook_Open. If they aren't enabled, that code won't run anyway.

  9. #9
    Registered User
    Join Date
    02-14-2019
    Location
    Argentina
    MS-Off Ver
    2018
    Posts
    5

    Re: Variant defined value gets erased after elseif statement

    Thanks rory i solved it, only had to add Options Explicit to declare the variable! now how i declare the thread as solved? very grateful for your help!

  10. #10
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: Variant defined value gets erased after elseif statement

    Click the Thread Tools dropdown at the top of the page, then mark Solved.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. If ElseIf statement
    By ColemanJames in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 02-16-2018, 10:43 AM
  2. [SOLVED] A variable defined as variant or variant array
    By billj in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 11-14-2016, 05:35 PM
  3. [SOLVED] Defined SheetValue as Variant, but it still says object required?
    By malleat1 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-03-2015, 10:09 AM
  4. Adding CountIf Statement within If/ElseIf Statement
    By freybe06 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-17-2014, 05:09 AM
  5. Code stepping in to elseif statement when elseif is definitely not true
    By DFrank231 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 02-13-2013, 05:31 PM
  6. Refer to array elements defined as Variant
    By hareleph in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-23-2011, 07:15 AM
  7. If then elseif statement
    By Mac10 in forum Excel General
    Replies: 3
    Last Post: 10-02-2010, 09:29 PM

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