Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
comparing amounts
I am comparing 2 spreadsheets to find identical items. I create a reference
column for each sheet. Each reference number contains the amount of the item. The sources for the data in each sheet comes from different sources. One sheet, the amount is a text field and displays as 1,500.00 in the formula bar and in the cell. The other sheet is a number field and displays as 1500 in the formula bar and as 1,500.00 in the cell. I need to convert the amounts in one sheet so they match the amounts in the other sheet when shown in the formula bar. Ideally, I would like to convert the amounts in the second sheet so they display commas and decimal points in the formula bar. Any suggestions would be greatly appreciated. Thanks for the help... -- JT |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
comparing amounts
JT
The only way I know how to do what you want is to change the value to text. This macro will do that for all of Column A starting with row 2. Note that the subsequent values are text, not numbers. HTH Otto Sub Macro1() Dim TheRng As Range Dim i As Range Dim TheValue As Double Set TheRng = Range("A2", Range("A" & Rows.Count).End(xlUp)) For Each i In TheRng TheValue = i.Value i.ClearContents i.NumberFormat = "@" i.Value = Format(TheValue, "#,##0.00") Next i End Sub "JT" wrote in message ... I am comparing 2 spreadsheets to find identical items. I create a reference column for each sheet. Each reference number contains the amount of the item. The sources for the data in each sheet comes from different sources. One sheet, the amount is a text field and displays as 1,500.00 in the formula bar and in the cell. The other sheet is a number field and displays as 1500 in the formula bar and as 1,500.00 in the cell. I need to convert the amounts in one sheet so they match the amounts in the other sheet when shown in the formula bar. Ideally, I would like to convert the amounts in the second sheet so they display commas and decimal points in the formula bar. Any suggestions would be greatly appreciated. Thanks for the help... -- JT |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Comparing Row by Row - A-D dollar amounts to E dollar amount | Excel Worksheet Functions | |||
Rolling Monthly Amounts to Annual Monthly Amounts | Excel Discussion (Misc queries) | |||
11 $ amounts, 6 of the amounts must equal $3854.12, which 6? | Excel Discussion (Misc queries) | |||
How do I add 5 to amounts <25, 10 to amounts 25<100, etc? | Excel Worksheet Functions | |||
same amounts from two different database | Excel Discussion (Misc queries) |