macro index+Match+loop

hail11

Board Regular
Joined
May 3, 2007
Messages
199
dim name as string
for x = 1 to 5
name = Sheets("Main").Range("A"& x)
then i need INDEX([wss.xlsm]name!$AC$63:$AC$74,MATCH(C2,[wss.xlsm]name!$AB$63:$AB$74,FALSE),1)

for each sheet it is an associates name to get there pay and hours

I then need to copy this and paste it into

="Main!A"&x

Please help
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
1. Do you need to place those formulas into "Main!A"&x, or do you just need to place the resulting values from looking up the Pay or Hours?

2. Are you sure the (formula or value) should get pasted into "Main!A"&x ?
It appears that's where your names are currently. Did you mean "Main!B"&x or do you want to overwrite the names now in "Main!A"&x ?
 
Upvote 0
Hi,

Can try formula at Main --> anycell

Code:
=INDEX(INDIRECT("[wss.xlsm]" & A1&"!$AC$63:$AC$74"),MATCH(C2,INDIRECT("[wss.xlsm]" & A1&"!$AB$63:$AB$74"),FALSE),1)
 
Upvote 0
Hi,

Can try formula at Main --> anycell

Code:
=INDEX(INDIRECT("[wss.xlsm]" & A1&"!$AC$63:$AC$74"),MATCH(C2,INDIRECT("[wss.xlsm]" & A1&"!$AB$63:$AB$74"),FALSE),1)

Hi alvinwlh,

INDIRECT will work if the wss.xlsm workbook is open, but not if it is closed.
That was part of the reason I asked if the OP needs the formula to stay in the cell or just the resulting value.

If the formula will be entered by VBA and has to stay in the cell, I think the INDEX-MATCH formula will be better without INDIRECT.
 
Upvote 0
1. Do you need to place those formulas into "Main!A"&x, or do you just need to place the resulting values from looking up the Pay or Hours?

2. Are you sure the (formula or value) should get pasted into "Main!A"&x ?
It appears that's where your names are currently. Did you mean "Main!B"&x or do you want to overwrite the names now in "Main!A"&x ?

1) I need to place the found numbers into "Main!A"&x
2) Sorry I meant "Main!B"&x
 
Upvote 0
Hi alvinwlh,

INDIRECT will work if the wss.xlsm workbook is open, but not if it is closed.
That was part of the reason I asked if the OP needs the formula to stay in the cell or just the resulting value.

If the formula will be entered by VBA and has to stay in the cell, I think the INDEX-MATCH formula will be better without INDIRECT.

The WSS workbook will not always be open I have to open it with macro(this i have coded) then I want to run the code off of the report that transfers into workbook wss.
 
Upvote 0
Since you replied that you only need to place the found number in the Cell and not the formula, you could use INDIRECT in a temporary formula, then replace it with the resulting value.

Try...
Code:
Sub Lookup_PayData()
    Dim name As String
    Dim x As Long
    
    With Sheets("Main").Range("B1:B5")
        .FormulaR1C1 = "=INDEX(INDIRECT(""[wss.xlsm]"" & RC[-1]&""!$AC$63:$AC$74"")," _
        & "MATCH(R2C3,INDIRECT(""[wss.xlsm]"" & RC[-1]&""!$AB$63:$AB$74""),FALSE),1)"
        .Value = .Value
    End With
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top