Excel VBA, Vlookup to another sheet in same work book

[Patty3-4]

Good afternoon,

I'm attempting to use a VLookup macro to find data in a different sheet, within the same workbook. Oddly enough it was working until it didn't find two matches, then it stopped working. I've tried several ways to edit it - initially I had the source data in a different workbook then moved it to the same workbook, along with other attempts. Still, I can't get it to work. Any help would be appreciated.

The intended use is = that i enter the initial id number in column A and when i run the macro, all of the other fields autofill but right now nothing happens. This is my code:

i'm using Excel Office16 Windows

Sub Lookup()

Dim lRow As Integer
Dim wb As Workbook
Set wb = ActiveWorkbook
ThisWorkbook.Activate
Dim i As Integer

lRow = Cells(Rows.Count, 2).End(xlUp).Row

For i = 2 To lRow
If Len(Cells(i, 1)) < 5 Then
Range("a" & i).Value = Format(Date, "mm/dd/yyyy")
End If

If Not IsEmpty(Range("b" & i).Value) Then
Range("b" & i).Value = WorksheetFunction.VLookup(Range("a" & i).Value, Sheets("Phone List").Range("A2:G12"), 2, 0)
Range("c" & i).Value = WorksheetFunction.VLookup(Range("a" & i).Value, Sheets("Phone List").Range("A2:G12"), 3, 0)
Range("d" & i).Value = WorksheetFunction.VLookup(Range("a" & i).Value, Sheets("Phone List").Range("A2:G12"), 4, 0)
Range("e" & i).Value = WorksheetFunction.VLookup(Range("a" & i).Value, Sheets("Phone List").Range("A2:G12"), 5, 0)
Range("f" & i).Value = WorksheetFunction.VLookup(Range("a" & i).Value, Sheets("Phone List").Range("A2:G12"), 6, 0)
Range("g" & i).Value = WorksheetFunction.VLookup(Range("a" & i).Value, Sheets("Phone List").Range("A2:G12"), 7, 0)

End If
Next i

End Sub

 

[myall_blues]

Is there a specific reason you're using a macro to do this rather than simply inserting the VLOOKUP formulas in Sheet1?

 

[StephenCrump]

Your .VLookup code lines will either return a value or cause a run-time error. So if "nothing" is happening, this suggests that there is no looping going on.

What is the value of lRow? Should it be looking at column B?

You also have an unqualified reference to Cells(Rows.Count, 2). Is the ActiveSheet the right sheet to be looking at?

 

[Patty3-4]

Is there a specific reason you're using a macro to do this rather than simply inserting the VLOOKUP formulas in Sheeti

Is there a specific reason you're using a macro to do this rather than simply inserting the VLOOKUP formulas in Sheet1?

I did have vlookup formulas in the columns initially but it increased the size of the file and, since the data entered will grow with time, I didn't want to have to drag them down as time went on. Also, I'll be sharing this workbook with someone who is not very familiar with Excel and tends to break/delete formulas/functions. Thought it best to create macros and then try to protect them from being accessed. I haven't researched how to protect them yet but was planning to before releasing the file.

 

[Patty3-4]

Your .VLookup code lines will either return a value or cause a run-time error. So if "nothing" is happening, this suggests that there is no looping going on.

What is the value of lRow? Should it be looking at column B?

You also have an unqualified reference to Cells(Rows.Count, 2). Is the ActiveSheet the right sheet to be looking at?

My apologies, you're correct. The "nothing happens" means I get a Run=time error '1004' Application-defined or object-defined error.
I added the lRow in because prior to adding this, and many other macros to try to automate our tasks, we already had 587 rows of data. I didn't want the macros to re-do anything prior to any new entries. I'm including below the original which originally looks at the source in another workbook entirely and the entry is made into column B. It was working fine a couple of days ago when I entered 8 ID's; it found 6 of them and 2 were not found. This is a spreadsheet of employees and it looks at the SourceData/Phone List to pull the rest of their info. The 2 not found had recently terminated employment. It stopped working completely after that (I get the error code). Also, the SourceData file would open and close but since it stopped working, the SourceData file stays open. Since I wanted to have the SourceData in the same workbook in the end, I tried to direct it to PhoneList. Same error.

I might've missed modifying the code when I made the sample file pic to upload here. I'm very new to vba/macros - the other user I'll be sharing this file with routinely deletes formulas, which is why i went this route; among other reasons. This is the original vba/macro

Sub Lookup()

Dim lRow As Integer
Dim wb As Workbook
Workbooks.Open ("C:\SSO\SourceData.xlsx")
Set wb = ActiveWorkbook
ThisWorkbook.Activate
Dim i As Integer

lRow = Cells(Rows.Count, 2).End(xlUp).Row

For i = 588 To lRow
If Len(Cells(i, 1)) < 5 Then
Range("a" & i).Value = Format(Date, "mm/dd/yyyy")
End If

Range("d" & i).Value = WorksheetFunction.VLookup(Range("b" & i).Value, wb.Sheets(1).Range("A2:AJ1697"), 7, 0)
Range("e" & i).Value = WorksheetFunction.VLookup(Range("b" & i).Value, wb.Sheets(1).Range("A2:AJ1697"), 3, 0)
Range("f" & i).Value = WorksheetFunction.VLookup(Range("b" & i).Value, wb.Sheets(1).Range("A2:AJ1697"), 21, 0)
Range("h" & i).Value = WorksheetFunction.VLookup(Range("b" & i).Value, wb.Sheets(1).Range("A2:AJ1697"), 4, 0)
Range("k" & i).Value = WorksheetFunction.VLookup(Range("b" & i).Value, wb.Sheets(1).Range("A2:AJ1697"), 32, 0)

Next i

Workbooks("C:\SSO\SourceData.xlsx").Close SaveChanges:=False

End Sub

 

[Patty3-4]

ok, I was able to get it to work by dimming and setting the range. Thank you both for taking the time to look at my post and asking questions.

Sub Lookup()

Dim lRow As Long
Dim wb As Workbook
Dim lookRange As Range
Set lookRange = Sheets("Phone_List").Range("A2:AJ1697")

ThisWorkbook.Activate

Dim i As Integer

lRow = Cells(Rows.Count, 2).End(xlUp).Row


For i = 595 To lRow
If Len(Cells(i, 1)) < 5 Then
Range("a" & i).Value = Format(Date, "mm/dd/yyyy")
End If


If Not IsEmpty(Range("b" & i).Value) Then
Range("d" & i).Value = WorksheetFunction.VLookup(Range("b" & i).Value, lookRange, 7, 0)
Range("e" & i).Value = WorksheetFunction.VLookup(Range("b" & i).Value, lookRange, 3, 0)
Range("f" & i).Value = WorksheetFunction.VLookup(Range("b" & i).Value, lookRange, 21, 0)
Range("h" & i).Value = WorksheetFunction.VLookup(Range("b" & i).Value, lookRange, 4, 0)
Range("k" & i).Value = WorksheetFunction.VLookup(Range("b" & i).Value, lookRange, 32, 0)

End If
Next i

End Sub

 

[StephenCrump]

ok, I was able to get it to work ...

Excellent, well done. I'm glad we could help.