Hi,
I am trying to set named range by VBA to another workbook.


Dim LR As Integer
Dim I As Integer
Dim J As Integer
Dim WRM As Workbook
Dim SDR As Worksheet

Set WRM = Workbooks("RUIM new.xls")
Set SDR = WRM.Sheets("Damage Receipt")
For I = 1 To 12
ActiveWorkbook.Names.Add Name:=SDR.Cells(1, I).Value, _
RefersTo:=SDR.Range((Cells(1, I)), Cells(20000, I)),
Visible:=True <<<ERROR
Next

I am getting error on the above statement.
Need help.
Regards,
Madiya

You are missing the line continuation character: "_" (an underscore).
RefersTo:=SDR.Range((Cells(1, I)), Cells(20000, I)), _
Visible:=True
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"Madiya" wrote in message ups.com...
Hi,
I am trying to set named range by VBA to another workbook.
Dim LR As Integer
Dim I As Integer
Dim J As Integer
Dim WRM As Workbook
Dim SDR As Worksheet

Set WRM = Workbooks("RUIM new.xls")
Set SDR = WRM.Sheets("Damage Receipt")
For I = 1 To 12
ActiveWorkbook.Names.Add Name:=SDR.Cells(1, I).Value, _
RefersTo:=SDR.Range((Cells(1, I)), Cells(20000, I)),
Visible:=True <<<ERROR
Next

I am getting error on the above statement.
Need help.
Regards,
Madiya

On Aug 1, 3:21 am, "Jim Cone" wrote:
You are missing the line continuation character: "_" (an underscore).
RefersTo:=SDR.Range((Cells(1, I)), Cells(20000, I)), _
Visible:=True
--
Jim Cone
San Francisco, USAhttp://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)

"Madiya" wrote in oglegroups.com...

Hi,
I am trying to set named range by VBA to another workbook.
Dim LR As Integer
Dim I As Integer
Dim J As Integer
Dim WRM As Workbook
Dim SDR As Worksheet

Set WRM = Workbooks("RUIM new.xls")
Set SDR = WRM.Sheets("Damage Receipt")
For I = 1 To 12
ActiveWorkbook.Names.Add Name:=SDR.Cells(1, I).Value, _
RefersTo:=SDR.Range((Cells(1, I)), Cells(20000, I)),
Visible:=True <<<ERROR
Next

I am getting error on the above statement.
Need help.
Regards,
Madiya

Thanks for your reply.
Actually this is google error.
I have got the whole line from refers upto visible in the same line in
VBA.
There is something else very simple and obvious which I am missing.
But thanks for your help. Pl let me know if you have any other
thought.

Regards,
Madiya

On Aug 1, 4:39 am, Madiya wrote:
On Aug 1, 3:21 am, "Jim Cone" wrote:





You are missing the line continuation character: "_" (an underscore).
RefersTo:=SDR.Range((Cells(1, I)), Cells(20000, I)), _
Visible:=True
--
Jim Cone
San Francisco, USAhttp://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)

"Madiya" wrote in oglegroups.com...

Hi,
I am trying to set named range by VBA to another workbook.
Dim LR As Integer
Dim I As Integer
Dim J As Integer
Dim WRM As Workbook
Dim SDR As Worksheet

Set WRM = Workbooks("RUIM new.xls")
Set SDR = WRM.Sheets("Damage Receipt")
For I = 1 To 12
ActiveWorkbook.Names.Add Name:=SDR.Cells(1, I).Value, _
RefersTo:=SDR.Range((Cells(1, I)), Cells(20000, I)),
Visible:=True <<<ERROR
Next

I am getting error on the above statement.
Need help.
Regards,
Madiya

Thanks for your reply.
Actually this is google error.
I have got the whole line from refers upto visible in the same line in
VBA.
There is something else very simple and obvious which I am missing.
But thanks for your help. Pl let me know if you have any other
thought.

Regards,
Madiya- Hide quoted text -

- Show quoted text -

Hi Jim,

Got the error.
The name is not valid (name should not contain any blank cherecter).
Is there any way I can convert any text in cell containing space to
"_"
For example, say I have a text "abc xyz" which should get converted in
"abc_xyz"


Regards,
Madiya

Sub FillInTheBlanks()
Dim str As String
str = "abc xyz"
str = Application.Substitute(str, " ", "_")
MsgBox str
End Sub
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"Madiya"
wrote in message
Hi Jim,
Got the error.
The name is not valid (name should not contain any blank cherecter).
Is there any way I can convert any text in cell containing space to
"_"
For example, say I have a text "abc xyz" which should get converted in
"abc_xyz"
Regards,
Madiya

On Aug 1, 5:24 am, "Jim Cone" wrote:
Sub FillInTheBlanks()
Dim str As String
str = "abc xyz"
str = Application.Substitute(str, " ", "_")
MsgBox str
End Sub
--
Jim Cone
San Francisco, USAhttp://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)

"Madiya"
wrote in message
Hi Jim,
Got the error.
The name is not valid (name should not contain any blank cherecter).
Is there any way I can convert any text in cell containing space to
"_"
For example, say I have a text "abc xyz" which should get converted in
"abc_xyz"
Regards,
Madiya

Thank you dear Jim.
My job is done with your help.

Regards,
Madiya