Results 1 to 9 of 9
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Returned Contents (XL97;SR2)

    I'm having an issue passing the exact cell contents to a variable and sending the variable contents to a particular cell.

    Example: Pass each cell contents to Var1 and then to Cell("B1")
    A1=Dept
    A2=65
    A3='0001

    Sub PassVar()
    Var1=Range("A1") 'substitute A1 for A2 and etc..
    Range("B1") = Var1
    End Sub

    The issue is with A3; B1 ends up being 1 not "'0001".

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Returned Contents (XL97;SR2)

    As far as VBA is concerned , '0001 is the same as 1, the leading ' just being an indicator to excel to display the value as text. You could use something like

    Range("B3") = Format(var1, "'0000")

    but why not use

    Range("A1:A3").Copy Destination:=Range("B1:B3") ?.

    Or if you wist to move the values replace Copy with Cut.

    Andrew C

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Returned Contents (XL97;SR2)

    I have a laundry list of XL file names (similar to what I previously mentioned) that when I pass the file names to the variable the next step is to open it. Unfortunately there is no file named "1"; it is "0001"

  4. #4
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Returned Contents (XL97;SR2)

    What kind of var is Var1? This test:
    <pre>Sub VarTypeTest()
    Dim varVariant As Variant, varString As String, varInteger As Integer
    'value in A2 is '0001
    varVariant = Range("A2")
    Debug.Print "Variant --> " & varVariant
    varString = Range("A2")
    Debug.Print "String --> " & varString
    varInteger = Range("A2")
    Debug.Print "Integer --> " & varInteger
    End Sub</pre>

    Yielded these results:
    <pre>Variant --> 0001
    String --> 0001
    Integer --> 1</pre>

    So typing the variable as a string should solve the problem of being able to manipulate the full value as input in VBA. But when you assign the string to a range, Excel will convert it back to a 1. To get around this, try something like this:
    <pre>Range("b2") = "'" & varString</pre>

    Clearly not the most elegant method

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Returned Contents (XL97;SR2)

    Hi,
    Without seeing the rest of your code, I'm not entirely sure why you're passing the value to a variable and then passing that variable to another cell. Does Range("B3") = Range("A3") not do what you want? It would eliminate the conversion of 0001 to 1 that Excel is performing when you assign the variable to a range (as Jefferson explained).
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Returned Contents (XL97;SR2)

    If you trying to compose a filename from the data in A1:A3, try

    var1 = Range("A1") & Format(Range("A2"), "00") & Format(Range("A3"),"0000")

    Andrew C

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Returned Contents (XL97;SR2)

    John,
    You'll need to try the attachment again - if you preview your post, you have to reattach the file!
    Regards,
    Rory

    Microsoft MVP - Excel

  8. #8
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Returned Contents (XL97;SR2)

    Rory,

    I have attached a sample file named "Book1". You will have to create another XL file with a range named "Dept". Save this new book as "Book2". Run the code from Book1 and watch what happens to the cell contents of Cell(A4-Book1) when it ends up in the range "Dept" in Book2.

    Thanks for your assistance,
    John
    Attached Files Attached Files

  9. #9
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Returned Contents (XL97;SR2)

    I finally tweaked the line of code in the example file:

    Code Org Workbooks("" & MyFile & ".xls").Sheets("sheet1").Range("DeptNo") = dept
    To
    Code Now Workbooks("" & MyFile & ".xls").Sheets("sheet1").Range("DeptNo") = "'"& dept &""

    Thanks again for everyone's help.
    John

Posting Permissions

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