| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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? |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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? |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How can change range to select active rows instead of :=Range("S10 | Excel Discussion (Misc queries) | |||
| Compare a selected Range with a Named range and select cells that do not exist | Excel Programming | |||
| Select a Range and Format | Excel Programming | |||
| When I select "format cells", the format dialog box does not disp. | Excel Worksheet Functions | |||
| Select Sheet then Select Range | Excel Programming | |||
Linear Mode
