Format cell depending upon type of data pulled

[tomeck]

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.

 

[Gary''s Student]

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.

 

[tomeck]

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]

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.