Results 1 to 9 of 9

VBA set variable from cell value

This is a discussion on VBA set variable from cell value within the Excel Questions forums, part of the Question Forums category; It seems like this ought to be simple, but I can't figure out the correct syntax. I have a worksheet ...

  1. #1
    New Member
    Join Date
    Jan 2003
    Location
    St. Mary's County, Maryland
    Posts
    16

    Default

    It seems like this ought to be simple, but I can't figure out the correct syntax.

    I have a worksheet (Sheet1) with a value of 1234 in cell F12. It is formatted as a number with 0 decimal places. I am trying to copy that value of 1234 into a variable. Here is my routine:

    Sub SetVarFromCell()
    Dim FTW As Long
    Worksheets("Sheet1").Activate
    FTW = Cells(12, "F").Value
    End Sub


    This routine runs without error, but FTW has a value of "0", rather than "1234" which is what it is supposed to have. I have also tried the statement:
    FTW = Range("F12").Value and the result is the same -- "0" instead of "1234".

    Is there a way to assign the value of the cell (1234) to the variable FTW?

    Thanks,
    Hector


  2. #2
    MrExcel MVP phantom1975's Avatar
    Join Date
    Jun 2002
    Location
    Omaha, Nebraska
    Posts
    3,962

    Default

    Your syntax is OK. I've tried all of your methods and can't reproduce the error you are having. Try running through your macro using the F8 key and see where the disconnection occurs.
    Silly Billy was here....

    ***************** EXCEL/VB NEWBIES ARE MY FAVORITE! *****************

  3. #3
    MrExcel MVP
    Join Date
    Apr 2002
    Location
    Vancouver BC , Canada
    Posts
    6,259

    Default

    Sub SetVarFromCell()
    Dim FTW As Long

    FTW = Worksheets("Sheet1").Cells(12, "F").Value
    MsgBox (FTW)
    End Sub

    <MARQUEE>...........Never be afraid to try something new. Remember, amateurs built the ark, professionals built the Titanic...............The easiest thing to find is fault, don't be easy !.. --Anonymous--...</marquee>

  4. #4
    MrExcel MVP
    Join Date
    Apr 2002
    Location
    Vancouver BC , Canada
    Posts
    6,259

    Default

    Your code work for me as well. I'm thinking you may not be accessing where you think you are. For example maybe you have several workbooks open.

    Any way try this:
    Select cell "F12" and run the following code : it might tell you something

    Sub SetVarFromCell()
    Dim FTW As Long

    MsgBox (ThisWorkbook.Name & " " & ActiveSheet.Name & " " & actviecell.Address)
    MsgBox (ActiveCell.Value)
    FTW = Worksheets("Sheet1").Cells(12, "F").Value
    MsgBox (FTW)
    End Sub

    <MARQUEE>...........Never be afraid to try something new. Remember, amateurs built the ark, professionals built the Titanic...............The easiest thing to find is fault, don't be easy !.. --Anonymous--...</marquee>

  5. #5
    New Member
    Join Date
    Jan 2003
    Location
    St. Mary's County, Maryland
    Posts
    16

    Default

    Thanks for your help! Nimrod, your second post identified my problem -- I had not been looking at the right worksheet. When I was testing I had multiple workbooks open and apparently at the time I was trying to run the simple macro, the active sheet was not the one with the 1234 in F12. As you who responded said, my code was working fine, but it was not pointing to the expected worksheet. Those are always sticky problems when the macro code is correct but some other factor is causing unexpected results. I will be more alert to that possibility in the future.
    Thanks again, Hector

  6. #6
    New Member
    Join Date
    Jan 2003
    Location
    St. Mary's County, Maryland
    Posts
    16

    Default

    Thanks for your help! Nimrod, your second post identified my problem -- I had not been looking at the right worksheet. When I was testing I had multiple workbooks open and apparently at the time I was trying to run the simple macro, the active sheet was not the one with the 1234 in F12. As you who responded said, my code was working fine, but it was not pointing to the expected worksheet. Those are always sticky problems when the macro code is correct but some other factor is causing unexpected results. I will be more alert to that possibility in the future.
    Thanks again, Hector

  7. #7
    New Member
    Join Date
    Aug 2011
    Posts
    8

    Default Re: VBA set variable from cell value

    I just came across this thread. Is it possible to do this same process but instead of returning a numeric answer to return the contents of a cell with letters (AA, fort example)?

  8. #8
    New Member
    Join Date
    Mar 2011
    Posts
    37

    Default Re: VBA set variable from cell value

    lolol, I want to do the exact opposite of what is here. That is to store the value returned to a var from an inputbox(yadda, yadda, yadda) to the variable ASH. Then I want to dump the contents of ASH to a cell that will be part of a criteria range for a Filter..

    Is there a function of some sort that will do this or do I have to play a bit?

  9. #9
    New Member
    Join Date
    Mar 2011
    Posts
    37

    Default Re: VBA set variable from cell value

    Quote Originally Posted by pullmyefinger View Post
    lolol, I want to do the exact opposite of what is here. That is to store the value returned to a var from an inputbox(yadda, yadda, yadda) to the variable ASH. Then I want to dump the contents of ASH to a cell that will be part of a criteria range for a Filter..

    Is there a function of some sort that will do this or do I have to play a bit?
    OK, got to play for a minute and it works. Thanks for the original idea!

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
  •  


DMCA.com