find and replace in vba
 

I need vba to look at ie A1 and look if there is a "/" and replace it with
"-", but it must not return that value in the cell. I want to use the value
and a named range This what i have done so far.

Sub ParentNamedRange()

Dim ParentNamedRange
' set Parent Named range


ParentStockcCode = ???????????

Range("M6:M10").Select
ActiveWorkbook.Names.Add Name:=ParentNamedRange,
RefersToR1C1:="=R6C13:R10C13"
End Sub

Thanks

find and replace in vba
 

Sub MacParentNamedRange()

Dim ParentNamedRange as Range
dim ParentStockCode as String

set parentnamedrange = worksheets("sheet99").range("a1")

parentstockcode = parentnamedrange.value

'but - is not valid in a name, either--so I used an underscor
parentstockcode = replace(parentstockcode,"/","_")
'or if you're using xl97
parentstockcode = application.substitute(parentstockcode,"/","_")

ActiveWorkbook.Names.Add Name:=Parentstockcode, _
RefersToR1C1:="=R6C13:R10C13"

End Sub

And it's not a good idea to name a variable the same as your subroutine.

lasca wrote:

I need vba to look at ie A1 and look if there is a "/" and replace it with
"-", but it must not return that value in the cell. I want to use the value
and a named range This what i have done so far.

Sub ParentNamedRange()

Dim ParentNamedRange
' set Parent Named range


ParentStockcCode = ???????????

Range("M6:M10").Select
ActiveWorkbook.Names.Add Name:=ParentNamedRange,
RefersToR1C1:="=R6C13:R10C13"
End Sub

Thanks

--

Dave Peterson

find and replace in vba
 

Hi thanks.

What would this command be if it is not a given sheet but the current
worksheet ?

set parentnamedrange = worksheets("sheet99").range("a1")

Thanks for pointing out the other errors!




"Dave Peterson" wrote:

Sub MacParentNamedRange()

Dim ParentNamedRange as Range
dim ParentStockCode as String

set parentnamedrange = worksheets("sheet99").range("a1")

parentstockcode = parentnamedrange.value

'but - is not valid in a name, either--so I used an underscor
parentstockcode = replace(parentstockcode,"/","_")
'or if you're using xl97
parentstockcode = application.substitute(parentstockcode,"/","_")

ActiveWorkbook.Names.Add Name:=Parentstockcode, _
RefersToR1C1:="=R6C13:R10C13"

End Sub

And it's not a good idea to name a variable the same as your subroutine.

lasca wrote:

I need vba to look at ie A1 and look if there is a "/" and replace it with
"-", but it must not return that value in the cell. I want to use the value
and a named range This what i have done so far.

Sub ParentNamedRange()

Dim ParentNamedRange
' set Parent Named range


ParentStockcCode = ???????????

Range("M6:M10").Select
ActiveWorkbook.Names.Add Name:=ParentNamedRange,
RefersToR1C1:="=R6C13:R10C13"
End Sub

Thanks

--

Dave Peterson

find and replace in vba
 

set parentnamedrange = Activesheet.range("a1")



lasca wrote:

Hi thanks.

What would this command be if it is not a given sheet but the current
worksheet ?

set parentnamedrange = worksheets("sheet99").range("a1")

Thanks for pointing out the other errors!

"Dave Peterson" wrote:

Sub MacParentNamedRange()

Dim ParentNamedRange as Range
dim ParentStockCode as String

set parentnamedrange = worksheets("sheet99").range("a1")

parentstockcode = parentnamedrange.value

'but - is not valid in a name, either--so I used an underscor
parentstockcode = replace(parentstockcode,"/","_")
'or if you're using xl97
parentstockcode = application.substitute(parentstockcode,"/","_")

ActiveWorkbook.Names.Add Name:=Parentstockcode, _
RefersToR1C1:="=R6C13:R10C13"

End Sub

And it's not a good idea to name a variable the same as your subroutine.

lasca wrote:

I need vba to look at ie A1 and look if there is a "/" and replace it with
"-", but it must not return that value in the cell. I want to use the value
and a named range This what i have done so far.

Sub ParentNamedRange()

Dim ParentNamedRange
' set Parent Named range


ParentStockcCode = ???????????

Range("M6:M10").Select
ActiveWorkbook.Names.Add Name:=ParentNamedRange,
RefersToR1C1:="=R6C13:R10C13"
End Sub

Thanks

--

Dave Peterson


--

Dave Peterson

find and replace in vba
 

Thank you very much!


"Dave Peterson" wrote:

set parentnamedrange = Activesheet.range("a1")



lasca wrote:

Hi thanks.

What would this command be if it is not a given sheet but the current
worksheet ?

set parentnamedrange = worksheets("sheet99").range("a1")

Thanks for pointing out the other errors!

"Dave Peterson" wrote:

Sub MacParentNamedRange()

Dim ParentNamedRange as Range
dim ParentStockCode as String

set parentnamedrange = worksheets("sheet99").range("a1")

parentstockcode = parentnamedrange.value

'but - is not valid in a name, either--so I used an underscor
parentstockcode = replace(parentstockcode,"/","_")
'or if you're using xl97
parentstockcode = application.substitute(parentstockcode,"/","_")

ActiveWorkbook.Names.Add Name:=Parentstockcode, _
RefersToR1C1:="=R6C13:R10C13"

End Sub

And it's not a good idea to name a variable the same as your subroutine.

lasca wrote:

I need vba to look at ie A1 and look if there is a "/" and replace it with
"-", but it must not return that value in the cell. I want to use the value
and a named range This what i have done so far.

Sub ParentNamedRange()

Dim ParentNamedRange
' set Parent Named range


ParentStockcCode = ???????????

Range("M6:M10").Select
ActiveWorkbook.Names.Add Name:=ParentNamedRange,
RefersToR1C1:="=R6C13:R10C13"
End Sub

Thanks

--

Dave Peterson


--

Dave Peterson