jwise
02-17-2010, 02:18 PM
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:
With ws1.Range(Cells(1, 1), Cells(lastApt, 5))
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.
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:
With ws1.Range(Cells(1, 1), Cells(lastApt, 5))
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.