Hello,
I have already received some great advice on here on how to name a field,
but I have a question in regards to the code. The following code searches a
2 columns where column B has repeated departments, and names the field of job
positions in Column C. It creates a names based on column B. In the code,
it replaces " " with "". How can I also use the code and replace a few other
characters with "", such as / - ' ? I have tried everything...help

lRow = Cells(Rows.Count, 2).End(xlUp).Row + 1
Set colB = Range(Cells(2, 2), Cells(lRow, 2))
Dept = Trim(Cells(2, 2).Value)
newDept = Replace(Dept, " ", "")

sRow = 2
eRow = 2
For Each chk In colB
If chk.Value < Dept Then
ActiveWorkbook.Names.Add Name:=newDept, _
RefersToR1C1:="=Data!R" & sRow & "C3:R" & eRow & "C3"
Dept = chk.Value
newDept = Replace(Dept, " ", "")
sRow = chk.Row
eRow = chk.Row
Else
eRow = chk.Row
End If
Next chk

End Sub


Thanks!!
Mike

Make a line of code for each character:
newDept = Replace(Dept, " ", "")
newDept = Replace(Dept, "/", "")
newDept = Replace(Dept, "-", "")
newDept = Replace(Dept, "'", "")

Mike F

"Mike R." wrote in message
...
Hello,
I have already received some great advice on here on how to name a field,
but I have a question in regards to the code. The following code searches
a
2 columns where column B has repeated departments, and names the field of
job
positions in Column C. It creates a names based on column B. In the
code,
it replaces " " with "". How can I also use the code and replace a few
other
characters with "", such as / - ' ? I have tried everything...help

lRow = Cells(Rows.Count, 2).End(xlUp).Row + 1
Set colB = Range(Cells(2, 2), Cells(lRow, 2))
Dept = Trim(Cells(2, 2).Value)
newDept = Replace(Dept, " ", "")

sRow = 2
eRow = 2
For Each chk In colB
If chk.Value < Dept Then
ActiveWorkbook.Names.Add Name:=newDept, _
RefersToR1C1:="=Data!R" & sRow & "C3:R" & eRow & "C3"
Dept = chk.Value
newDept = Replace(Dept, " ", "")
sRow = chk.Row
eRow = chk.Row
Else
eRow = chk.Row
End If
Next chk

End Sub


Thanks!!
Mike

Hi,
and where whould I put this code. I have tried to put this in, but it
didn't seem to work. help.
Mike

"Mike Fogleman" wrote:

Make a line of code for each character:
newDept = Replace(Dept, " ", "")
newDept = Replace(Dept, "/", "")
newDept = Replace(Dept, "-", "")
newDept = Replace(Dept, "'", "")

Mike F

"Mike R." wrote in message
...
Hello,
I have already received some great advice on here on how to name a field,
but I have a question in regards to the code. The following code searches
a
2 columns where column B has repeated departments, and names the field of
job
positions in Column C. It creates a names based on column B. In the
code,
it replaces " " with "". How can I also use the code and replace a few
other
characters with "", such as / - ' ? I have tried everything...help

lRow = Cells(Rows.Count, 2).End(xlUp).Row + 1
Set colB = Range(Cells(2, 2), Cells(lRow, 2))
Dept = Trim(Cells(2, 2).Value)
newDept = Replace(Dept, " ", "")

sRow = 2
eRow = 2
For Each chk In colB
If chk.Value < Dept Then
ActiveWorkbook.Names.Add Name:=newDept, _
RefersToR1C1:="=Data!R" & sRow & "C3:R" & eRow & "C3"
Dept = chk.Value
newDept = Replace(Dept, " ", "")
sRow = chk.Row
eRow = chk.Row
Else
eRow = chk.Row
End If
Next chk

End Sub


Thanks!!
Mike

Sorry I didn't capture each new string as characters were removed:

newDept = Replace(Dept, " ", "")
Dept = newDept
newDept = Replace(Dept, "/", "")
Dept = newDept
newDept = Replace(Dept, "-", "")
Dept = newDept
newDept = Replace(Dept, "'", "")

