Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pulling a currency variable through my arrays drops 3rd decimal?
My code is hundreds or maybe even 1000+ rows, so I won't waste bandwidth by
posting it all; I'll give a brief description and then post relevant code. I appreciate any suggestions you might have. Programming in XL2003/XP but workbook will also be used in XL2007/XP. I have some currency values in Sheet16, including some that are to the third decimal ($.125, $.150, $.175, etc.). I pull those values into an small array (range?) to process, then paste them into a second (large)array/range, then finally paste the large array/range back to fill an entire worksheet. Somewhere in the translation, I lose the last decimal :( I need to figure out why, and fix it so that later calculations based on these numbers will be accurate. I appreciate any review/advice/corrections- Thanks, Keith 'Create the big array/range so I can later fill it: Dim GraphDataArray As Variant Sheet11.Activate Sheet11.Range("A1:Z65000").Select Selection.Clear 'start with a blank array/range GraphDataArray = Sheet11.Range("A1:Z1000").Value {snip} 'Grab the currency values Dim SubTemp1 As Range Set SubTemp1 = Sheet16.Range(TempUseCol & "10:" & TempUseCol & "14") {snip} 'example of pushing one of these currency values into the large array GraphDataArray(VXD + 24, 16 + 1) = SubTemp1(1) {snip} 'Push the grid back out to the worksheet: Sheet11.Range("A1:Z1000").Value = GraphDataArray |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pulling a currency variable through my arrays drops 3rd decimal?
Some follow-up testing; using debug.print, it appears that the 3rd decimal is
retained even up to placing it in the large array; Debug.Print GraphDataArray(VXD + 25, 17 + EachThresh) just before pasting the values back into Sheet16 shows the expected values. So, that narrows down the location of the problem (pasting the array back into Sheet16), but I'm still at a loss as to why it would drop the extra decimals. For what it's worth, the large array (GraphDataArray) contains a variety of different formats- currency, numbers, numbers with decimals, text, formulas... Thanks, Keith "ker_01" wrote: My code is hundreds or maybe even 1000+ rows, so I won't waste bandwidth by posting it all; I'll give a brief description and then post relevant code. I appreciate any suggestions you might have. Programming in XL2003/XP but workbook will also be used in XL2007/XP. I have some currency values in Sheet16, including some that are to the third decimal ($.125, $.150, $.175, etc.). I pull those values into an small array (range?) to process, then paste them into a second (large)array/range, then finally paste the large array/range back to fill an entire worksheet. Somewhere in the translation, I lose the last decimal :( I need to figure out why, and fix it so that later calculations based on these numbers will be accurate. I appreciate any review/advice/corrections- Thanks, Keith 'Create the big array/range so I can later fill it: Dim GraphDataArray As Variant Sheet11.Activate Sheet11.Range("A1:Z65000").Select Selection.Clear 'start with a blank array/range GraphDataArray = Sheet11.Range("A1:Z1000").Value {snip} 'Grab the currency values Dim SubTemp1 As Range Set SubTemp1 = Sheet16.Range(TempUseCol & "10:" & TempUseCol & "14") {snip} 'example of pushing one of these currency values into the large array GraphDataArray(VXD + 24, 16 + 1) = SubTemp1(1) {snip} 'Push the grid back out to the worksheet: Sheet11.Range("A1:Z1000").Value = GraphDataArray |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pulling a currency variable through my arrays drops 3rd decimal?
Yet even more data:
The source data being pulled into SubTemp1 is most often low percents (2%, 4.5%) and those are showing up correctly (.02, .045). Only a few of my "records" (which is what gets pulled into SubTemp1) use currency instead of a percent, and those are the ones being forcibly rounded to 2 decimal places. Set SubTemp1 =... is used in a loop, so in some cases it will be filled with the percents, and in other cases it will be filled with the currency values. I haven't specified a format for SubTemp1, so I would expect it to just pull as general, thereby pulling well more than 3 decimal places. Still welcoming advice, Keith "ker_01" wrote: My code is hundreds or maybe even 1000+ rows, so I won't waste bandwidth by posting it all; I'll give a brief description and then post relevant code. I appreciate any suggestions you might have. Programming in XL2003/XP but workbook will also be used in XL2007/XP. I have some currency values in Sheet16, including some that are to the third decimal ($.125, $.150, $.175, etc.). I pull those values into an small array (range?) to process, then paste them into a second (large)array/range, then finally paste the large array/range back to fill an entire worksheet. Somewhere in the translation, I lose the last decimal :( I need to figure out why, and fix it so that later calculations based on these numbers will be accurate. I appreciate any review/advice/corrections- Thanks, Keith 'Create the big array/range so I can later fill it: Dim GraphDataArray As Variant Sheet11.Activate Sheet11.Range("A1:Z65000").Select Selection.Clear 'start with a blank array/range GraphDataArray = Sheet11.Range("A1:Z1000").Value {snip} 'Grab the currency values Dim SubTemp1 As Range Set SubTemp1 = Sheet16.Range(TempUseCol & "10:" & TempUseCol & "14") {snip} 'example of pushing one of these currency values into the large array GraphDataArray(VXD + 24, 16 + 1) = SubTemp1(1) {snip} 'Push the grid back out to the worksheet: Sheet11.Range("A1:Z1000").Value = GraphDataArray |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pulling a currency variable through my arrays drops 3rd decimal?
You need to use .value2 rather than the default .value
GraphDataArray = Sheet11.Range("A1:Z1000").Value2 If you use .Value there is an implicit under-the-covers conversion to VBA Currency datatype which causes the problem regards Charles ___________________________________ The Excel Calculation Site http://www.decisionmodels.com "ker_01" wrote in message ... My code is hundreds or maybe even 1000+ rows, so I won't waste bandwidth by posting it all; I'll give a brief description and then post relevant code. I appreciate any suggestions you might have. Programming in XL2003/XP but workbook will also be used in XL2007/XP. I have some currency values in Sheet16, including some that are to the third decimal ($.125, $.150, $.175, etc.). I pull those values into an small array (range?) to process, then paste them into a second (large)array/range, then finally paste the large array/range back to fill an entire worksheet. Somewhere in the translation, I lose the last decimal :( I need to figure out why, and fix it so that later calculations based on these numbers will be accurate. I appreciate any review/advice/corrections- Thanks, Keith 'Create the big array/range so I can later fill it: Dim GraphDataArray As Variant Sheet11.Activate Sheet11.Range("A1:Z65000").Select Selection.Clear 'start with a blank array/range GraphDataArray = Sheet11.Range("A1:Z1000").Value {snip} 'Grab the currency values Dim SubTemp1 As Range Set SubTemp1 = Sheet16.Range(TempUseCol & "10:" & TempUseCol & "14") {snip} 'example of pushing one of these currency values into the large array GraphDataArray(VXD + 24, 16 + 1) = SubTemp1(1) {snip} 'Push the grid back out to the worksheet: Sheet11.Range("A1:Z1000").Value = GraphDataArray |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
auto decimal in currency | Excel Worksheet Functions | |||
csv drops decimal places | Excel Discussion (Misc queries) | |||
Currency Formatting to 3 decimal Places | Excel Programming | |||
decimal places in currency | Excel Programming | |||
variable as Currency two decimal places | Excel Programming |