Range().Value = Range().Value causing issues with currency formats

=RSI · Nov 19th 2004

Now this is weird...

I have 2 sheets. The first sheet contains calculations with results in the range AL3:BO3. Each time it calculates, the results change as its formulae contain randon numbers. The second sheet is blank.

I have a small peice of code that recalculates the workbook a few times and puts the result of the calculation in the first sheet in successive rows in the second:

Code

Sub MontyCarlo(Itterations As Integer)
    Dim x As Integer
    For x = 1 To Itterations
        Application.Calculate
        Output2.Range(Cells(x, 1), Cells(x, 30)).Value = MC.Range("AL3:BO3").Value
    Next x
End Sub

Now here's the weird bit: The results in the first sheet are in currency format (GBP). When the code puts them in the second sheet they become text, prefixed as USD currency with a trailing space!

After trying lots of alternatives I found that if I changed the format of the results to 'General' the code worked fine :confused:

Although I've now got it working, can anyone explain why .Value = .Value gave such strange results? Could it be to do with the over engineered build of NT I am on (I cannot change the styles for example)?

Cheers,
Chris.

**Batman** · Nov 19th 2004

Re: Range().Value = Range().Value causing issues with currency formats

Hi Chris,

Your code isn't attempting to copy the format of the originating cells to the destination cells, merely the data content, which should then be displayed according to the format assigned to the destination cells.

Do you know what format type the cells had before they received the data and whether that had changed as a result of the transfer?

Regards,
Batman.

=RSI · Nov 20th 2004

Re: Range().Value = Range().Value causing issues with currency formats

Hi batman,

Yes they were all 'general' format before they received the data. So they shouldn't have received any formatting from that code. I tried replacing the sheet (hence Output2) but still the same...

I've just tried to replicate the problem at home on XL2000 (have 97 still at work!) and do not get the same results. I guess it must be to do with the NT environment.

Thanks for your help!
Chris.

**Batman** · Nov 20th 2004

Re: Range().Value = Range().Value causing issues with currency formats

Hi Chris,

I also have XL97 at work on NT, and I couldn't replicate it there, so I guess it must be something to do with your environment.

Regards,
Batman.

Dave Hawley · Nov 21st 2004

Re: Range().Value = Range().Value causing issues with currency formats

Are you certain that the Worksheet variable Output2 has been set to a sheet where NO formatting has been applied? I cannot see anyway the formatting would ever change via the code you show.

Not sure what format you want, but try one of the codes below to force a format change

Code

With Output2.Range(Cells(x, 1), Cells(x, 30))
        .NumberFormat = "General"
        .Value = MC.Range("AL3:BO3").Value
    End With

Code

With Output2.Range(Cells(x, 1), Cells(x, 30))
        .NumberFormat = MC.Range("AL3:BO3").NumberFormat
        .Value = MC.Range("AL3:BO3").Value
    End With

=RSI · Nov 21st 2004

Re: Range().Value = Range().Value causing issues with currency formats

Thanks, I hadn't thought of forcing the number format. I'll try it on Monday and see what happens.

I'm certain that there was no formatting, I even restarted Excel and replicated just the effect in a new, blank workbook. I'm not conserned about what the format is, so long as it's a number not text.

=RSI · Nov 22nd 2004

Re: Range().Value = Range().Value causing issues with currency formats

Tried forcing the number formats with no joy. It seems to only occur when the source data is in currency or accounting format with just a plain '£' prefix. Any of the '$' prefixes or even the prefix labeled '£ English (United Kingdom)' do not produce this error.

I've attached an example of the result for reference in case any one runs into this again.

Dave Hawley · Nov 22nd 2004

Re: Range().Value = Range().Value causing issues with currency formats

The problem is because these are not true numbers, but rather text. You can tell this by setting their Horizontal alignment to the default of General. Then then stay left aligned when true number would be right aligned.

Run the code below first and the problem no longer occurs;

Code

Sub ForceToNumber()
Dim wSheet As Worksheet
    For Each wSheet In Worksheets
       With wSheet
           .Range("IV65536") = vbNullString
           .Range("IV65536").Copy
           .UsedRange.PasteSpecial xlPasteValues, xlPasteSpecialOperationAdd
       End With
    Next wSheet
End Sub

=RSI · Nov 22nd 2004

Re: Range().Value = Range().Value causing issues with currency formats

Thanks Dave, it works a treat!

Werner · Jul 21st 2005

Re: Range().Value = Range().Value causing issues with currency formats

Thank god! You can't even imagine how by using Dave Hawley's code it took out a burden of my shoulders. I had the exact same problem with Excel 97. Divisions by vba range code was giving me incompatible error with currency values. It worked like magic.

I know it is a thread which is many months old, but I simply had to say a big thanks as I've searched a long time for this solution!

Regards,

Werner

Werner · Jul 22nd 2005

Re: Range().Value = Range().Value causing issues with currency formats

About this macro, I have a question. After running Dave's macro, my search function doesn't work anymore. It seems my findNext method doesn't want to find more than one result (the first row and not the others). It seems it doesn't recognize anymore the other results; as if the number format affected the find method. Is it possible to turn back to vbString or something like that those specific ranges affected? It probably means a ForceToString macro hehe
Thx!

Werner

Range().Value = Range().Value causing issues with currency formats

Participate now!

Share