Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Code Error
I'm having trouble with the following code, it gets stuck at
"sh.Range("Summary_Gross")." What I am trying to do is change the format of cells, which works fine, but instead of naming cells I would like to name ranges. Sheets("Contract Data").Select Range("C7").Select ActiveCell.FormulaR1C1 = "$" Range("G4:G34").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0.00)" Range("C13:C14").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0.00)" Range("C20").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0.00)" sh.Range("Summary_Gross").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0.00)" sh.Range("Summary_Rate").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0.00)" sh.Range("Summary_Prev").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0.00)" sh.Range("Adj_Gross").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0.00)" sh.Range("Adj_Rate").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0.00)" sh.Range("Adj_Prev").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0.00)" sh.Range("K108:K105").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0." sh.Range("I67").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0." sh.Range("D12:D13").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0." sh.Range("K16").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0." Suggestions? JIM |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Code Error
In your code what is sh..
Try the below...You can assign the format to a variable... Dim sh As Worksheet Dim strFormat As String Set sh = Sheets("Contract Data") strFormat = "[$$-409] #,##0.00;[Red]-([$$-409] #,##0." sh.Range("G4:G34").NumberFormat = strFormat sh.Range("Summary_Gross").NumberFormat = strFormat If this post helps click Yes --------------- Jacob Skaria "Jim" wrote: I'm having trouble with the following code, it gets stuck at "sh.Range("Summary_Gross")." What I am trying to do is change the format of cells, which works fine, but instead of naming cells I would like to name ranges. Sheets("Contract Data").Select Range("C7").Select ActiveCell.FormulaR1C1 = "$" Range("G4:G34").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0.00)" Range("C13:C14").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0.00)" Range("C20").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0.00)" sh.Range("Summary_Gross").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0.00)" sh.Range("Summary_Rate").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0.00)" sh.Range("Summary_Prev").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0.00)" sh.Range("Adj_Gross").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0.00)" sh.Range("Adj_Rate").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0.00)" sh.Range("Adj_Prev").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0.00)" sh.Range("K108:K105").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0." sh.Range("I67").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0." sh.Range("D12:D13").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0." sh.Range("K16").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0." Suggestions? JIM |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Code Error
Hi Jim,
it gets stuck at "sh.Range("Summary_Gross") What is the error number and description you are getting? You name ranges the same way you name cells but select the required range to be named. -- Regards, OssieMac "Jim" wrote: I'm having trouble with the following code, it gets stuck at "sh.Range("Summary_Gross")." What I am trying to do is change the format of cells, which works fine, but instead of naming cells I would like to name ranges. Sheets("Contract Data").Select Range("C7").Select ActiveCell.FormulaR1C1 = "$" Range("G4:G34").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0.00)" Range("C13:C14").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0.00)" Range("C20").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0.00)" sh.Range("Summary_Gross").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0.00)" sh.Range("Summary_Rate").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0.00)" sh.Range("Summary_Prev").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0.00)" sh.Range("Adj_Gross").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0.00)" sh.Range("Adj_Rate").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0.00)" sh.Range("Adj_Prev").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0.00)" sh.Range("K108:K105").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0." sh.Range("I67").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0." sh.Range("D12:D13").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0." sh.Range("K16").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0." Suggestions? JIM |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Code Error
Jacob,
Here is the full code: Private Sub ComboBox1_Change() For Each sh In ActiveWorkbook.Sheets Select Case Me.ComboBox1.Text 'add as many Case tests as required Case "$" Sheets("Contract Data").Select Range("C7").Select ActiveCell.FormulaR1C1 = "$" Range("G4:G34").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0.00)" Range("C13:C14").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0.00)" Range("C20").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0.00)" sh.Range("Summary_Gross").Select.NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0.00)" sh.Range("Summary_Rate").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0.00)" sh.Range("Summary_Prev").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0.00)" sh.Range("Adj_Gross").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0.00)" sh.Range("Adj_Rate").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0.00)" sh.Range("Adj_Prev").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0.00)" sh.Range("K108:K105").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0." sh.Range("I67").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0." sh.Range("D12:D13").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0." sh.Range("K16").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0." Case "£" Sheets("Contract Data").Select Range("C7").Select ActiveCell.FormulaR1C1 = "£" Range("G4:G34").NumberFormat = _ "£ #,##0.00;[Red]-(£ #,##0.00)" Range("C13:C14").NumberFormat = _ "£ #,##0.00;[Red]-(£ #,##0.00)" Range("C20").NumberFormat = _ "£ #,##0.00;[Red]-(£ #,##0.00)" sh.Range("I17:I65").NumberFormat = _ "£ #,##0.00;[Red]-(£ #,##0.00)" sh.Range("K17:K65").NumberFormat = _ "£ #,##0.00;[Red]-(£ #,##0.00)" sh.Range("K69:K73").NumberFormat = _ "£ #,##0.00;[Red]-(£ #,##0.00)" sh.Range("I67").NumberFormat = _ "£ #,##0.00;[Red]-(£ #,##0.00)" sh.Range("D12:D13").NumberFormat = _ "£ #,##0.00;[Red]-(£ #,##0.00)" sh.Range("K16").NumberFormat = _ "£ #,##0.00;[Red]-(£ #,##0.00)" Case "‚¬" Sheets("Contract Data").Select Range("C7").Select ActiveCell.FormulaR1C1 = "‚¬" Range("G4:G34").NumberFormat = _ "‚¬ #,##0.00;[Red]-(‚¬ #,##0.00)" Range("C13:C14").NumberFormat = _ "‚¬ #,##0.00;[Red]-(‚¬ #,##0.00)" Range("C20").NumberFormat = _ "‚¬ #,##0.00;[Red]-(‚¬ #,##0.00)" sh.Range("I17:I65").NumberFormat = _ "‚¬ #,##0.00;[Red]-(‚¬ #,##0.00)" sh.Range("K17:K65").NumberFormat = _ "‚¬ #,##0.00;[Red]-(‚¬ #,##0.00)" sh.Range("K69:K73").NumberFormat = _ "‚¬ #,##0.00;[Red]-(‚¬ #,##0.00)" sh.Range("I67").NumberFormat = _ "‚¬ #,##0.00;[Red]-(‚¬ #,##0.00)" sh.Range("D12:D13").NumberFormat = _ "‚¬ #,##0.00;[Red]-(‚¬ #,##0.00)" sh.Range("K16").NumberFormat = _ "‚¬ #,##0.00;[Red]-(‚¬ #,##0.00)" Case "GEL" Sheets("Contract Data").Select Range("C7").Select ActiveCell.FormulaR1C1 = "GEL" Range("G4:G34").NumberFormat = _ "[$GEL -437]#,##0.00;[Red]-([$GEL -437]#,##0.00)" Range("C13:C14").NumberFormat = _ "[$GEL -437]#,##0.00;[Red]-([$GEL -437]#,##0.00)" Range("C20").NumberFormat = _ "[$GEL -437]#,##0.00;[Red]-([$GEL -437]#,##0.00)" sh.Range("I17:I65").NumberFormat = _ "[$GEL -437]#,##0.00;[Red]-([$GEL -437]#,##0.00)" sh.Range("K17:K65").NumberFormat = _ "[$GEL -437]#,##0.00;[Red]-([$GEL -437]#,##0.00)" sh.Range("K69:K73").NumberFormat = _ "[$GEL -437]#,##0.00;[Red]-([$GEL -437]#,##0.00)" sh.Range("I67").NumberFormat = _ "[$GEL -437]#,##0.00;[Red]-([$GEL -437]#,##0.00)" sh.Range("D12:D13").NumberFormat = _ "[$GEL -437]#,##0.00;[Red]-([$GEL -437]#,##0.00)" sh.Range("K16").NumberFormat = _ "[$GEL -437]#,##0.00;[Red]-([$GEL -437]#,##0.00)" End Select Next sh End Sub It all worked before I started to change ranges (ie. Range("C20:C25) to named ranges. The reason I am making the changes is when I insert rows, they will not be correctly formated, but if i make ranges and insert in the middle, they would be. Suggestions? "Jacob Skaria" wrote: In your code what is sh.. Try the below...You can assign the format to a variable... Dim sh As Worksheet Dim strFormat As String Set sh = Sheets("Contract Data") strFormat = "[$$-409] #,##0.00;[Red]-([$$-409] #,##0." sh.Range("G4:G34").NumberFormat = strFormat sh.Range("Summary_Gross").NumberFormat = strFormat If this post helps click Yes --------------- Jacob Skaria "Jim" wrote: I'm having trouble with the following code, it gets stuck at "sh.Range("Summary_Gross")." What I am trying to do is change the format of cells, which works fine, but instead of naming cells I would like to name ranges. Sheets("Contract Data").Select Range("C7").Select ActiveCell.FormulaR1C1 = "$" Range("G4:G34").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0.00)" Range("C13:C14").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0.00)" Range("C20").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0.00)" sh.Range("Summary_Gross").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0.00)" sh.Range("Summary_Rate").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0.00)" sh.Range("Summary_Prev").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0.00)" sh.Range("Adj_Gross").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0.00)" sh.Range("Adj_Rate").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0.00)" sh.Range("Adj_Prev").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0.00)" sh.Range("K108:K105").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0." sh.Range("I67").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0." sh.Range("D12:D13").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0." sh.Range("K16").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0." Suggestions? JIM |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Code Error
Jacob,
I forgot to say that these changes are happening on mulitple sheets. I have a different code that copies sheet 'Cert 1' and creates sheet 'Cert 2' and so on. These format changes happen to every sheet with 'Cert ' in the title. "Jacob Skaria" wrote: In your code what is sh.. Try the below...You can assign the format to a variable... Dim sh As Worksheet Dim strFormat As String Set sh = Sheets("Contract Data") strFormat = "[$$-409] #,##0.00;[Red]-([$$-409] #,##0." sh.Range("G4:G34").NumberFormat = strFormat sh.Range("Summary_Gross").NumberFormat = strFormat If this post helps click Yes --------------- Jacob Skaria "Jim" wrote: I'm having trouble with the following code, it gets stuck at "sh.Range("Summary_Gross")." What I am trying to do is change the format of cells, which works fine, but instead of naming cells I would like to name ranges. Sheets("Contract Data").Select Range("C7").Select ActiveCell.FormulaR1C1 = "$" Range("G4:G34").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0.00)" Range("C13:C14").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0.00)" Range("C20").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0.00)" sh.Range("Summary_Gross").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0.00)" sh.Range("Summary_Rate").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0.00)" sh.Range("Summary_Prev").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0.00)" sh.Range("Adj_Gross").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0.00)" sh.Range("Adj_Rate").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0.00)" sh.Range("Adj_Prev").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0.00)" sh.Range("K108:K105").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0." sh.Range("I67").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0." sh.Range("D12:D13").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0." sh.Range("K16").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0." Suggestions? JIM |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Code Error
The error is :
Run-time error '1004': Application-defined or joject-defined error "OssieMac" wrote: Hi Jim, it gets stuck at "sh.Range("Summary_Gross") What is the error number and description you are getting? You name ranges the same way you name cells but select the required range to be named. -- Regards, OssieMac "Jim" wrote: I'm having trouble with the following code, it gets stuck at "sh.Range("Summary_Gross")." What I am trying to do is change the format of cells, which works fine, but instead of naming cells I would like to name ranges. Sheets("Contract Data").Select Range("C7").Select ActiveCell.FormulaR1C1 = "$" Range("G4:G34").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0.00)" Range("C13:C14").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0.00)" Range("C20").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0.00)" sh.Range("Summary_Gross").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0.00)" sh.Range("Summary_Rate").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0.00)" sh.Range("Summary_Prev").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0.00)" sh.Range("Adj_Gross").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0.00)" sh.Range("Adj_Rate").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0.00)" sh.Range("Adj_Prev").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0.00)" sh.Range("K108:K105").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0." sh.Range("I67").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0." sh.Range("D12:D13").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0." sh.Range("K16").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0." Suggestions? JIM |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Code Error
In a different section of the same sheet, I get another error. It's a run
time error '424' object required. Here is the code: Sub Hide_Gross_Values() ' ' Hide_Gross_Values Macro ' ' sh.Columns("G").Select Selection.EntireColumn.Hidden = True End Sub I want to hide column G when this code executes on all sheets with 'cert' in the title. "OssieMac" wrote: Hi Jim, it gets stuck at "sh.Range("Summary_Gross") What is the error number and description you are getting? You name ranges the same way you name cells but select the required range to be named. -- Regards, OssieMac "Jim" wrote: I'm having trouble with the following code, it gets stuck at "sh.Range("Summary_Gross")." What I am trying to do is change the format of cells, which works fine, but instead of naming cells I would like to name ranges. Sheets("Contract Data").Select Range("C7").Select ActiveCell.FormulaR1C1 = "$" Range("G4:G34").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0.00)" Range("C13:C14").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0.00)" Range("C20").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0.00)" sh.Range("Summary_Gross").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0.00)" sh.Range("Summary_Rate").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0.00)" sh.Range("Summary_Prev").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0.00)" sh.Range("Adj_Gross").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0.00)" sh.Range("Adj_Rate").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0.00)" sh.Range("Adj_Prev").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0.00)" sh.Range("K108:K105").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0." sh.Range("I67").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0." sh.Range("D12:D13").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0." sh.Range("K16").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0." Suggestions? JIM |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Code Error
Hi Jim
Few points to be noted --You cannot have the same named range in all sheets..Can you? --So if you can name it with relevance to the sheetname for example sheetname as a prefix. Range("G4:G34") of Sheet1 should be named as sheet1_name1 Range("G4:G34") of Sheet2 should be named as sheet2_name1 in which case you can use the named ranges in the macro --Please find the modified code. Hope you will review and feedback Sub Macro() Dim strFormat As String Dim ws As Worksheet Dim sh As Worksheet Set ws = Sheets("Contract Data") ws.Range("C7") = Me.combobox1.Text Select Case Trim(Me.combobox1.Text) Case "$" strFormat = "[$$-409] #,##0.00;[Red]-([$$-409] #,##0.00)" Case "£" strFormat = "£ #,##0.00;[Red]-(£ #,##0.00)" Case "‚¬" strFormat = "‚¬ #,##0.00;[Red]-(‚¬ #,##0.00)" Case "GEL" strFormat = "[$GEL -437]#,##0.00;[Red]-([$GEL -437]#,##0.00)" End Select ws.Range("G4:G34").NumberFormat = strFormat ws.Range("C13:C14").NumberFormat = strFormat ws.Range("C20").NumberFormat = strFormat For Each sh In ActiveWorkbook.Sheets sh.Range("I17:I65").NumberFormat = strFormat sh.Range("K17:K65").NumberFormat = strFormat sh.Range("K69:K73").NumberFormat = strFormat sh.Range("I67").NumberFormat = strFormat sh.Range("D12:D13").NumberFormat = strFormat sh.Range("K16").NumberFormat = strFormat Next sh End Sub If this post helps click Yes --------------- Jacob Skaria "Jim" wrote: Jacob, Here is the full code: Private Sub ComboBox1_Change() For Each sh In ActiveWorkbook.Sheets Select Case Me.ComboBox1.Text 'add as many Case tests as required Case "$" Sheets("Contract Data").Select Range("C7").Select ActiveCell.FormulaR1C1 = "$" Range("G4:G34").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0.00)" Range("C13:C14").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0.00)" Range("C20").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0.00)" sh.Range("Summary_Gross").Select.NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0.00)" sh.Range("Summary_Rate").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0.00)" sh.Range("Summary_Prev").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0.00)" sh.Range("Adj_Gross").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0.00)" sh.Range("Adj_Rate").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0.00)" sh.Range("Adj_Prev").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0.00)" sh.Range("K108:K105").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0." sh.Range("I67").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0." sh.Range("D12:D13").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0." sh.Range("K16").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0." Case "£" Sheets("Contract Data").Select Range("C7").Select ActiveCell.FormulaR1C1 = "£" Range("G4:G34").NumberFormat = _ "£ #,##0.00;[Red]-(£ #,##0.00)" Range("C13:C14").NumberFormat = _ "£ #,##0.00;[Red]-(£ #,##0.00)" Range("C20").NumberFormat = _ "£ #,##0.00;[Red]-(£ #,##0.00)" sh.Range("I17:I65").NumberFormat = _ "£ #,##0.00;[Red]-(£ #,##0.00)" sh.Range("K17:K65").NumberFormat = _ "£ #,##0.00;[Red]-(£ #,##0.00)" sh.Range("K69:K73").NumberFormat = _ "£ #,##0.00;[Red]-(£ #,##0.00)" sh.Range("I67").NumberFormat = _ "£ #,##0.00;[Red]-(£ #,##0.00)" sh.Range("D12:D13").NumberFormat = _ "£ #,##0.00;[Red]-(£ #,##0.00)" sh.Range("K16").NumberFormat = _ "£ #,##0.00;[Red]-(£ #,##0.00)" Case "‚¬" Sheets("Contract Data").Select Range("C7").Select ActiveCell.FormulaR1C1 = "‚¬" Range("G4:G34").NumberFormat = _ "‚¬ #,##0.00;[Red]-(‚¬ #,##0.00)" Range("C13:C14").NumberFormat = _ "‚¬ #,##0.00;[Red]-(‚¬ #,##0.00)" Range("C20").NumberFormat = _ "‚¬ #,##0.00;[Red]-(‚¬ #,##0.00)" sh.Range("I17:I65").NumberFormat = _ "‚¬ #,##0.00;[Red]-(‚¬ #,##0.00)" sh.Range("K17:K65").NumberFormat = _ "‚¬ #,##0.00;[Red]-(‚¬ #,##0.00)" sh.Range("K69:K73").NumberFormat = _ "‚¬ #,##0.00;[Red]-(‚¬ #,##0.00)" sh.Range("I67").NumberFormat = _ "‚¬ #,##0.00;[Red]-(‚¬ #,##0.00)" sh.Range("D12:D13").NumberFormat = _ "‚¬ #,##0.00;[Red]-(‚¬ #,##0.00)" sh.Range("K16").NumberFormat = _ "‚¬ #,##0.00;[Red]-(‚¬ #,##0.00)" Case "GEL" Sheets("Contract Data").Select Range("C7").Select ActiveCell.FormulaR1C1 = "GEL" Range("G4:G34").NumberFormat = _ "[$GEL -437]#,##0.00;[Red]-([$GEL -437]#,##0.00)" Range("C13:C14").NumberFormat = _ "[$GEL -437]#,##0.00;[Red]-([$GEL -437]#,##0.00)" Range("C20").NumberFormat = _ "[$GEL -437]#,##0.00;[Red]-([$GEL -437]#,##0.00)" sh.Range("I17:I65").NumberFormat = _ "[$GEL -437]#,##0.00;[Red]-([$GEL -437]#,##0.00)" sh.Range("K17:K65").NumberFormat = _ "[$GEL -437]#,##0.00;[Red]-([$GEL -437]#,##0.00)" sh.Range("K69:K73").NumberFormat = _ "[$GEL -437]#,##0.00;[Red]-([$GEL -437]#,##0.00)" sh.Range("I67").NumberFormat = _ "[$GEL -437]#,##0.00;[Red]-([$GEL -437]#,##0.00)" sh.Range("D12:D13").NumberFormat = _ "[$GEL -437]#,##0.00;[Red]-([$GEL -437]#,##0.00)" sh.Range("K16").NumberFormat = _ "[$GEL -437]#,##0.00;[Red]-([$GEL -437]#,##0.00)" End Select Next sh End Sub It all worked before I started to change ranges (ie. Range("C20:C25) to named ranges. The reason I am making the changes is when I insert rows, they will not be correctly formated, but if i make ranges and insert in the middle, they would be. Suggestions? "Jacob Skaria" wrote: In your code what is sh.. Try the below...You can assign the format to a variable... Dim sh As Worksheet Dim strFormat As String Set sh = Sheets("Contract Data") strFormat = "[$$-409] #,##0.00;[Red]-([$$-409] #,##0." sh.Range("G4:G34").NumberFormat = strFormat sh.Range("Summary_Gross").NumberFormat = strFormat If this post helps click Yes --------------- Jacob Skaria "Jim" wrote: I'm having trouble with the following code, it gets stuck at "sh.Range("Summary_Gross")." What I am trying to do is change the format of cells, which works fine, but instead of naming cells I would like to name ranges. Sheets("Contract Data").Select Range("C7").Select ActiveCell.FormulaR1C1 = "$" Range("G4:G34").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0.00)" Range("C13:C14").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0.00)" Range("C20").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0.00)" sh.Range("Summary_Gross").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0.00)" sh.Range("Summary_Rate").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0.00)" sh.Range("Summary_Prev").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0.00)" sh.Range("Adj_Gross").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0.00)" sh.Range("Adj_Rate").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0.00)" sh.Range("Adj_Prev").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0.00)" sh.Range("K108:K105").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0." sh.Range("I67").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0." sh.Range("D12:D13").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0." sh.Range("K16").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0." Suggestions? JIM |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Code Error
Jim
If you are looking to avoid mentioning the range in code; there is an alternative. The range mentioned in your code which is applicable for all sheets is "I17:I65,K17:K65,K69:K73,I67,D12:D13,K16" mention this as a string (without quotes ) in a unused cell in a sheet. You can even change this without touching th e code. Try the below Sub Macro() Dim strRange As String, strFormat As String Dim ws As Worksheet, sh As Worksheet Set ws = Sheets("Contract Data") ws.Range("C7") = Me.combobox1.Text 'The below line can be removed if the range is to be taken from cell J1 strRange = "I17:I65,K17:K65,K69:K73,I67,D12:D13,K16" 'strRange = ws.Range("J1").Text 'If range is stored in cell J1 Select Case Trim(Me.combobox1.Text) Case "$" strFormat = "[$$-409] #,##0.00;[Red]-([$$-409] #,##0.00)" Case "£" strFormat = "£ #,##0.00;[Red]-(£ #,##0.00)" Case "‚¬" strFormat = "‚¬ #,##0.00;[Red]-(‚¬ #,##0.00)" Case "GEL" strFormat = "[$GEL -437]#,##0.00;[Red]-([$GEL -437]#,##0.00)" End Select ws.Range("G4:G34,C13:C14,C20").NumberFormat = strFormat For Each sh In ActiveWorkbook.Sheets sh.Range(strRange).NumberFormat = strFormat Next sh End Sub If this post helps click Yes --------------- Jacob Skaria "Jim" wrote: I'm having trouble with the following code, it gets stuck at "sh.Range("Summary_Gross")." What I am trying to do is change the format of cells, which works fine, but instead of naming cells I would like to name ranges. Sheets("Contract Data").Select Range("C7").Select ActiveCell.FormulaR1C1 = "$" Range("G4:G34").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0.00)" Range("C13:C14").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0.00)" Range("C20").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0.00)" sh.Range("Summary_Gross").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0.00)" sh.Range("Summary_Rate").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0.00)" sh.Range("Summary_Prev").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0.00)" sh.Range("Adj_Gross").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0.00)" sh.Range("Adj_Rate").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0.00)" sh.Range("Adj_Prev").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0.00)" sh.Range("K108:K105").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0." sh.Range("I67").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0." sh.Range("D12:D13").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0." sh.Range("K16").NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0." Suggestions? JIM |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Code Error
Hi again Jim,
Further to what Jacob has said, I notice that you were attempting to select a range in a worksheet that was not the selected worksheet. Cannot do that. Your code. sh.Range("Summary_Gross").Select.NumberFormat = _ "[$$-409] #,##0.00;[Red]-([$$-409] #,##0.00)" Sheets("Contract Data") was the selected sheet at that point of your code. There should be no need to select ranges to alter their contents if you correctly address the ranges. The example below changes the values without selecting the ranges. I have also included some more line breaks so that hopefully the lines won't break up on this post. Just as a point of interest, line breaks cannot be placed anywhere in a string between double quotes. However you can break the string into separate parts and use a line break. Insert double quotes where you want the break. Insert the space and underscore for the line break. Press Enter to push the code to the next line. Insert an ampersand (&) then double quotes at the start of the line. The above method effectively breaks the string between the double quotes into 2 strings and then concatenates them with the ampersand. Anyway the sample code that does not select worksheets or ranges. For Each sh In ActiveWorkbook.Sheets 'Select Case Me.ComboBox1.Text 'add as many Case tests as required Case "$" With Sheets("Contract Data") .Range("C7").FormulaR1C1 = "$" .Range("G4:G34").NumberFormat = _ "[$$-409] #,##0.00;[Red]-" _ & "([$$-409] #,##0.00)" .Range("C13:C14").NumberFormat = _ "[$$-409] #,##0.00;[Red]-" _ & "([$$-409] #,##0.00)" .Range("C20").NumberFormat = _ "[$$-409] #,##0.00;[Red]-" _ & "([$$-409] #,##0.00)" End With With sh .Range("Summary_Gross").NumberFormat = _ "[$$-409] #,##0.00;[Red]-" _ & "([$$-409] #,##0.00)" .Range("Summary_Rate").NumberFormat = _ "[$$-409] #,##0.00;[Red]-" _ & "([$$-409] #,##0.00)" .Range("Summary_Prev").NumberFormat = _ "[$$-409] #,##0.00;[Red]-" _ & "([$$-409] #,##0.00)" .Range("Adj_Gross").NumberFormat = _ "[$$-409] #,##0.00;[Red]-" _ & "([$$-409] #,##0.00)" .Range("Adj_Rate").NumberFormat = _ "[$$-409] #,##0.00;[Red]-" _ & "([$$-409] #,##0.00)" .Range("Adj_Prev").NumberFormat = _ "[$$-409] #,##0.00;[Red]-" _ & "([$$-409] #,##0.00)" .Range("K108:K105").NumberFormat = _ "[$$-409] #,##0.00;[Red]-" _ & "([$$-409] #,##0." .Range("I67").NumberFormat = _ "[$$-409] #,##0.00;[Red]-" _ & "([$$-409] #,##0." .Range("D12:D13").NumberFormat = _ "[$$-409] #,##0.00;[Red]-" _ & "([$$-409] #,##0." .Range("K16").NumberFormat = _ "[$$-409] #,##0.00;[Red]-" _ & "([$$-409] #,##0." End With Now the following error 424. Where did you dim sh? Was it inside another sub? If so, the sub where it was running did not know its value. If you want to use a variable in another sub in the same module then Dim it at the top of the VBA editor before any subs (Called the Declarations area.) If you want to use a variable in multiple modules then delare it as a Public variable but still in the Declarations area like this. Public sh as Worksheet Hope this helps to point you in the right direction. -- Regards, OssieMac |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Protect Sheet with code, but then code will not Paste error. How do i get around this. Please read for explainations.... | Excel Programming | |||
OnTime code error "can't execute code in break mode" | Excel Programming | |||
Error in Excel VBA Code (Error 91) | Excel Programming | |||
How can I still go to the error-code after a On Error Goto? | Excel Programming | |||
Code Error - Run Time Error 5 (Disable Cut, Copy & Paste) | Excel Programming |