PDA

View Full Version : Copying values



gscarter
07-17-2008, 09:04 AM
Hello,

Using the code below i am copying values from two worksheets into one.



Private Sub CopyData(path1, path2)

Set wb1 = Workbooks.Open(path1, True, True)
Set wb2 = Workbooks.Open(path2, True, True)

With ThisWorkbook.Worksheets("Sheet1")
.Columns("A:B").Value = wb1.Worksheets(sheetName(TextBox1)).Columns("A:B").Value
.Columns("D:E").Value = wb2.Worksheets(sheetName(TextBox2)).Columns("A:B").Value
End With

End Sub

The problem i am having is when i copy over any values with 'E's in it is copying them as exponentials rather than just E's. For example:


5697E93
will copy as:


5.70E+96
Anyone got any ideas?

Ive tried using .formula and .text instead of .value.
.formula copied the same
.text copied no values

Any fixes need to be coded, as i do not create the spreadsheets, this is the format they are given to me.

I am using Microsoft Office XP (2002) Excel spreadsheets

Hope you can help
Gary

grichey
07-17-2008, 09:28 AM
can you do just change formatcells for all cells to text?

Bob Phillips
07-17-2008, 09:31 AM
Is this approach acceptable?



Range("B1").Value = IIf(TypeName(Range("A1").Value) = "String", "'", "") & Range("A1").Value

gscarter
07-18-2008, 01:32 AM
can you do just change formatcells for all cells to text?

I've tried this, when i select general it leaves it in the second format, when i select number it turns it into a huge number because it is picking up the E as exponential.

gscarter
07-18-2008, 01:35 AM
Is this approach acceptable?



Range("B1").Value = IIf(TypeName(Range("A1").Value) = "String", "'", "") & Range("A1").Value


Hi thanks for the quick reply.

Sorry, i dont fully understand what that code does, does it only copy values that are stored as string?

And doing it like that would i have to copy each individual record one by one?

Thanks for your help

Bob Phillips
07-18-2008, 04:39 AM
Not, it precedes a string value with an apostophe, stopping Excel converting to an number.