Macro to find and replace text in a worksheet
 

The following code snippet will replace TextToReplace with
ReplacementText in an Excel worksheet:

Cells.Replace What:=TextToReplace, Replacement:=ReplacementText,
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False

That's easy enough. I'm a little stumped by this next one though...

I want to replace the text in the cell TO THE LEFT of the cell
containing the string TextToReplace with something else, which I'll
call TextDescription.

How do I replace text in the cell NEXT to a cell containing a
particular string?

Travis

Macro to find and replace text in a worksheet
 

try using FIND to locate the text and then OFFSET to replace your desires.
Post your efforts for additional assistance
--
Don Guillett
SalesAid Software

"travis" wrote in message
oups.com...
The following code snippet will replace TextToReplace with
ReplacementText in an Excel worksheet:

Cells.Replace What:=TextToReplace, Replacement:=ReplacementText,
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False

That's easy enough. I'm a little stumped by this next one though...

I want to replace the text in the cell TO THE LEFT of the cell
containing the string TextToReplace with something else, which I'll
call TextDescription.

How do I replace text in the cell NEXT to a cell containing a
particular string?

Travis

Macro to find and replace text in a worksheet
 

On Jun 30, 5:19 am, "Don Guillett" wrote:
try using FIND to locate the text and then OFFSET to replace your desires.
Post your efforts for additional assistance

I've tried that already, numerous attempts lead to various syntax
errors and the like.

Here is the full macro so far...

Dim Downcounter As Integer
Dim NarrativeToReplace As String
Dim ReplacementNarrative As String
Dim CashflowType As String
Dim NumberOfKnownNarratives As Integer

Application.ScreenUpdating = False


Sheets("Bank narratives").Select

' This identifies the last row of data in the Bank narratives
worksheet

NumberOfKnownNarratives = Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByRows).Row

'This loops through each of the known translations and does a replace
all on the Bank Statement sheet.

For Narrative = 1 To NumberOfKnownNarratives
NarrativeToReplace = Range("TopNarrative").Offset(Narrative,
0)
ReplacementNarrative = Range("TopNarrative").Offset(Narrative,
1)
CashflowType = Range("TopNarrative").Offset(Narrative, 2)

Sheets("Bank Statement").Select

Cells.Replace What:=NarrativeToReplace,
Replacement:=ReplacementNarrative, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True,
SearchFormat:=False, _
ReplaceFormat:=False

Next Narrative

What I want to do is replace the cell to the left of the cell where
the bank narrative is with text describing the type of cash flow this
is.

Travis

Macro to find and replace text in a worksheet
 

one thing looks like your missing
Dim Narrative As Long

"travis" wrote:

On Jun 30, 5:19 am, "Don Guillett" wrote:
try using FIND to locate the text and then OFFSET to replace your desires.
Post your efforts for additional assistance

I've tried that already, numerous attempts lead to various syntax
errors and the like.

Here is the full macro so far...

Dim Downcounter As Integer
Dim NarrativeToReplace As String
Dim ReplacementNarrative As String
Dim CashflowType As String
Dim NumberOfKnownNarratives As Integer

Application.ScreenUpdating = False


Sheets("Bank narratives").Select

' This identifies the last row of data in the Bank narratives
worksheet

NumberOfKnownNarratives = Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByRows).Row

'This loops through each of the known translations and does a replace
all on the Bank Statement sheet.

For Narrative = 1 To NumberOfKnownNarratives
NarrativeToReplace = Range("TopNarrative").Offset(Narrative,
0)
ReplacementNarrative = Range("TopNarrative").Offset(Narrative,
1)
CashflowType = Range("TopNarrative").Offset(Narrative, 2)

Sheets("Bank Statement").Select

Cells.Replace What:=NarrativeToReplace,
Replacement:=ReplacementNarrative, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True,
SearchFormat:=False, _
ReplaceFormat:=False

Next Narrative

What I want to do is replace the cell to the left of the cell where
the bank narrative is with text describing the type of cash flow this
is.

Travis

Macro to find and replace text in a worksheet
 

On Jun 30, 9:06 am, Mike wrote:
one thing looks like your missing
Dim Narrative As Long


Thanks, "Narrative" was actually that Downcounter variable until a few
minutes before posting that code... :)

Any suggestions, other than a loop through the Cells collection (which
would be really really slow) on how I could do what I'm trying to do?

When I originally wrote this program I deliberately chose to base it
on a replace all method rather than a loop because its much faster,
and this is a macro which I use quite a lot.

Travis