Solved: Error 1004 Bad Range definition?

[jwise]

Hi,

I'm having trouble using ".Find". Since this is difficult to explain, and you need data to see what's going on, I will upload a workbook. It has 3 worksheets. The macro "Test_Tx" demonstrates the problem.

The basic problem is that I must collect data from three sources and combine that data. Unfortunately, there are two sets of "code names" and the "real names" in these worksheets. To complicate this, additional data is periodically added. My solution was to include two worksheets which contain "translate tables" that allow one to properly find the data in the various worksheets.

I cut this problem down as small as I could and created some test data for it. If you look at the code, the comments may not match what the code is doing because it was yanked out of the very large workbook with many more rows of data.

One of these codes is numeric, used as text. This may be part of the problem. Since I have been using these codes a long time in several other applications, I can't figure out why ths is so difficult to find.

To view the error, just run "Test__TX" in the debugger. Basically this takes the contents of the first column, calls the first ".Find" routine to change the code to a full name. Then it takes this full name and calls a very similar routine to get the "profit" from another worksheet.

The error "1004" occurs in routine "Transx" on the statement:

[vba]With ws1.Range(Cells(1, 1), Cells(lastApt, 5))[/vba]

Two of these ".Find" routines are included because both attempt to solve the problem different ways (different Set statements).

I have very similar code working in some other applications.

Thanks for looking at this.

[SamT]

I couldn't get it to work, but I found a missing _
line continuation _
mark in one of the .Finds

SamT

[jwise]

Thanks.

Maybe someone else will understand the Sheet.Range construct.

[p45cal]

The problem with code like:
[vba]With ws1.Range(Cells(1, 1), Cells(lastApt, 5))
[/vba]is that the part:

Cells(1,1)

refers to the active sheet cell.
The same goes for:

Cells(lastApt, 5)

Try:
[vba]With Range(ws1.Cells(1, 1), ws1.Cells(lastApt, 5))
[/vba] I don't think you'll also need a qualifier in front of 'Range', but you can always try it if the above fails:
[vba]With ws1.Range(ws1.Cells(1, 1), ws1.Cells(lastApt, 5))[/vba]

[jwise]

Thanks. I assumed if you named the Sheet, that the Range must be in that sheet. Now I'm thinking that maybe a range can cross worksheet boundaries, and if so, it does make sense that the Cells notation would require clarification as to which worksheet it referenced. Otherwise, I don't know why this notation would be required.

I poorly stated my problem. Thanks for wading through my evasive terminology. I originally had an "On Error" statement which was hiding the fact that it was my "With" statement that was in eror.

Thanks again.