match and index macro question

[nardagus]

Hello guys and girls,

I'd like to ask you a question about macro I made. I'm totally newbie in writing macros so forgive me taking your time with such a simple thing.

Sorry for not using HTMLMaker but I get an error about "type mismatch" in excel version section (I'm using E2010).

Any way.
I have two sheets. One contains bookkeeping accounts from my accounting system (except those accounts in this sheet there are transcactions as well. Around 50k rows):

Name of the sheet: sheet3 (columns from A through C)[TABLE="class: grid, width: 320"]
<tbody>[TR]
[TD]My account[/TD]
[TD]Foreing Accout[/TD]
[TD]Description[/TD]
[/TR]
[TR]
[TD]702-2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]752-2-1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]501-5-15[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]501-5-21[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]501-5-22[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]201-2-1-900[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]201-2-1-900[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]201-2-1-900[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]501-5-15[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]501-5-15[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]752-2-1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]752-2-1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

And another table also with bookkeeping accounts structure exported from out accounting system and also with account structure from foreign accounting system:

Name of the sheet: sheet2 (columns from A through C)
[TABLE="class: grid, width: 388"]
<tbody>[TR]
[TD]My account[/TD]
[TD]Foreign Accout[/TD]
[TD]Description from foreign system[/TD]
[/TR]
[TR]
[TD]702-2[/TD]
[TD="align: right"]1965[/TD]
[TD]CICWAM[/TD]
[/TR]
[TR]
[TD]752-2-1[/TD]
[TD="align: right"]3221[/TD]
[TD]AICWAR[/TD]
[/TR]
[TR]
[TD]501-5-15[/TD]
[TD="align: right"]2110[/TD]
[TD]AICWAR[/TD]
[/TR]
[TR]
[TD]501-5-21[/TD]
[TD="align: right"]2315[/TD]
[TD]AICWAR[/TD]
[/TR]
[TR]
[TD]501-5-22[/TD]
[TD="align: right"]2960[/TD]
[TD]AICWAR[/TD]
[/TR]
[TR]
[TD]201-2-1-[/TD]
[TD="align: right"]1596[/TD]
[TD]Some other value[/TD]
[/TR]
</tbody>[/TABLE]

Now. My task was to find an account from a sheet3 in a sheet2 and copy values from columns B and C to sheet3. The trick is that some cell values from sheet3, column A had exact match in sheet2. However some didn't (account no: 201-2-1-900). The only solution I came up with was to match cell value from sheet3 and if it wasn't found then cut out the last char from it (i.e. 201-2-1-90). Macro was supposed to cut chars until first match was found (in this case it would be: 201-2-1-). Than "index()" formula would return values from sheet2 columns B and C. Only first match was important, that's why I put IF function to break FOR loop.

Here is the code:

Sub Accounts()
Dim i As Integer
Dim row_no As Integer
Dim j As Integer
Dim dl As Integer


For i = 2 To 13
dl = Len(Worksheets("sheet3").Range("A" & i))

For j = dl To 1 Step -1
On Error Resume Next
row_no = WorksheetFunction.Match(Mid(Worksheets("sheet3").Range("A" & i), 1, j), Worksheets("sheet2").Range("A2:A7"), 0)


If row_no > 0 Then GoTo Wynik


Next j
Wynik:
Worksheets("sheet3").Range("B" & i) = WorksheetFunction.Index(Worksheets("sheet2").Range("B2:B7"), row_no)
Worksheets("sheet3").Range("C" & i) = WorksheetFunction.Index(Worksheets("sheet2").Range("C2:C7"), row_no)


row_no = 0


Next i


End Sub
-------------------

Macro did it's job but in my full newbiesness I have no idea why it worked only when I added:

row_no = 0

Without it, it looped on a second row so I had value from second row in next 50k rows.
Could you please explain it to me? Maybe there is an error in the code (I wouldn't be surprised).

Regards
Rafal

 

[pjmorris]

Hi Nardagus

An alternative approach, which uses two equations may help and avoids using a macro:

In Cell D2 of Sheet 2 insert this equation =VLOOKUP(A2&"*",Sheet3!$A$2:$A$1000,1,FALSE) and copy it down the column as far as necesary, change the $A$1000 entry to suit the number of rows you have. This will extract the closest match from Sheet 3 against each row in Sheet 2 and overcomes your truncated entries issue (the A2 & "*" achieves this).

Then, in cell B2 of Sheet 3 enter =INDEX(Sheet2!$B$2:$D$7,MATCH(Sheet3!$A2,Sheet2!$D$2:$D$7,0),0) and copy it across and down as necessary, again make sure the row numbers are matched to the data.

Hope this helps.