I need to be able to search for some text and replace it. After recording a
macro and editing it I have this

1 Found_name = _
2 Cells.Find(What:="actualAxis2", After:=ActiveCell, LookIn:=xlFormulas, _
3 LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
4 MatchCase:=False).Activate

5 If Found_name = True Then
6 ActiveCell.Replace What:="actualAxis2", Replacement:="EL",
LookAt:=xlPart _
7 , SearchOrder:=xlByRows, MatchCase:=False
8 End If

Lines 2,3,4,6,7 were extracted from the macro I recorded. If the string is
not found, the macro blows up. I added lines 1,5, and 8 to handle the
condition where the find fails. This is not exactly what I had while
working this at work but it should be sufficient to form the question.

How do I write the If statement to bypass the replace statement when the
find is not sucessful?
If you happen to eliminate any items from these statements that are not
needed, that would be cool.

My followup question is:
How can I write a subroutine to the effect:

call replacement( old_text, new_text )

Such that I don't need to copy that code over and over to replace a series
of strings.
I could then write

replacement( "actualAzis1, AZ)
replacement( "actualAzis2, EL)
replacement( "actualAzis3, X)
replacement( "actualAzis4, Y)

and all four replacements would be made. If one string was not found, the
other three replacements would have been completed without error.
--
Thank you,
Bryan Kelly
Time is the medium we use to express out priorities.

try this....

Set rng = Sheets("xxx").Range("xxx").Find(whatyou want)
If rng Is Nothing Then GoTo wherever
abc = rng.row
cells(abc, column) = whatyouwant
wherever:

-----Original Message-----
I need to be able to search for some text and replace
it. After recording a
macro and editing it I have this

1 Found_name = _
2 Cells.Find(What:="actualAxis2", After:=ActiveCell,
LookIn:=xlFormulas, _
3 LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
4 MatchCase:=False).Activate

5 If Found_name = True Then
6 ActiveCell.Replace What:="actualAxis2",
Replacement:="EL",
LookAt:=xlPart _
7 , SearchOrder:=xlByRows, MatchCase:=False
8 End If

Lines 2,3,4,6,7 were extracted from the macro I
recorded. If the string is
not found, the macro blows up. I added lines 1,5, and 8
to handle the
condition where the find fails. This is not exactly what
I had while
working this at work but it should be sufficient to form
the question.

How do I write the If statement to bypass the replace
statement when the
find is not sucessful?
If you happen to eliminate any items from these
statements that are not
needed, that would be cool.

My followup question is:
How can I write a subroutine to the effect:

call replacement( old_text, new_text )

Such that I don't need to copy that code over and over to
replace a series
of strings.
I could then write

replacement( "actualAzis1, AZ)
replacement( "actualAzis2, EL)
replacement( "actualAzis3, X)
replacement( "actualAzis4, Y)

and all four replacements would be made. If one string
was not found, the
other three replacements would have been completed
without error.
--
Thank you,
Bryan Kelly
Time is the medium we use to express out priorities.


.

I am having problems with this.

anonymous wrote:
Set rng = Sheets("xxx").Range("xxx").Find(whatyou want)

I don't know what to put in for the xxx strings so I have progressed to this
point:

Set rng = ActiveSheets.Range("xxx").Find("actualAxis1")

The goal is to search the current active sheet, and the entire top row of
that sheet for the text "actualAxis1"
How do I change my statement to accomplish this?

Bryan


wrote in message
...

try this....

Set rng = Sheets("xxx").Range("xxx").Find(whatyou want)
If rng Is Nothing Then GoTo wherever
abc = rng.row
cells(abc, column) = whatyouwant
wherever:

-----Original Message-----
I need to be able to search for some text and replace
it. After recording a
macro and editing it I have this

1 Found_name = _
2 Cells.Find(What:="actualAxis2", After:=ActiveCell,
LookIn:=xlFormulas, _
3 LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
4 MatchCase:=False).Activate

5 If Found_name = True Then
6 ActiveCell.Replace What:="actualAxis2",
Replacement:="EL",
LookAt:=xlPart _
7 , SearchOrder:=xlByRows, MatchCase:=False
8 End If

Lines 2,3,4,6,7 were extracted from the macro I
recorded. If the string is
not found, the macro blows up. I added lines 1,5, and 8
to handle the
condition where the find fails. This is not exactly what
I had while
working this at work but it should be sufficient to form
the question.

How do I write the If statement to bypass the replace
statement when the
find is not sucessful?
If you happen to eliminate any items from these
statements that are not
needed, that would be cool.

My followup question is:
How can I write a subroutine to the effect:

call replacement( old_text, new_text )

Such that I don't need to copy that code over and over to
replace a series
of strings.
I could then write

replacement( "actualAzis1, AZ)
replacement( "actualAzis2, EL)
replacement( "actualAzis3, X)
replacement( "actualAzis4, Y)

and all four replacements would be made. If one string
was not found, the
other three replacements would have been completed
without error.
--
Thank you,
Bryan Kelly
Time is the medium we use to express out priorities.


.

Hi Bryan,

I think you need to enter the sheet name to search and the range on the sheet
so the code knows where to look..

I am having problems with this.
anonymous wrote:
Set rng = Sheets("xxx").Range("xxx").Find(whatyou want)
I don't know what to put in for the xxx strings so I have progressed to this
point:
Set rng = ActiveSheets.Range("xxx").Find("actualAxis1")

i.e
set rng = sheets("sheet1").Range("A1:A10").Find("actualAxis1 ")

This means it looks in sheet1 (or what ever you sheet is named on the tab)
and then searches in the range A1 through A10 (obviously you will have to
put the correct range in, that is which cells you want it to search)...

I hope this helps...

seeya ste

ste suggested I use:

set rng = sheets("sheet1").Range("A1:A10").Find("actualAxis1 ")

If I use sheets("sheet1") then the sheet where I am searching must be named
"sheet1"
This macro is to be a general purpose macro and the sheet that is being
searched may
be named "sheet6" or "data" or anything else. I don't know much about excel
macros
but I do now there is a way to say search the active sheet regardless of its
name.

If I use Range("A1:A10"), that may work, but what if my next search is in a
sheet that
has the data in cell A43. When manually performing a search using ctl-f,
the user can
select a row, select a column, select an area, or not select anything. In
all of those cases,
the "find" operates and tells me where the data was found.

Shifting perspective a bit:

When I am editing a macro, I put the cursor on "Range" (for example) and
press F1, Excell
gives me a screen full of data but does not explain what that data means.
If I already know,
its a good reminder. If I don't already know, it is little use.

When I look in a book on Excel macros I purchased and look up ranges, it has
ranges embedded
in statements and doesn't tell me its methods, arguments, and return values.
No help.

Here is my question again:

I am trying to say to Excel: In the current sheet search the whole sheet
(regardless of its size)
and tell me where you find the string "actualAxis1"

Then I want to say something like:

cell [ cell_index ] = "AZ"

Or, in that cell where you just found string "actualAxis1", enter the string
"AZ" replacing what was there.
And by the way, if you don't find the string, that's okay. Do not jump into
the debugger, do not
tell me you failed, do not try to replace anything, just go on to the next
statement in the macro.

in pseudo code it can be written as follows:

cell_index = find( "actualAxis1" )
if found
set cell [ cell_index ] = "AZ"
end if

And just in case, in the line
cell_index = find( "actualAxis1" )
The symbols "(" and ")" mean that the data between them is an argument to a
function or method.
And in the line
set cell [ cell_index ] = "AZ"
The symbols "[" and "]" mean that the data between them specify the index of
an array.
An Excel spreadsheet can be considered an array of cells.

Thank you for your time,
Bryan


"ste mac" wrote in message
om...
Hi Bryan,

I think you need to enter the sheet name to search and the range on the
sheet
so the code knows where to look..

I am having problems with this.
anonymous wrote:
Set rng = Sheets("xxx").Range("xxx").Find(whatyou want)
I don't know what to put in for the xxx strings so I have progressed to
this
point:
Set rng = ActiveSheets.Range("xxx").Find("actualAxis1")

i.e
set rng = sheets("sheet1").Range("A1:A10").Find("actualAxis1 ")

This means it looks in sheet1 (or what ever you sheet is named on the tab)
and then searches in the range A1 through A10 (obviously you will have to
put the correct range in, that is which cells you want it to search)...

I hope this helps...

seeya ste

I don't think anyone cares, but here goes.
After about 5 hours of bouncing back and forth between my book, Excel help,
and various message on this forum,
I finally arrived at the following.

Sub changeColumnNames()
' Find a value in a cell and replace it.
' Note that this does no cell selections, it does not
' change the currently selected cell.
'
find_target = "actualAxis1"
new_value = "AZ"
'
Set rng = ActiveSheet.Range("1:1").Find(find_target)

If Not (rng Is Nothing) Then
rng.Formula = new_value
End If

End Sub

There are really only two working lines in he
Set rng = ActiveSheet.Range("1:1").Find(find_target)
rng.Formula = new_value

The remainder just generalizes the function using variables, and protects
from the error of string not found.

Thanks for your time,
Bryan


"Bryan Kelly" wrote in message
.. .
I need to be able to search for some text and replace it. After recording
a
macro and editing it I have this

1 Found_name = _
2 Cells.Find(What:="actualAxis2", After:=ActiveCell, LookIn:=xlFormulas,
_
3 LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
4 MatchCase:=False).Activate

5 If Found_name = True Then
6 ActiveCell.Replace What:="actualAxis2", Replacement:="EL",
LookAt:=xlPart _
7 , SearchOrder:=xlByRows, MatchCase:=False
8 End If

Lines 2,3,4,6,7 were extracted from the macro I recorded. If the string
is
not found, the macro blows up. I added lines 1,5, and 8 to handle the
condition where the find fails. This is not exactly what I had while
working this at work but it should be sufficient to form the question.

How do I write the If statement to bypass the replace statement when the
find is not sucessful?
If you happen to eliminate any items from these statements that are not
needed, that would be cool.

My followup question is:
How can I write a subroutine to the effect:

call replacement( old_text, new_text )

Such that I don't need to copy that code over and over to replace a series
of strings.
I could then write

replacement( "actualAzis1, AZ)
replacement( "actualAzis2, EL)
replacement( "actualAzis3, X)
replacement( "actualAzis4, Y)

and all four replacements would be made. If one string was not found, the
other three replacements would have been completed without error.
--
Thank you,
Bryan Kelly
Time is the medium we use to express out priorities.