Hi Ken,

Try Changing:

Set Rng2 = .Cells(Rows.Count, "D").End(xlUp)

To:

Set Rng2 = .Cells(Rows.Count, "C").End(xlUp)


---
Regards,
Norman



"Ken Loomis" wrote in message
...
Thanks for the comments. They are helping me understand better what is
being
references.

I have used the debugger to get a little further in understanding why I
get
the error, but still don't know how to fix it.

Given this piece of code:



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

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



And this data in the "Replace Info" worksheet:

PLACE PL 3
APT # 3
UNIT # 3

Note: Place is in cell A5


I would expect ReplaceArray to contain:

ReplaceArray(1,1) = "PLACE"
ReplaceArray(1,2) = "PL"
ReplaceArray(1,3) = 3
ReplaceArray(2,1) = "APT"
ReplaceArray(2,2) = "#"
ReplaceArray(2,3) = 3
ReplaceArray(3,1) = "UNIT"
ReplaceArray(3,2) = "#"
ReplaceArray(3,3) = 3


But instead, the first row of ReplaceArray contains the data from cells
A1,
B1, C1. Since C1 is empty, ReplaceArray(1,3) = 0.

The InColumn get assigned the value of ReplaceArray(1,3) or zero.

So the statement that causes the 1004 error:

Set rFound = Columns(InColumn).Find(findIt)

is trying to access column zero which will cause the error.

How do I modify the code above so that ReplaceArray is populated with the
data in the cells starting at A5 and continuing thru column D and the last
row on the worksheet?

Ken Loomis

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

Until I added and deleted some worksheets ...

In your code line:

Set rFound = Columns(InColumn).Find(findIt)

Columns is not qualified and, therefore, refes to the active sheet. An
added worksheet becomes the active sheet and Columns may reference the
wrong sheet.

It is much safer to explicitly qualify your ranges:

Set RFound = Sheets("Replace Info").Columns(InColumn).Find(findit)

This way, the correct range will be referenced even if you add or delete
sheets.


---
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