Returned Contents (XL97;SR2)

[jstevens]

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".

[Andrew Cronnolly]

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

[jstevens]

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"

[jscher2000]

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

[rory]

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.

[Andrew Cronnolly]

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

[rory]

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

[jstevens]

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

[jstevens]

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