ExcelBanter - Further Help Req'd - Looping thru cells in named range

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Further Help Req'd - Looping thru cells in named range (https://www.excelbanter.com/excel-programming/275899-further-help-reqd-looping-thru-cells-named-range.html)

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

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

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

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

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