Name.Add problem
 

I am trying to set a range based on a fixed first cell and finding the last
cell in the column with data and assigning it a variable name to be used in
the Selection.add.name code. The string variable displays the correct range
when I step through it but does not work in the Find statement. What have I
done wrong?
Thank you.

'Set cells for range name
Dim strLastInA As String
Range("A6500").Select
Selection.End(xlUp).Select
strLastInA = "A4:" & ActiveCell.Address

ActiveSheet.Names.Add Name:="RepUnitAbbr", RefersTo:=strLastInA
'
Cells.Range(RepUnitAbbr).Replace What:="SBMU", Replacement:="S ",
Lookat:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, searchformat:=False, _
ReplaceFormat:=False

Name.Add problem
 

TRy this

'Set cells for range name
Range(Range("A4"), Range("A" & Rows.Count).End(xlUp)).Name =
"RepUnitAbbr"
'
Range(RepUnitAbbr).Replace What:="SBMU", _
Replacement:="S ", _
Lookat:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False, _
searchformat:=False, _
ReplaceFormat:=False

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Billy B" wrote in message
...
I am trying to set a range based on a fixed first cell and finding the last
cell in the column with data and assigning it a variable name to be used
in
the Selection.add.name code. The string variable displays the correct
range
when I step through it but does not work in the Find statement. What have
I
done wrong?
Thank you.

'Set cells for range name
Dim strLastInA As String
Range("A6500").Select
Selection.End(xlUp).Select
strLastInA = "A4:" & ActiveCell.Address

ActiveSheet.Names.Add Name:="RepUnitAbbr", RefersTo:=strLastInA
'
Cells.Range(RepUnitAbbr).Replace What:="SBMU", Replacement:="S ",
Lookat:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, searchformat:=False, _
ReplaceFormat:=False