VBA: Vlookup info for entire column?

[HRIS]

I have a ton to learn about VBA and am struggling to figure out how I match information up from the column of another worksheet to the column in a newly added worksheet. I am trying to do this in a macro that sets up the new worksheet. Here is the macro as it is now:

Dim ds As Range
Set ds = ActiveSheet.Range("A1").CurrentRegion
With ActiveSheet
    Range("E:P,R:U,W:X,Z:Z,AE:AG,AI:IV").Select
    Selection.Delete Shift:=xlToLeft
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.AutoFilter
    Cells.Select
    Cells.EntireColumn.AutoFit
 
Application.EnableEvents = True
 
    Selection.AutoFilter Field:=5, Criteria1:="FTNB"
    Set ds = ds.Offset(1, 0).Resize(ds.Rows.Count - 1)
    Set ds = ds.SpecialCells(xlCellTypeVisible)
    ds.EntireRow.Delete
 
    Set ds = ActiveSheet.Range("A1").CurrentRegion
 
    Selection.AutoFilter Field:=5, Criteria1:="PTNB"
    Set ds = ds.Offset(1, 0).Resize(ds.Rows.Count - 1)
    Set ds = ds.SpecialCells(xlCellTypeVisible)
    ds.EntireRow.Delete
 
    Selection.AutoFilter Field:=5
 
    Range("M1") = "DATE SEEN"
    Range("M1").Font.Bold = True
 
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.AutoFilter
    Selection.AutoFilter
    Cells.EntireColumn.AutoFit
End With
ActiveSheet.Name = Format(Date, "mmddyy")
End Sub

Basically what I'd like to do is add something that worked like a vlookup copied down through all applicable cells in column M. The vlookup formula as I'd do it without VBA would be =VLOOKUP($A2,PREVIOUS!$A$2:$M$3034,13,FALSE), and then copied down through the necessary cells.

Any suggestions on how to do this, while taking into account changing numbers of rows to look up from and changing numbers of rows to bring data over to?

 

[Jerry Sullivan]

Any suggestions on how to do this, while taking into account changing numbers of rows to look up from and changing numbers of rows to bring data over to?

Hi,

You can use .FormulaR1C1 to apply your formula and then change it to a value.

With rngX
     .FormulaR1C1= "=VLOOKUP(RC1,Previous!R2C1:R3034C13,13,FALSE)"
     .FormulaR1C1= .value
End with

I have a ton to learn about VBA...

I hope you don't mind a few other comments/suggestions:
1. Try eliminating the use of .Select and .Selection - it will make your code more efficient and easier to follow.
2. Use Worksheet.AutoFiltermode=False to clear Autofilters
3. Why are you enabling events in the middle of your procedure?

Good luck!

 

[HRIS]

Hi,

You can use .FormulaR1C1 to apply your formula and then change it to a value.

With rngX
     .FormulaR1C1= "=VLOOKUP(RC1,Previous!R2C1:R3034C13,13,FALSE)"
     .FormulaR1C1= .value
End with

I hope you don't mind a few other comments/suggestions:
1. Try eliminating the use of .Select and .Selection - it will make your code more efficient and easier to follow.
2. Use Worksheet.AutoFiltermode=False to clear Autofilters
3. Why are you enabling events in the middle of your procedure?

Good luck!

Thank you for your response. I had continued looking into it yesterday evening and had come up with the following code:

Dim ds As Range
Dim Previous As Range
Dim LR As Integer
Set ds = ActiveSheet.Range("A1").CurrentRegion
Application.DisplayAlerts = False
With ActiveSheet
    Range("E:P,R:U,W:X,Z:Z,AE:AG,AI:IV").Delete Shift:=xlToLeft
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.AutoFilter
    Cells.EntireColumn.AutoFit
 
    Selection.AutoFilter Field:=5, Criteria1:="FTNB"
    Set ds = ds.Offset(1, 0).Resize(ds.Rows.Count - 1)
    Set ds = ds.SpecialCells(xlCellTypeVisible)
    ds.EntireRow.Delete
 
    Set ds = ActiveSheet.Range("A1").CurrentRegion
 
    Selection.AutoFilter Field:=5, Criteria1:="PTNB"
    Set ds = ds.Offset(1, 0).Resize(ds.Rows.Count - 1)
    Set ds = ds.SpecialCells(xlCellTypeVisible)
    ds.EntireRow.Delete
 
    Selection.AutoFilter Field:=5
 
    Range("M1") = "DATE SEEN"
    Range("M1").Font.Bold = True
    Range("M:M").NumberFormat = "m/d/yyyy"
 
 
