# VLOOKUP VBA looping multiple worksheets

#### financehelp_excel

##### New Member
Thanks in advance for solving this.

I've created a macro but it's returning an error. I have a two column list in my first sheet and I want VLOOKUP to go down the list. So for cell C1 sheet 2, I want it to lookup Cell A2 in sheet named "First". For cell C1 in sheet 3, I want it to lookup Cell A3 in sheet named "First".

Here's what I have so far:

'Sub test()
Dim First As Integer, Last As Integer, i As Integer
First = Sheets("First").Index
Last = Sheets("Last").Index
For i = First + 1 To Last - 1
With Sheets(i)
'Range("C1").Select
'Range("C1") = Application.WorksheetFunction.VLookup(Sheets("First").Range("A1+1"), Sheets("First").Range("\$A\$1:\$B504"), 2, False)
End With
Next i
End Sub

### Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Code:
``````Sub test()
Dim i%, f As Worksheet
Set f = Sheets("First")
For i = Sheets("First").Index + 1 To Sheets("Last").Index - 1
Sheets(i).Range("C1") = Application.WorksheetFunction.VLookup _
(f.Range("A" & i), f.Range("\$A\$1:\$B504"), 2, False)
Next
End Sub``````

Thank you so much! You are awesome!!!

One quick question, is there a way to name each tab the contents of what is in each C1?

Like this:

Code:
``````Sub Renaming()
Dim sh As Worksheet
For Each sh In ThisWorkbook.Worksheets
If Len(sh.Range("c1")) > 0 Then sh.Name = sh.Range("c1")
Next
End Sub``````

Last edited:
Brilliant!!! You are my savior! Thank you!!

You are welcome

Replies
4
Views
111
Replies
10
Views
452
Replies
12
Views
183
Replies
5
Views
215
Replies
7
Views
240

1,203,727
Messages
6,056,966
Members
444,899
Latest member
Excel_Temp

### 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.

### Which adblocker are you using?

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

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