Hi Ken,

It is not clear (to me!) where you say your error is occurring.

Also, why do you set the Rng1 and Rng2 variables to ranges on Worksheets(2)
and then immediately reassign these variables to ranges on the Replace Info
sheet.

Perhaps you could post the entire relevant code portion?

---
Regards,
Norman



"Ken Loomis" wrote in message
...
This piece of code worked just fine:

Dim Rng1 As Range, Rng2 As Range
Dim MyRng As Range

With Worksheets(2)
Set Rng1 = .Range("A5") '<<=== Given Start cell
Set Rng2 = .Cells(Rows.count, "D").End(xlUp)
End With

With Sheets("Replace Info")
Set Rng1 = .Range("A5") '<<=== Given Start cell
Set Rng2 = .Cells(Rows.count, "D").End(xlUp)
End With

Set MyRng = Range(Rng1, Rng2)
ReplaceArray = MyRng

searches = UBound(ReplaceArray, 1)

For srchCnt = 1 To searches
findIt = ReplaceArray(srchCnt, 1)
ReplaceWith = ReplaceArray(srchCnt, 2)
InColumn = ReplaceArray(srchCnt, 3)
Set rFound = Columns(InColumn).Find(findIt)

- code snipped here <-


Until I added and deleted some worksheets. Now I am getting a run-time
error 1004 at the last line of that code and Rng1 = the value from cell
A5.

So I tried this:

With Worksheets("Replace Info")
Set Rng1 = .Range("A5") '<<=== Given Start cell
Set Rng2 = .Cells(Rows.count, "D").End(xlUp)
End With



And this,

With Sheets("Replace Info")
Set Rng1 = .Range("A5") '<<=== Given Start cell
Set Rng2 = .Cells(Rows.count, "D").End(xlUp)
End With


But neither of those work.

Can someone tell me how to fix this? And, if possible tell me what I am
not getting about this? Maybe suggest some online reading. I have already
ordered 2 books that were earlier suggested, but I need to get this
project done before they get here.

Thanks for any help and insights.

Ken Loomis

Sorry. I just tried to copy and paste too many segments.

I get the run-time error (1004) at this line:
Set rFound = Columns(InColumn).Find(findIt)

Instead of getting a range in Rng1, I get the value of the cell at A5 on the
"Replace Info" sheet.

I modified this sub from the code I got he

http://archive.baarns.com/excel/faq/xd_rang1.asp#5

If I really understood it, I might be able to fix what I broke.

What I need to do is replace all occurances of a string in a column with its
replacement string.

The array from rhe "Replace Info" sheet contains the string to serach for,
the the string to replace it with and the column to look in.

For example, "STREET", "ST" and 3


Here is the complete sub:

Sub ReplaceAllStuffInNAHBO()
Dim ReplaceArray As Variant
Dim rFound As Range
Dim szFirst As String
Dim iCount As Integer
Dim rngTarget As Range
Dim searches As Integer
Dim srchCnt As Integer
Dim n As Integer
Dim m As Integer
Dim findIt As String
Dim ReplaceWith As String
Dim InColumn As Integer
Dim Rng1 As Range, Rng2 As Range
Dim MyRng As Range

With Sheets(2)
Set Rng1 = .Range("A5") '<<=== Given Start cell
Set Rng2 = .Cells(Rows.count, "D").End(xlUp)
End With

Set MyRng = Range(Rng1, Rng2)
ReplaceArray = MyRng
searches = UBound(ReplaceArray, 1)
For srchCnt = 1 To searches
findIt = ReplaceArray(srchCnt, 1)
ReplaceWith = ReplaceArray(srchCnt, 2)
InColumn = ReplaceArray(srchCnt, 3)
Set rFound = Columns(InColumn).Find(findIt)
iCount = 0
Do While Not rFound Is Nothing
''' Store address of first occurrence
If szFirst = "" Then
szFirst = rFound.Address
ElseIf rFound.Address = szFirst Then
Exit Do ''' If we have looped around, quit
End If
rFound.Value = Application.Substitute(rFound.Value, _
findIt, ReplaceWith)
iCount = iCount + 1
Set rFound = Columns(InColumn).Cells.FindNext(rFound)
Loop
If ShowMsgs Then
MsgBox "Replaced " & iCount & " occurrences of " & findIt & "
with " & ReplaceWith
End If
Next srchCnt
End Sub



"Norman Jones" wrote in message
...
Hi Ken,

It is not clear (to me!) where you say your error is occurring.

Also, why do you set the Rng1 and Rng2 variables to ranges on
Worksheets(2) and then immediately reassign these variables to ranges on
the Replace Info sheet.

Perhaps you could post the entire relevant code portion?

---
Regards,
Norman



"Ken Loomis" wrote in message
...
This piece of code worked just fine:

Dim Rng1 As Range, Rng2 As Range
Dim MyRng As Range

With Worksheets(2)
Set Rng1 = .Range("A5") '<<=== Given Start cell
Set Rng2 = .Cells(Rows.count, "D").End(xlUp)
End With

With Sheets("Replace Info")
Set Rng1 = .Range("A5") '<<=== Given Start cell
Set Rng2 = .Cells(Rows.count, "D").End(xlUp)
End With

Set MyRng = Range(Rng1, Rng2)
ReplaceArray = MyRng

searches = UBound(ReplaceArray, 1)

For srchCnt = 1 To searches
findIt = ReplaceArray(srchCnt, 1)
ReplaceWith = ReplaceArray(srchCnt, 2)
InColumn = ReplaceArray(srchCnt, 3)
Set rFound = Columns(InColumn).Find(findIt)

- code snipped here <-


