Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Format cell depending upon type of data pulled
I have created a table that pulls and displays data from one of the many
named ranges available to and selected by the user. The data pulled will have a different number of decimal places depending upon what named range is selected. I want to be able to change the format of the cells displaying the data so as to display the correct number of digits and, if appropraite, as currency or just a number. I have limited experience with VBA, so can you lead me in the direction to pursue to resolve this? Sample scripts are great - but even pointers on where to learn about the functions needed is helpful. Thanks. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Format cell depending upon type of data pulled
An easy way is to pick up the format from the Named cell as well as the value:
Sub dural() Dim r As Range, destn As Range Dim v As Variant Dim s As String Set r = Application.InputBox(prompt:="enter range", Type:=8) v = r.Value s = r.NumberFormat Set destn = Range("B9") destn.Value = v destn.NumberFormat = s End Sub You could also use copy/paste. -- Gary''s Student - gsnu200859 "tomeck" wrote: I have created a table that pulls and displays data from one of the many named ranges available to and selected by the user. The data pulled will have a different number of decimal places depending upon what named range is selected. I want to be able to change the format of the cells displaying the data so as to display the correct number of digits and, if appropraite, as currency or just a number. I have limited experience with VBA, so can you lead me in the direction to pursue to resolve this? Sample scripts are great - but even pointers on where to learn about the functions needed is helpful. Thanks. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Format cell depending upon type of data pulled
OK, I can see what you are doing, but I am using a vlookup command to pull
the data based upon user input, so each cell is filled by a different vlookup command. So, I am looking to then go back to all of cells populated by the vlookup commands and format them to a certain number of decimal places. Is there a way to do this? "Gary''s Student" wrote: An easy way is to pick up the format from the Named cell as well as the value: Sub dural() Dim r As Range, destn As Range Dim v As Variant Dim s As String Set r = Application.InputBox(prompt:="enter range", Type:=8) v = r.Value s = r.NumberFormat Set destn = Range("B9") destn.Value = v destn.NumberFormat = s End Sub You could also use copy/paste. -- Gary''s Student - gsnu200859 "tomeck" wrote: I have created a table that pulls and displays data from one of the many named ranges available to and selected by the user. The data pulled will have a different number of decimal places depending upon what named range is selected. I want to be able to change the format of the cells displaying the data so as to display the correct number of digits and, if appropraite, as currency or just a number. I have limited experience with VBA, so can you lead me in the direction to pursue to resolve this? Sample scripts are great - but even pointers on where to learn about the functions needed is helpful. Thanks. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Format cell depending upon type of data pulled
Using VLOOKUP() or any reference method makes it very difficult. Consider the very simplest example: In A2 enter: =A1 You can put a value in A1 and it will be reflected in A2, but as you change the format in A1, nothing will changed in A2. Only the value is getting captured, not the format. -- Gary''s Student - gsnu200859 "tomeck" wrote: OK, I can see what you are doing, but I am using a vlookup command to pull the data based upon user input, so each cell is filled by a different vlookup command. So, I am looking to then go back to all of cells populated by the vlookup commands and format them to a certain number of decimal places. Is there a way to do this? "Gary''s Student" wrote: An easy way is to pick up the format from the Named cell as well as the value: Sub dural() Dim r As Range, destn As Range Dim v As Variant Dim s As String Set r = Application.InputBox(prompt:="enter range", Type:=8) v = r.Value s = r.NumberFormat Set destn = Range("B9") destn.Value = v destn.NumberFormat = s End Sub You could also use copy/paste. -- Gary''s Student - gsnu200859 "tomeck" wrote: I have created a table that pulls and displays data from one of the many named ranges available to and selected by the user. The data pulled will have a different number of decimal places depending upon what named range is selected. I want to be able to change the format of the cells displaying the data so as to display the correct number of digits and, if appropraite, as currency or just a number. I have limited experience with VBA, so can you lead me in the direction to pursue to resolve this? Sample scripts are great - but even pointers on where to learn about the functions needed is helpful. Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Format Cell as custom type but data doesn't display like I custom. | Excel Discussion (Misc queries) | |||
Font format according con cell content data type | Excel Programming | |||
format many rows depending on record type | Excel Programming | |||
How do I format a row depending on the value of a cell in the row | Excel Discussion (Misc queries) | |||
Variant type for Excel cell percentage data format | Excel Programming |