PDA

View Full Version : [SOLVED] Index/Match VBA equivalent to append data



davew
10-03-2012, 10:09 AM
Hi, hope one of you guys can help please,

I’m trying to append some values from one table into another table. Normally I’d do this using Index/Match pasted into every cell where I’m trying to append the data to. Due to the larger than normal quantity of data this would mean I’d need a lot of Index/Match calc’s and it gets a bit cumbersome. Is there a workable Index/Match type function in VBA?

I’ve attached a simple example to illustrate what I’m trying to achieve. The first sheet has the data I’m trying to append and the second sheet has the table I’m trying to append it to. If you’re able to structure your response around this then that would be great otherwise any help would be appreciated anyway.

Hope you can help.

Thanks

Davew

snb
10-04-2012, 06:27 AM
Sub snb()
sn = [populatesheet!A3:A14&populatesheet!B3:B14&populatesheet!C3:C14]
sp = [basevalues!A3:A5&basevalues!B3:B5&basevalues!C3:C5]
st = [populatesheet!A3:K14]
sr = [basevalues!A3:K5]
For j = 1 To UBound(st)
If Not IsError(Application.Match(sn(j, 1), sp, 0)) Then
For jj = 3 To UBound(st, 2)
st(j, jj) = sr(Application.Match(sn(j, 1), sp, 0), jj)
Next
End If
Next
[populatesheet!A3:K14] = st
End Sub

davew
10-04-2012, 02:13 PM
This works. Really impressed with this. Thanks very much.

snb
10-05-2012, 01:01 PM
There's a simpler method to do this:



sub snb
sz = [iferror(match(populatesheet!A3:A14&populatesheet!B3:B14&populatesheet!C3:C14,basevalues!A3:A5&basevalues!B3:B5&basevalues!C3:C5,0),)]
For j = 1 To UBound(sz)
If Val(sz(j, 1)) > 0 Then Sheets("Populatesheet").Cells(2 + j, 4).Resize(, 8) = Application.Index([basevalues!D3:K5].Value, sz(j, 1))
Next
End sub