LR = Range("A" & Rows.Count).End(xlUp).Row
PR = Worksheets("Previous").Range("A" & Rows.Count).End(xlUp).Row
Set Previous = Worksheets("Previous").Range("A2:M" & LR)
 
    Range("M2:M" & LR).FormulaArray = Application.WorksheetFunction.VLookup(Range("A2:A" & LR), Previous, 13, False)
    Columns("M:M").Select
    Selection.Replace What:="#N/A", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.AutoFilter
    Selection.AutoFilter
    Cells.EntireColumn.AutoFit
End With
ActiveSheet.Name = Format(Date, "mmddyy")
Worksheets("Previous").Delete
Application.DisplayAlerts = True
Range("A1").Select
End Sub

In your opinion, is the way I ended up doing the formula ok or would it be more efficient to do it another way?

Regarding your other suggestions:
1&2) I eliminated some of the .Select & .Selections - but wasn't sure how to eliminate the ones when turning on/off the Autofilter. I tried using the Worksheet.AutoFiltermode=False (&True) to turn off/on the autofilter - but kept getting "Run time error 424. Object required." Appreciate any suggestions you have on how to do this?

3) I tried enabling events in the procedure as I had read that would prevent the message boxes from popping up when I delete rows/sheets. I ended up changing that to Application.DisplayAlerts = True/False.

Thank you again for your assistance, and appreciate any and all feedback on my code!

 

[Jerry Sullivan]

The edits you made are a good step in the right direction.
I've taken that a step further in the code below.

Most of the changes I made won't make a significant difference in the speed of the macro,
but rather they make it easier to follow and maintain if you have changes.

A couple of the edits could potentially affect your results and are worth noting:
1. If a range might be Nothing you should test for that before calling its Properties or Methods.
2. When using a With block, you need to use "dots" as qualifiers to indicate you are referencing the With Object

Sub Update_From_Previous()
    Dim ds As Range
    Dim strWSN As String
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    On Error GoTo CleanUp
    With ActiveSheet
        .AutoFilterMode = False
        .Range("E:P,R:U,W:X,Z:Z,AE:AG,AI:IV").Delete Shift:=xlToLeft
        With .Range("A1").CurrentRegion
            .AutoFilter Field:=5, Criteria1:="=FTNB", _
                Operator:=xlOr, Criteria2:="=PTNB"
            Set ds = .Offset(1, 0).Resize(.Rows.Count - 1) _
                .SpecialCells(xlCellTypeVisible)
            If Not ds Is Nothing Then
                ds.EntireRow.Delete
            End If
        End With
        .AutoFilterMode = False
        With .Range("M1")
            .Value = "DATE SEEN"
            .Font.Bold = True
        End With
 
        With .Range("M2:M" & .Range("A" & .Rows.Count).End(xlUp).Row)
             .NumberFormat = "m/d/yyyy"
             .FormulaR1C1 = "=VLOOKUP(RC1,Previous!C1:C13,13,FALSE)"
             .FormulaR1C1 = .Value
             .Replace What:="#N/A", Replacement:="", LookAt:=xlWhole
        End With
 
        .Cells.EntireColumn.AutoFit
        strWSN = Format(Date, "mmddyy")
        On Error Resume Next
        If (IsError(Sheets(strWSN).Activate)) Then
            .Name = strWSN
        Else
            Application.ScreenUpdating = True
            MsgBox "Unable to rename Sheet " & .Name & " to " & strWSN
        End If
        Worksheets("Previous").Delete
        Application.Goto Reference:=.Range("A1"), Scroll:=True
    End With
CleanUp:
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub

Regarding the use of the ArrayFormula to do the VLookup...that was an interesting approach and
it's great to see you figured that out.

Depending on the size of your dataset, I think the R1C1formula approach could run noticiably faster -
you can try it both ways and see what you find.

Sorry if I've overwhelmed you with comments.
Please compare the two versions and let me know if you have any questions.

 

[HRIS]

Thank you for all of the comments and assistance! It is much appreciated!!

 

[HRIS]

Just wanted to let you know that I made the changes you suggested and the code does run a ton faster with the formula method you indicated! Thank you again!

 

[Jerry Sullivan]

Great to hear that helped. Thanks for the feedback.