Activecell value inserted as number

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Activecell value inserted as number

  1. #1
    New Member
    Join Date
    Sep 2003
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Activecell value inserted as number

     
    In a macro, I have a form (Bedding_Tag_Information) that has been filled out with sizes (bti_length, bti_width, and bti_thickness. These are the variables assigned to the numbers keyed into the form. When I click the "Done" button, I want to paste the values into sheet (body) and starting at range (body_poly). I then have a vlookup on the bti_thickness for a calculation. The cells on the sheet (body) are formatted as numbers, but when I use Activecell.Value = bti_thickness, the macro puts the correct number in the cell but changes the format. Then the vlookup returns the #N/A. Any help? Thanks.

  2. #2
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    73,092
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Activecell value inserted as number

    Try:

    Activecell.Value = Val(bti_thickness)

    It's probably a string from a TextBox.

  3. #3
    Board Regular Aaron Blood's Avatar
    Join Date
    Oct 2002
    Location
    West Palm Beach, Florida
    Posts
    485
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Activecell value inserted as number

    Andrew's solution will work...

    However, you should just explicitly state that you'd like to pass a value from the textbox.

    Instead of:
    Activecell.Value = bti_thickness

    Use:
    Activecell.Value = bti_thickness.Value

  4. #4
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    73,092
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Activecell value inserted as number

    Quote Originally Posted by Aaron Blood
    Andrew's solution will work...

    However, you should just explicitly state that you'd like to pass a value from the textbox.

    Instead of:
    Activecell.Value = bti_thickness

    Use:
    Activecell.Value = bti_thickness.Value
    Aaron,

    The OP said "These are the variables assigned to the numbers keyed into the form."

    So I assumed they weren't the TextBoxes.

  5. #5
    Board Regular Aaron Blood's Avatar
    Join Date
    Oct 2002
    Location
    West Palm Beach, Florida
    Posts
    485
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Activecell value inserted as number

    Perhaps that's a fair assumption.

    I think they're referring to textboxes even though they say, "variables".

    I guess it is possible someone could create a textbox on a userform and then assign it to a variable:
    bti_length = Textbox1

    But, if they want to pass it as a value they should still use:
    bti_length = Textbox1.Value


    Whichever way they meant, I think we got em covered.

  6. #6
    New Member
    Join Date
    Sep 2003
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Thanks for the help. I an new at VBA and the teminology. It was a text box in the form. I did use the Activecell.Value = bti_thickness.Value and it worked great!

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
  •  

 

 
DMCA.com