| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Further Help Req'd - Looping thru cells in named range
Hi All,
Earlier today l posted a query re looping thru cells in a named range. I tested the answer posted and all seemed OK. However l have now writen the code within the workbook l need it in and it doesn't work. The named range is created OK but it falls down when trying to loop thru cells in the named range. When the macro is run the messages l get when l hover over the rng variable is, rng = Nothing rng.Formula = <Object variable or With block variable not set rng.Text = <Object variable or With block variable not set Can anybody tell me whats wrong with the following code: Sub ConvToFormula() Dim rng As Range Worksheets("Imported Data").Select 'Define RangeName with cell references ActiveWorkbook.Names.Add Name:="ConvToFormulaRange", RefersTo:= _ "=Imported Data!$O$7," & _ "Imported Data!$Q$7," & _ "Imported Data!$R$7," & _ "Imported Data!$T$7," & _ "Imported Data!$U$7," & _ "Imported Data!$V$7," & _ "Imported Data!$W$7" 'Loop thru each cell of named range and convert current formula to text then 'to formula. Effectively Copy.PasteSpecial.Values then insert = at beginning. For Each rng In Sheets("Imported Data").Range("ConvToFormulaRange") rng.Formula = "=" & rng.Text Next 'Tidy up Application.CutCopyMode = False Set rng = Nothing End Sub All help very much appreciated. Regards |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Further Help Req'd - Looping thru cells in named range
This worked for me:
Sub ConvToFormula() Dim rng As Range Worksheets("Imported Data").Select 'Define RangeName with cell references With Worksheets("Imported Data") Set rng1 = .Range("O7,Q7,R7,T7,U7,V7,W7") End With ActiveWorkbook.Names.Add Name:="ConvToFormulaRange", RefersTo:= _ "=" & rng1.Address(External:=True) 'Loop thru each cell of named range and convert current formula to text then 'to formula. Effectively Copy.PasteSpecial.Values then insert = at 'beginning. For Each rng In Worksheets("Imported Data").Range("ConvToFormulaRange") rng.Formula = "=" & rng.Text Next 'Tidy up Application.CutCopyMode = False Set rng = Nothing End Sub You name definition wasn't proper. -- Regards, Tom Ogilvy "Michael Beckinsale" wrote in message ... Hi All, Earlier today l posted a query re looping thru cells in a named range. I tested the answer posted and all seemed OK. However l have now writen the code within the workbook l need it in and it doesn't work. The named range is created OK but it falls down when trying to loop thru cells in the named range. When the macro is run the messages l get when l hover over the rng variable is, rng = Nothing rng.Formula = <Object variable or With block variable not set rng.Text = <Object variable or With block variable not set Can anybody tell me whats wrong with the following code: Sub ConvToFormula() Dim rng As Range Worksheets("Imported Data").Select 'Define RangeName with cell references ActiveWorkbook.Names.Add Name:="ConvToFormulaRange", RefersTo:= _ "=Imported Data!$O$7," & _ "Imported Data!$Q$7," & _ "Imported Data!$R$7," & _ "Imported Data!$T$7," & _ "Imported Data!$U$7," & _ "Imported Data!$V$7," & _ "Imported Data!$W$7" 'Loop thru each cell of named range and convert current formula to text then 'to formula. Effectively Copy.PasteSpecial.Values then insert = at beginning. For Each rng In Sheets("Imported Data").Range("ConvToFormulaRange") rng.Formula = "=" & rng.Text Next 'Tidy up Application.CutCopyMode = False Set rng = Nothing End Sub All help very much appreciated. Regards |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Further Help Req'd - Looping thru cells in named range
If you have option explicit, you need to add
Dim rng1 as Range as well. -- Regards, Tom Ogilvy "Tom Ogilvy" wrote in message ... This worked for me: Sub ConvToFormula() Dim rng As Range Worksheets("Imported Data").Select 'Define RangeName with cell references With Worksheets("Imported Data") Set rng1 = .Range("O7,Q7,R7,T7,U7,V7,W7") End With ActiveWorkbook.Names.Add Name:="ConvToFormulaRange", RefersTo:= _ "=" & rng1.Address(External:=True) 'Loop thru each cell of named range and convert current formula to text then 'to formula. Effectively Copy.PasteSpecial.Values then insert = at 'beginning. For Each rng In Worksheets("Imported Data").Range("ConvToFormulaRange") rng.Formula = "=" & rng.Text Next 'Tidy up Application.CutCopyMode = False Set rng = Nothing End Sub You name definition wasn't proper. -- Regards, Tom Ogilvy "Michael Beckinsale" wrote in message ... Hi All, Earlier today l posted a query re looping thru cells in a named range. I tested the answer posted and all seemed OK. However l have now writen the code within the workbook l need it in and it doesn't work. The named range is created OK but it falls down when trying to loop thru cells in the named range. When the macro is run the messages l get when l hover over the rng variable is, rng = Nothing rng.Formula = <Object variable or With block variable not set rng.Text = <Object variable or With block variable not set Can anybody tell me whats wrong with the following code: Sub ConvToFormula() Dim rng As Range Worksheets("Imported Data").Select 'Define RangeName with cell references ActiveWorkbook.Names.Add Name:="ConvToFormulaRange", RefersTo:= _ "=Imported Data!$O$7," & _ "Imported Data!$Q$7," & _ "Imported Data!$R$7," & _ "Imported Data!$T$7," & _ "Imported Data!$U$7," & _ "Imported Data!$V$7," & _ "Imported Data!$W$7" 'Loop thru each cell of named range and convert current formula to text then 'to formula. Effectively Copy.PasteSpecial.Values then insert = at beginning. For Each rng In Sheets("Imported Data").Range("ConvToFormulaRange") rng.Formula = "=" & rng.Text Next 'Tidy up Application.CutCopyMode = False Set rng = Nothing End Sub All help very much appreciated. Regards |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Further Help Req'd - Looping thru cells in named range
The following seems to work:
Sub ConvToFormula() Dim rng As Range Worksheets("Imported Data").Select 'Define RangeName with cell references ActiveWorkbook.Names.Add Name:="ConvToFormulaRange", RefersTo:= _ "='Imported Data'!$O$7,'Imported Data'!$Q$7," & _ "'Imported Data'!$R$7,'Imported Data'!$T$7," & _ "'Imported Data'!$U$7,'Imported Data'!$V$7,'Imported Data'!$W$7" 'Loop thru each cell of named range and convert current formula to text 'then 'to formula. Effectively Copy.PasteSpecial.Values then insert = at 'beginning. For Each rng In Sheets("Imported Data").Range("ConvToFormulaRange") Set rng = Range(rng.Address) rng.Formula = "=" & """" & rng.Text & """" Next 'Tidy up Application.CutCopyMode = False Set rng = Nothing End Sub Alan Beban Michael Beckinsale wrote: Hi All, Earlier today l posted a query re looping thru cells in a named range. I tested the answer posted and all seemed OK. However l have now writen the code within the workbook l need it in and it doesn't work. The named range is created OK but it falls down when trying to loop thru cells in the named range. When the macro is run the messages l get when l hover over the rng variable is, rng = Nothing rng.Formula = <Object variable or With block variable not set rng.Text = <Object variable or With block variable not set Can anybody tell me whats wrong with the following code: Sub ConvToFormula() Dim rng As Range Worksheets("Imported Data").Select 'Define RangeName with cell references ActiveWorkbook.Names.Add Name:="ConvToFormulaRange", RefersTo:= _ "=Imported Data!$O$7," & _ "Imported Data!$Q$7," & _ "Imported Data!$R$7," & _ "Imported Data!$T$7," & _ "Imported Data!$U$7," & _ "Imported Data!$V$7," & _ "Imported Data!$W$7" 'Loop thru each cell of named range and convert current formula to text then 'to formula. Effectively Copy.PasteSpecial.Values then insert = at beginning. For Each rng In Sheets("Imported Data").Range("ConvToFormulaRange") rng.Formula = "=" & rng.Text Next 'Tidy up Application.CutCopyMode = False Set rng = Nothing End Sub All help very much appreciated. Regards |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Further Help Req'd - Looping thru cells in named range
In an earlier thread, he said he is creating a linking formula, so he
doesn't need/shouldn't have the quotes on either side of rng.text. I made the same wrong suggestion. -- Regards, Tom Ogilvy "Alan Beban" wrote in message ... The following seems to work: Sub ConvToFormula() Dim rng As Range Worksheets("Imported Data").Select 'Define RangeName with cell references ActiveWorkbook.Names.Add Name:="ConvToFormulaRange", RefersTo:= _ "='Imported Data'!$O$7,'Imported Data'!$Q$7," & _ "'Imported Data'!$R$7,'Imported Data'!$T$7," & _ "'Imported Data'!$U$7,'Imported Data'!$V$7,'Imported Data'!$W$7" 'Loop thru each cell of named range and convert current formula to text 'then 'to formula. Effectively Copy.PasteSpecial.Values then insert = at 'beginning. For Each rng In Sheets("Imported Data").Range("ConvToFormulaRange") Set rng = Range(rng.Address) rng.Formula = "=" & """" & rng.Text & """" Next 'Tidy up Application.CutCopyMode = False Set rng = Nothing End Sub Alan Beban Michael Beckinsale wrote: Hi All, Earlier today l posted a query re looping thru cells in a named range. I tested the answer posted and all seemed OK. However l have now writen the code within the workbook l need it in and it doesn't work. The named range is created OK but it falls down when trying to loop thru cells in the named range. When the macro is run the messages l get when l hover over the rng variable is, rng = Nothing rng.Formula = <Object variable or With block variable not set rng.Text = <Object variable or With block variable not set Can anybody tell me whats wrong with the following code: Sub ConvToFormula() Dim rng As Range Worksheets("Imported Data").Select 'Define RangeName with cell references ActiveWorkbook.Names.Add Name:="ConvToFormulaRange", RefersTo:= _ "=Imported Data!$O$7," & _ "Imported Data!$Q$7," & _ "Imported Data!$R$7," & _ "Imported Data!$T$7," & _ "Imported Data!$U$7," & _ "Imported Data!$V$7," & _ "Imported Data!$W$7" 'Loop thru each cell of named range and convert current formula to text then 'to formula. Effectively Copy.PasteSpecial.Values then insert = at beginning. For Each rng In Sheets("Imported Data").Range("ConvToFormulaRange") rng.Formula = "=" & rng.Text Next 'Tidy up Application.CutCopyMode = False Set rng = Nothing End Sub All help very much appreciated. Regards |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Looping thru a range of cells | New Users to Excel | |||
| Array as a "named range" - formula ok in cells, but error as "named range" | Excel Discussion (Misc queries) | |||
| inserting a named range into new cells based on a named cell | Excel Discussion (Misc queries) | |||
| Looping thru cells in a named range | Excel Programming | |||
| looping cells though a named range | Excel Programming | |||
Linear Mode
