VBA Index Match & Paste???

[Mr B J B]

Hi All,

I've currently got 2 worksheets in a single workbook which interact with each other. Worksheet1 pulls data from worksheet2 by using an index match, but the problem I have with this is when the data is changed in worksheet 2 this then knocks on to worksheet1. I have the excel formulas working fine but I think as I require the data to become permanent in worksheet1 I will need to change the formulas to VBA code and add a copy and paste command??

Please can someone help me with this as I'm struggling to translate what I require into VBA. The formula that pulls the information through looks like this:

INDEX('Worksheet2'!E:E,MATCH('Worksheet1'!W2,'Worksheet2'!C:C,0))

The cells with data in to be matched in Worksheet1 are W2 to W10000 and in Worksheet2 are C3 to C100.

Many Thanks

 

[DanteAmor]

Put the following macro in a module

Sub Macro10()
    With Range("X2:X" & Range("W" & Rows.Count).End(xlUp).Row)
        .Formula = "=INDEX(Worksheet2!E:E,MATCH(W2,Worksheet2!C:C,0))"
        .Value = .Value
    End With
End Sub

Change the "X" in "X2:X" to the column where you want to put the result.

Execute the macro on the sheet1.

Test and tell me.

 

[Mr B J B]

Thanks Dante, will give this a go and let you know what happens.

 

[DanteAmor]

I await your comments

 

[Mr B J B]

Hi Dante,

I tried as you suggested but I'm getting a Run-time error '1004': Application-defined or object-defined error, which when I run debugger stops at the line starting With. The code I used is:

Sub Macro10()
With Range("AI2:AI" & Range("W" & Rows.Count).End(x1up).Row)
.Formula = "=INDEX(Worksheet2!E:E,MATCH(W2,Worksheet2!C:C,0))"
.Value = .Value
End With
End Sub

 

[DanteAmor]

In this part : End(x1up) you put the number 1, must be a letter l: End(Xlup)

 

[Mr B J B]

Oh, thanks Dante, will try the correct code now and let you know how it goes!

 

[Mr B J B]

Hi Dante,

The code seems to be overwriting the formula also when a value isn't returned.

So I changed the formula to =IFERROR(INDEX('Worksheet2'!E:E,MATCH('Worksheet1'!W2,'Worksheet2'!C:C,0)),"") to try and remove the #N/A but when I do this the code is now running an error on the foumla line:

.Formula = "=IFERROR(INDEX('Worksheet2'!E:E,MATCH('Worksheet1'!W2,'Worksheet2'!C:C,0)),"")"

Please can you advise on where I have gone wrong.

Thanks

Brendan

 

[Fluff]

You will need to "double-up" on the quotes inside the formula, like
"=IFERROR(INDEX('Worksheet2'!E:E,MATCH('Worksheet1'!W2,'Worksheet2'!C:C,0)),"""")"

 

[Mr B J B]

Thanks Fluff, the error has gone now. However when the data is changed in Worksheet 2 and the data that has been matched is no longer there what has been pulled into column AI previously is overwritten.

I have tried using ISBlank in the formula so only the the index match formula applies to blank cells in column AI but this is not working.

Is it possible to do this?