VBA Code .value2 = .value2 copying currency as number stored as text

[excelnat]

Hi guys,

I have a line of VBA code where I copy a row of values (text, dates, numbers and currency) to another workbook:

ActiveSheet.Range("A65536").End(xlUp).Resize(, 16).Offset(1, 0).Value2 = ThisWorkbook.Sheets("COPY").Range("C55:R55").Value2

Where column L and N are currency values.

However, when it copies across, it goes as 'number stored as text' on the currency figures?! Although, it is still formatted as currency and the whole column is formatted as currency.

Can anyone help with how to stop it from storing the currency as text please? (as it interferes with a later code to sum the currency columns!!)

Thanks,
ExcelNat

 

[RoryA]

Are you sure columns L and N are actually stored as numbers in the source worksheet, and not text?

 

[excelnat]

Are you sure columns L and N are actually stored as numbers in the source worksheet, and not text?

Hi Rory,
On the source workbook, the values are not numbers stored as text, but they are formulas formatted to show currency, if that makes any difference? As I'm trying to get the evaluation of that formula copied to the destination workbook without the formula.
ExcelNat

 

[RoryA]

What are the formulas (do they use the TEXT function)? If the destination worksheet columns aren't formatted as text, the formulas must be returning text.

 

[excelnat]

What are the formulas (do they use the TEXT function)? If the destination worksheet columns aren't formatted as text, the formulas must be returning text.

Ah-ha! Thank you for your help! It was the fact that the formula was linked to an ActiveX Textbox value, which, after some research, seems to convert values to text (even though I had a LostFocus event on the textbox to convert the figure to currency), so I've changed the formula in the source workbook to point to the Value() of that textbox and it now copies across correctly.