Until I added and deleted some worksheets. Now I am getting a run-time
error 1004 at the last line of that code and Rng1 = the value from cell
A5.

So I tried this:

With Worksheets("Replace Info")
Set Rng1 = .Range("A5") '<<=== Given Start cell
Set Rng2 = .Cells(Rows.count, "D").End(xlUp)
End With



And this,

With Sheets("Replace Info")
Set Rng1 = .Range("A5") '<<=== Given Start cell
Set Rng2 = .Cells(Rows.count, "D").End(xlUp)
End With


But neither of those work.

Can someone tell me how to fix this? And, if possible tell me what I am
not getting about this? Maybe suggest some online reading. I have already
ordered 2 books that were earlier suggested, but I need to get this
project done before they get here.

Thanks for any help and insights.

Ken Loomis

Hi Ken,

In addition to my adjacent post, depending on the position of added/deleted
sheets, the sheet referenced by :

Sheets(2)

will change.

To demonstrate, from the intermediate window:

? Sheets(2).Name
Sheet2

Sheets.Add Sheets befo Sheets("Sheet2")

? Sheets(2).Name
Sheet4

So, use the Sheets name instead of its index and qualify the Columns range
by prefacing it with the appropriate sheet.

---
Regards,
Norman



"Ken Loomis" wrote in message
...
Sorry. I just tried to copy and paste too many segments.

I get the run-time error (1004) at this line:
Set rFound = Columns(InColumn).Find(findIt)

Instead of getting a range in Rng1, I get the value of the cell at A5 on
the "Replace Info" sheet.

I modified this sub from the code I got he

http://archive.baarns.com/excel/faq/xd_rang1.asp#5

If I really understood it, I might be able to fix what I broke.

What I need to do is replace all occurances of a string in a column with
its replacement string.

The array from rhe "Replace Info" sheet contains the string to serach for,
the the string to replace it with and the column to look in.

For example, "STREET", "ST" and 3


Here is the complete sub:

Sub ReplaceAllStuffInNAHBO()
Dim ReplaceArray As Variant
Dim rFound As Range
Dim szFirst As String
Dim iCount As Integer
Dim rngTarget As Range
Dim searches As Integer
Dim srchCnt As Integer
Dim n As Integer
Dim m As Integer
Dim findIt As String
Dim ReplaceWith As String
Dim InColumn As Integer
Dim Rng1 As Range, Rng2 As Range
Dim MyRng As Range

With Sheets(2)
Set Rng1 = .Range("A5") '<<=== Given Start cell
Set Rng2 = .Cells(Rows.count, "D").End(xlUp)
End With

Set MyRng = Range(Rng1, Rng2)
ReplaceArray = MyRng
searches = UBound(ReplaceArray, 1)
For srchCnt = 1 To searches
findIt = ReplaceArray(srchCnt, 1)
ReplaceWith = ReplaceArray(srchCnt, 2)
InColumn = ReplaceArray(srchCnt, 3)
Set rFound = Columns(InColumn).Find(findIt)
iCount = 0
Do While Not rFound Is Nothing
''' Store address of first occurrence
If szFirst = "" Then
szFirst = rFound.Address
ElseIf rFound.Address = szFirst Then
Exit Do ''' If we have looped around, quit
End If
rFound.Value = Application.Substitute(rFound.Value, _
findIt, ReplaceWith)
iCount = iCount + 1
Set rFound = Columns(InColumn).Cells.FindNext(rFound)
Loop
If ShowMsgs Then
MsgBox "Replaced " & iCount & " occurrences of " & findIt & "
with " & ReplaceWith
End If
Next srchCnt
End Sub



"Norman Jones" wrote in message
...
Hi Ken,

It is not clear (to me!) where you say your error is occurring.

Also, why do you set the Rng1 and Rng2 variables to ranges on
Worksheets(2) and then immediately reassign these variables to ranges on
the Replace Info sheet.

Perhaps you could post the entire relevant code portion?

---
Regards,
Norman



"Ken Loomis" wrote in message
...
This piece of code worked just fine:

Dim Rng1 As Range, Rng2 As Range
Dim MyRng As Range

With Worksheets(2)
Set Rng1 = .Range("A5") '<<=== Given Start cell
Set Rng2 = .Cells(Rows.count, "D").End(xlUp)
End With

With Sheets("Replace Info")
Set Rng1 = .Range("A5") '<<=== Given Start cell
Set Rng2 = .Cells(Rows.count, "D").End(xlUp)
End With

Set MyRng = Range(Rng1, Rng2)
ReplaceArray = MyRng

searches = UBound(ReplaceArray, 1)

For srchCnt = 1 To searches
findIt = ReplaceArray(srchCnt, 1)
ReplaceWith = ReplaceArray(srchCnt, 2)
InColumn = ReplaceArray(srchCnt, 3)
Set rFound = Columns(InColumn).Find(findIt)

- code snipped here <-


Until I added and deleted some worksheets. Now I am getting a run-time
error 1004 at the last line of that code and Rng1 = the value from cell
A5.

So I tried this:

With Worksheets("Replace Info")
Set Rng1 = .Range("A5") '<<=== Given Start cell
Set Rng2 = .Cells(Rows.count, "D").End(xlUp)
End With



And this,

With Sheets("Replace Info")
Set Rng1 = .Range("A5") '<<=== Given Start cell
Set Rng2 = .Cells(Rows.count, "D").End(xlUp)
End With


But neither of those work.

Can someone tell me how to fix this? And, if possible tell me what I am
not getting about this? Maybe suggest some online reading. I have
already ordered 2 books that were earlier suggested, but I need to get
this project done before they get here.

Thanks for any help and insights.

Ken Loomis