Index Array Match - 1004 Error

What's the declaration type for the File Name?

Sorry, should have given you the full macro . See below!

Please Login or Register  to view this content.

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here



(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

Hi,

Change the references in the formula from

cFileNametmb05

to

cFileNametm05

Incidentally although the variables are being declared by default, it's always preferable to explicitly declare the type and configure the VBE to always require variable declaration with an Option Explicit instruction above the code. Then use code like.

Dim cFileNametm05 As String

It's too easy to mistype a variable name and without an explicit declaration the mistyped name will automatically become a valid variable.

What do you see if you step through the macro and then hover over the cfilenametm05 variable when you hit the error message?

Upload the workbook so that we may see the problem in context.

Hi,

Why are you using the .FormulaArray instruction when the formula seems to be a straightforward simple indexing formula? Try a plain

Formula:

Please Login or Register  to view this content.

On a more general point, and I admit I don't understand the minute detail of what you're doing but I question the necessity for all the looping code which is never very efficient. If you're grabbing data from another workbook, which seems the essence of the task, then a simple Data Advanced Filter would seem the obvious way to go and perform the task in a fraction of a second .

Neither does all the .Select stuff help matters. This is rarely necessary and certainly not the case here. Just address the object directly rather than selecting it.

Sorry but it's not clear to me exactly what you want.

Are you able to manually add a set of results that you want to see and add some notes explaining how you produced them. We can then work out the most optimum way of getting those results.

I still don't see why you need an array formula when you're using an Index function to return a single value. Is there a misunderstanding on the term an 'array formula'
i.e. with your formula

Formula:

Please Login or Register  to view this content.

in a cell then no doubt it evaluates correctly.
But what happens if you convert it to an ARRAY formula by entering it with Ctrl-Shift-Enter so that Excel adds the {} parentheses around it. Does it return an error?

Hi,

I'm not entirely sure I still understand the precise requirement.

You've listed 000026343 in R10:C59 of the TR2 report and in R10:C1 000000999 which happens to be the first ID in R5:C14 of the HR Planning Report which happens to be grouped with others covered by 000026343 in R5:C5.

Is this the link and in the real world would you be adding the other 12 IDs covered by 000026343 in Rows 11:22 Column 1 of the TR2 sheet.

One thing that does concern me is the merged cells in the HR sheet. These play havoc with any serious analysis and most of us avoid them like the plague since they just cause too many problems and make life more complicated than necessary. I don't know how the HR report is produced but I'm guessing it comes from some other back office system and it may be worth considering whether this could provide a more regular, and for Excel usable' layout of data.

The overall requirement seems to revolve around either many to one matches or one to many matches to produce the successor type analysis that I think you're driving at. However without seeing a detailed spec. of the overall requirement I am still struggling. Something tells me that with different layouts this exercise might be simplified somewhat.

Hi,

I'm not entirely sure I still understand the precise requirement.

You've listed 000026343 in R10:C59 of the TR2 report and in R10:C1 000000999 which happens to be the first ID in R5:C14 of the HR Planning Report which happens to be grouped with others covered by 000026343 in R5:C5.

Correct. So employee 000000999 is one of 13 employees who possibly follow-up employee 000026343.

Is this the link and in the real world would you be adding the other 12 IDs covered by 000026343 in Rows 11:22 Column 1 of the TR2 sheet.

In the real world these other 12 ID's will show up anyways in column 1 of the TR2 Sheet. In the actual file all employee ID's for all employees in the company will show up in column 1 of the TR2 sheet. Thus, employee 000041600 (R6:C14 of the HR Planning Report) will be somewhere in column 1 of the TR2 sheet (not necessarily row R11:C1).

One thing that does concern me is the merged cells in the HR sheet. These play havoc with any serious analysis and most of us avoid them like the plague since they just cause too many problems and make life more complicated than necessary. I don't know how the HR report is produced but I'm guessing it comes from some other back office system and it may be worth considering whether this could provide a more regular, and for Excel usable' layout of data.

I could not agree with you more on the merged cells . However, as you mentioned correctly, this is produced by another system and is what it is. I use a work-around via a macro by first unmerging all cells in the HR Planning Report. This causes R5:C5 to keep the original value 000026343. The other cells then turn empty and get a unique value by adding an A. See attached file.
In column 29 of the TR2 file, I use real employee ID's. In column 32, I use the concatenation of the Employee+A. In column 35, that concatenation +A (which results into Employee ID+AA).

The overall requirement seems to revolve around either many to one matches or one to many matches to produce the successor type analysis that I think you're driving at. However without seeing a detailed spec. of the overall requirement I am still struggling. Something tells me that with different layouts this exercise might be simplified somewhat.

You are right. As you can see in the updated attached file, each cell in column 5 has a unique value (no value returns twice). This way I can easily identify all possible successors for all employees mentioned in column 1 in the TR2 file. On the other hand, in column 14 of the HR Planning file, a lot of values show up twice or more.

In the particular example of 000000999, R10:C59 in the TR2 file should show 000026343 (Index/Match in the HRPlanning file between R15:C14 and R5:C5:. R10:C70 (in TR2 file) should show 000043547A (Index/Match between R800:C14 and R800:C5 (in the HR Planning File)).

When this is realized, I just deleted the A's in columns 61, 70, et cetera.

I think we are getting there slowly, but steady. But I am more than happy to explain it even better if needed! Once again, thanks a ton for all your effort so far!