You already had this first line of code to remove spaces, I think it was in
two places in your code. Put the remaining 6 lines right after it, in both
places, just like it looks above.

Mike F
"Mike R." wrote in message
...
Hi,
and where whould I put this code. I have tried to put this in, but it
didn't seem to work. help.
Mike

"Mike Fogleman" wrote:

Make a line of code for each character:
newDept = Replace(Dept, " ", "")
newDept = Replace(Dept, "/", "")
newDept = Replace(Dept, "-", "")
newDept = Replace(Dept, "'", "")

Mike F

"Mike R." wrote in message
...
Hello,
I have already received some great advice on here on how to name a
field,
but I have a question in regards to the code. The following code
searches
a
2 columns where column B has repeated departments, and names the field
of
job
positions in Column C. It creates a names based on column B. In the
code,
it replaces " " with "". How can I also use the code and replace a few
other
characters with "", such as / - ' ? I have tried everything...help

lRow = Cells(Rows.Count, 2).End(xlUp).Row + 1
Set colB = Range(Cells(2, 2), Cells(lRow, 2))
Dept = Trim(Cells(2, 2).Value)
newDept = Replace(Dept, " ", "")

sRow = 2
eRow = 2
For Each chk In colB
If chk.Value < Dept Then
ActiveWorkbook.Names.Add Name:=newDept, _
RefersToR1C1:="=Data!R" & sRow & "C3:R" & eRow & "C3"
Dept = chk.Value
newDept = Replace(Dept, " ", "")
sRow = chk.Row
eRow = chk.Row
Else
eRow = chk.Row
End If
Next chk

End Sub


Thanks!!
Mike

Or even just once:

newDept = Replace(Dept, " ", "")
newDept = Replace(newDept, "/", "")
newDept = Replace(newDept, "-", "")
newDept = Replace(newDept, "'", "")

(and that keeps Dept unchanged from the original value, too.)

or even
newDept _
= Replace(Replace(Replace(Replace(Dept, " ", ""), "/", ""), "-", ""), "'", "")

Personally, I like the multiline version. I find it easier to add and remove
more stuff that way.


Mike Fogleman wrote:

Sorry I didn't capture each new string as characters were removed:

newDept = Replace(Dept, " ", "")
Dept = newDept
newDept = Replace(Dept, "/", "")
Dept = newDept
newDept = Replace(Dept, "-", "")
Dept = newDept
newDept = Replace(Dept, "'", "")

You already had this first line of code to remove spaces, I think it was in
two places in your code. Put the remaining 6 lines right after it, in both
places, just like it looks above.

Mike F
"Mike R." wrote in message
...
Hi,
and where whould I put this code. I have tried to put this in, but it
didn't seem to work. help.
Mike

"Mike Fogleman" wrote:

Make a line of code for each character:
newDept = Replace(Dept, " ", "")
newDept = Replace(Dept, "/", "")
newDept = Replace(Dept, "-", "")
newDept = Replace(Dept, "'", "")

Mike F

"Mike R." wrote in message
...
Hello,
I have already received some great advice on here on how to name a
field,
but I have a question in regards to the code. The following code
searches
a
2 columns where column B has repeated departments, and names the field
of
job
positions in Column C. It creates a names based on column B. In the
code,
it replaces " " with "". How can I also use the code and replace a few
other
characters with "", such as / - ' ? I have tried everything...help

lRow = Cells(Rows.Count, 2).End(xlUp).Row + 1
Set colB = Range(Cells(2, 2), Cells(lRow, 2))
Dept = Trim(Cells(2, 2).Value)
newDept = Replace(Dept, " ", "")

sRow = 2
eRow = 2
For Each chk In colB
If chk.Value < Dept Then
ActiveWorkbook.Names.Add Name:=newDept, _
RefersToR1C1:="=Data!R" & sRow & "C3:R" & eRow & "C3"
Dept = chk.Value
newDept = Replace(Dept, " ", "")
sRow = chk.Row
eRow = chk.Row
Else
eRow = chk.Row
End If
Next chk

End Sub


Thanks!!
Mike




--

Dave Peterson