VBA Code Error

J

Jim

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
 
J

Jacob Skaria

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
 
O

OssieMac

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.
 
J

Jim

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 said:
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 said:
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
Click to expand...
Click to expand...
 
J

Jim

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 said:
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 said:
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
Click to expand...
Click to expand...
 
J

Jim

The error is :

Run-time error '1004':

Application-defined or joject-defined error

OssieMac said:
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 said:
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
Click to expand...
Click to expand...
 
J

Jim

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 said:
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 said:
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
Click to expand...
Click to expand...
 
J

Jacob Skaria

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 said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
J

Jacob Skaria

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
 
O

OssieMac

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA Code Optimization for Array Formulas 0
Convert Excel vba to Access 3
stop looping and comments on VBA 3
VBA created formulas won't autocalculate 5
Alter code 0
Alter existing code 5
Whats wrong with this code 5
Pivot Tables in VBA 4

Top