In VBA how to select the right format for a range
 

Hi,
I'm trying to change format depending the data in a range.
In the cell D10 a "signe $%#", will tell vba to select the right format like
$ or % or #, etc.
The format need to change the range D11:D17

Exemple if D10 show: $ the range will be change to currency, if the cell D10
show % the range will change to % with 2 decimal.

I did something like this:

Sub FORMATCol()
Dim Col As Range
For Each Col In Range("D11:D70")
If Range("D10") = "$" Then
Col.NumberFormat = "$#,##0"
End If
Next
' Note: Section if not $ change for #
For Each Col In Range("D11:D70")
If Range("D10") = "#" Then
Col.NumberFormat = "#,##0"
End If
Next

End Sub

Problem is: I dont know how to tell the sub to select the right format IF
D10 =$ or #
I tried to add another IF (see the line after the ' ) in it, however it's
not working. Can you explain how to to this? Thank you

In VBA how to select the right format for a range
 

Sub FORMATCol()
Dim s as String
Select Case Range("D10").Text
Case "$"
s = "$ #,##0"
Case "%"
s= 0.00%"
Case "#"
s = "#,##0"
Case Else
s = "General"
End Select
Range("D11:D17").Numberformat = s
End Sub

You have shown both D17 and D70, so adjust the D17 to D70 if that is what it
is supposed to be.
--
Regards,
Tom Ogilvy




"Mouimet" wrote:

Hi,
I'm trying to change format depending the data in a range.
In the cell D10 a "signe $%#", will tell vba to select the right format like
$ or % or #, etc.
The format need to change the range D11:D17

Exemple if D10 show: $ the range will be change to currency, if the cell D10
show % the range will change to % with 2 decimal.

I did something like this:

Sub FORMATCol()
Dim Col As Range
For Each Col In Range("D11:D70")
If Range("D10") = "$" Then
Col.NumberFormat = "$#,##0"
End If
Next
' Note: Section if not $ change for #
For Each Col In Range("D11:D70")
If Range("D10") = "#" Then
Col.NumberFormat = "#,##0"
End If
Next

End Sub

Problem is: I dont know how to tell the sub to select the right format IF
D10 =$ or #
I tried to add another IF (see the line after the ' ) in it, however it's
not working. Can you explain how to to this? Thank you

In VBA how to select the right format for a range
 

Mouimet,

Your could use something like

Sub FORMATCol2()
Range("D11:D70").NumberFormat = Range("D10").Value & "#,##0.00"
End Sub

or, if you want to change the number of decimal places

Sub FORMATCol3()
If Range("D10").Value = "$" Then
Range("D11:D70").NumberFormat = "$#,##0"
End If

If Range("D10").Value = "#" Then
Range("D11:D70").NumberFormat = "#,##0.00"
End If

If Range("D10").Value = "%" Then
Range("D11:D70").NumberFormat = "%#,##0.0"
End If

End Sub

HTH,
Bernie
MS Excel MVP


"Mouimet" wrote in message
...
Hi,
I'm trying to change format depending the data in a range.
In the cell D10 a "signe $%#", will tell vba to select the right format like
$ or % or #, etc.
The format need to change the range D11:D17

Exemple if D10 show: $ the range will be change to currency, if the cell D10
show % the range will change to % with 2 decimal.

I did something like this:

Sub FORMATCol()
Dim Col As Range
For Each Col In Range("D11:D70")
If Range("D10") = "$" Then
Col.NumberFormat = "$#,##0"
End If
Next
' Note: Section if not $ change for #
For Each Col In Range("D11:D70")
If Range("D10") = "#" Then
Col.NumberFormat = "#,##0"
End If
Next

End Sub

Problem is: I dont know how to tell the sub to select the right format IF
D10 =$ or #
I tried to add another IF (see the line after the ' ) in it, however it's
not working. Can you explain how to to this? Thank you

In VBA how to select the right format for a range
 

Just curious, but I don't think I have ever seen the % in front like that

? activecell.Text
%23.14

Is that a common format at the Consumers Union? Elsewhere?

--
Regards,
Tom Ogilvy



"Bernie Deitrick" wrote:

Mouimet,

Your could use something like

Sub FORMATCol2()
Range("D11:D70").NumberFormat = Range("D10").Value & "#,##0.00"
End Sub

or, if you want to change the number of decimal places

Sub FORMATCol3()
If Range("D10").Value = "$" Then
Range("D11:D70").NumberFormat = "$#,##0"
End If

If Range("D10").Value = "#" Then
Range("D11:D70").NumberFormat = "#,##0.00"
End If

If Range("D10").Value = "%" Then
Range("D11:D70").NumberFormat = "%#,##0.0"
End If

End Sub

HTH,
Bernie
MS Excel MVP


"Mouimet" wrote in message
...
Hi,
I'm trying to change format depending the data in a range.
In the cell D10 a "signe $%#", will tell vba to select the right format like
$ or % or #, etc.
The format need to change the range D11:D17

Exemple if D10 show: $ the range will be change to currency, if the cell D10
show % the range will change to % with 2 decimal.

I did something like this:

Sub FORMATCol()
Dim Col As Range
For Each Col In Range("D11:D70")
If Range("D10") = "$" Then
Col.NumberFormat = "$#,##0"
End If
Next
' Note: Section if not $ change for #
For Each Col In Range("D11:D70")
If Range("D10") = "#" Then
Col.NumberFormat = "#,##0"
End If
Next

End Sub

Problem is: I dont know how to tell the sub to select the right format IF
D10 =$ or #
I tried to add another IF (see the line after the ' ) in it, however it's
not working. Can you explain how to to this? Thank you

In VBA how to select the right format for a range
 

No! I just wasn't thinking clearly, and never ran the code....

Bernie


Just curious, but I don't think I have ever seen the % in front like that

? activecell.Text
%23.14

Is that a common format at the Consumers Union? Elsewhere?

In VBA how to select the right format for a range
 

OK. Just curious - it was a good idea anyway.

--
Regards,
Tom Ogilvy

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
No! I just wasn't thinking clearly, and never ran the code....

Bernie


Just curious, but I don't think I have ever seen the % in front like that

? activecell.Text
%23.14

Is that a common format at the Consumers Union? Elsewhere?

In VBA how to select the right format for a range
 

HI,
Sorry if i'm sending this 3 weeks after your answer. I was a lot busy.
Thank you it worked well.
This is also very good for another template.
I have a small question:
I use a combo list to select the numbers. After I select the number I need I
need to click on a cell before the format change.
Do you have a way to make the change of the format at the same time I select
something in the combo list?
Thanks Again

"Tom Ogilvy" wrote:

Just curious, but I don't think I have ever seen the % in front like that

? activecell.Text
%23.14

Is that a common format at the Consumers Union? Elsewhere?

--
Regards,
Tom Ogilvy



"Bernie Deitrick" wrote:

Mouimet,

Your could use something like

Sub FORMATCol2()
Range("D11:D70").NumberFormat = Range("D10").Value & "#,##0.00"
End Sub

or, if you want to change the number of decimal places

Sub FORMATCol3()
If Range("D10").Value = "$" Then
Range("D11:D70").NumberFormat = "$#,##0"
End If

If Range("D10").Value = "#" Then
Range("D11:D70").NumberFormat = "#,##0.00"
End If

If Range("D10").Value = "%" Then
Range("D11:D70").NumberFormat = "%#,##0.0"
End If

End Sub

HTH,
Bernie
MS Excel MVP


"Mouimet" wrote in message
...
Hi,
I'm trying to change format depending the data in a range.
In the cell D10 a "signe $%#", will tell vba to select the right format like
$ or % or #, etc.
The format need to change the range D11:D17

Exemple if D10 show: $ the range will be change to currency, if the cell D10
show % the range will change to % with 2 decimal.

I did something like this:

Sub FORMATCol()
Dim Col As Range
For Each Col In Range("D11:D70")
If Range("D10") = "$" Then
Col.NumberFormat = "$#,##0"
End If
Next
' Note: Section if not $ change for #
For Each Col In Range("D11:D70")
If Range("D10") = "#" Then
Col.NumberFormat = "#,##0"
End If
Next

End Sub

Problem is: I dont know how to tell the sub to select the right format IF
D10 =$ or #
I tried to add another IF (see the line after the ' ) in it, however it's
not working. Can you explain how to to this? Thank you

In VBA how to select the right format for a range
 

You could use the change event: see example code below. Copy the code, right-click the sheet tab,
select "View Code" and paste the code into the window that appears. Of course, you will need to
adjust your range references....

HTH,
Bernie
MS Excel MVP

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address < "$D$10" Then Exit Sub
Range("D11:D70").NumberFormat = "0." & Application.WorksheetFunction.Rept("0", Target.Value)
End Sub


"Mouimet" wrote in message
...
HI,
Sorry if i'm sending this 3 weeks after your answer. I was a lot busy.
Thank you it worked well.
This is also very good for another template.
I have a small question:
I use a combo list to select the numbers. After I select the number I need I
need to click on a cell before the format change.
Do you have a way to make the change of the format at the same time I select
something in the combo list?
Thanks Again

"Tom Ogilvy" wrote:

Just curious, but I don't think I have ever seen the % in front like that

? activecell.Text
%23.14

Is that a common format at the Consumers Union? Elsewhere?

--
Regards,
Tom Ogilvy



"Bernie Deitrick" wrote:

Mouimet,

Your could use something like

Sub FORMATCol2()
Range("D11:D70").NumberFormat = Range("D10").Value & "#,##0.00"
End Sub

or, if you want to change the number of decimal places

Sub FORMATCol3()
If Range("D10").Value = "$" Then
Range("D11:D70").NumberFormat = "$#,##0"
End If

If Range("D10").Value = "#" Then
Range("D11:D70").NumberFormat = "#,##0.00"
End If

If Range("D10").Value = "%" Then
Range("D11:D70").NumberFormat = "%#,##0.0"
End If

End Sub

HTH,
Bernie
MS Excel MVP


"Mouimet" wrote in message
...
Hi,
I'm trying to change format depending the data in a range.
In the cell D10 a "signe $%#", will tell vba to select the right format like
$ or % or #, etc.
The format need to change the range D11:D17

Exemple if D10 show: $ the range will be change to currency, if the cell D10
show % the range will change to % with 2 decimal.

I did something like this:

Sub FORMATCol()
Dim Col As Range
For Each Col In Range("D11:D70")
If Range("D10") = "$" Then
Col.NumberFormat = "$#,##0"
End If
Next
' Note: Section if not $ change for #
For Each Col In Range("D11:D70")
If Range("D10") = "#" Then
Col.NumberFormat = "#,##0"
End If
Next

End Sub

Problem is: I dont know how to tell the sub to select the right format IF
D10 =$ or #
I tried to add another IF (see the line after the ' ) in it, however it's
not working. Can you explain how to to this? Thank you