Excel VBA - vlookup for a variable number of rows within a macro

Question

I've created a macro to organize a data set and compile into another sheet in a way that makes more sense for doing analyses. The set originally is comprised of columns for user, timestamp and 3 possible events. The user could appear on multiple rows but I wanted to look at this data set by user and have a separate column for each timestamp. The macros I've made can successfully clean, filter by event type, and separate by event type into separate worksheets (no matter how many rows of data) but I'm having trouble with compiling data into one sheet using vlookup AND accounting for a variable number of rows. I have looked at other answers to this question and tried this:

ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-2],'Email Opened'!R1C1:R" & LastRow0 & "C3,2,FALSE)"

... but it keeps giving me errors.

What I have below (Vlookup_events2) works but just not for the entire variable number of rows.Please help me adjust the code for the vlookup so it will work no matter how many rows.

Here is the code below for separating data (just for reference), then the problem macro - compiling it with vlookup. I would really appreciate some help, I know there's an amazing VBA expert out there!

    Sheets.Add
    Sheets("Sheet1").Name = "Email Sent"
    ActiveSheet.Next.Select
    Selection.AutoFilter
    ActiveCell.Offset(0, 2).Range("A1").Select
    ActiveSheet.Range("$A$1:$D$1000000").AutoFilter Field:=3, Criteria1:= _
        "=Campaign Created", Operator:=xlOr, Criteria2:="=Email Sent"
    ActiveCell.Offset(0, -2).Range("A1:D2355").Select
    ActiveCell.Activate
    Selection.Copy
    ActiveSheet.Previous.Select
    Range("A1").Select
    ActiveSheet.Paste
    ActiveSheet.Next.Select
    Range("A1").Select
    ActiveSheet.Range("$A$1:$D$1000000").AutoFilter Field:=3, Criteria1:= _
        "=Campaign Created", Operator:=xlOr, Criteria2:="=Email Opened"
    Sheets.Add
    Sheets("Sheet2").Name = "Email Opened"
    ActiveSheet.Next.Select
    ActiveCell.Range("A1:D1000000").Select
    Application.CutCopyMode = False
    Selection.Copy
    ActiveSheet.Previous.Select
    Range("A1").Select
    ActiveSheet.Paste
    ActiveCell.Offset(0, 3).Range("A1").Select
    Application.CutCopyMode = False
    Selection.ClearContents
    ActiveCell.Columns("A:A").EntireColumn.Select
    Selection.Delete Shift:=xlToLeft
    Sheets.Add
    Sheets("Sheet3").Name = "Clicked Link"
    ActiveSheet.Next.Select
    ActiveSheet.Next.Select
    ActiveCell.Offset(0, 2).Range("A1").Select
    ActiveSheet.Range("$A$1:$D$1000000").AutoFilter Field:=3, Criteria1:= _
        "=Campaign Created", Operator:=xlOr, Criteria2:="=Clicked Link"
    ActiveCell.Offset(0, -2).Range("A1:D1000000").Select
    ActiveCell.Activate
    Selection.Copy
    ActiveSheet.Previous.Select
    ActiveSheet.Previous.Select
    Range("A1").Select
    ActiveSheet.Paste
    ActiveCell.Offset(0, 3).Columns("A:A").EntireColumn.Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    Range("A1").Select
End Sub
Sub Vlookup_events2()
' Vlookup_events2 Macro

    ActiveSheet.Previous.Select
    Range("A2").Select
    ActiveCell.FormulaR1C1 = "user"
    Range("A3").Select
    ActiveSheet.Next.Select
    Range("A2").Select
    ActiveCell.FormulaR1C1 = "user"
    Range("A3").Select
    ActiveSheet.Next.Select
    Range("A2").Select
    ActiveCell.FormulaR1C1 = "user"
    Range("A3").Select
    Sheets.Add
    Sheets("Sheet4").Name = "Compiled Events"
    ActiveSheet.Previous.Select
    ActiveSheet.Previous.Select
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    ActiveSheet.Next.Select
    ActiveSheet.Next.Select
    Range("A1").Select
    ActiveSheet.Paste
    Columns("C:D").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "Email Sent Time"
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "Email Opened Time"
    Range("D1").Select
    ActiveCell.FormulaR1C1 = "Clicked Link Time"
    Range("A1").Select
    Application.Goto Reference:="R2C3"
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-2],'Email Opened'!R1C1:R601C3,2,FALSE)"
    Range("C3").Select
    Range(Selection, Selection.End(xlUp)).Select
    Columns("C:C").Select
    Selection.FormulaR1C1 = "=VLOOKUP(RC[-2],'Email Opened'!R1C1:R601C3,2,FALSE)"
    Columns("D:D").Select
    Selection.FormulaR1C1 = "=VLOOKUP(RC[-3],'Clicked Link'!R1C1:R56C3,2,FALSE)"
    Columns("C:C").Select
    Selection.NumberFormat = "m/d/yyyy h:mm"
    Columns("D:D").Select
    Selection.NumberFormat = "m/d/yyyy h:mm"
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "Email Opened Time"
    Range("D1").Select
    ActiveCell.FormulaR1C1 = "Clicked Link Time"
    Range("C2").Select
End Sub

Sorry, not an answer to your question but you really need to read https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba?rq=1 — SJR, Jun 13 '18 at 15:24
Also see [this](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-vba) for detail on how to properly find the "last" row or column in a sheet/range. — David Zemens, Jun 13 '18 at 15:27
One thing you should do is this: `Debug.Print` the string value you're trying to assign to the formula. Then enter that value manually in to the cell and see what happens. Your formula must be wrong and cannot be evaluated as such, but without seeing the resulting formula, it's hard to provide further assistance. — David Zemens, Jun 13 '18 at 15:31
SJR - I don't know why it's "lastrow0" , I just tried to implement a solution that apparently worked for someone else having this issue. — Enigma, Jun 13 '18 at 15:38
Please, please, please read [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) then modify your recorded code and dozens of experts will fight over the right to assist you; — , Jun 13 '18 at 15:41
Well it will error if you use a variable in your code but haven't defined it in this context. As you haven't included it in your posted code, it's hard to know how it should be used. There is plenty online about how to define the last used row. — SJR, Jun 13 '18 at 15:47
Thanks David Zemens. No one is more noob than me at this - I'm a market researcher and have never made a macro before... I did try to tweak it by manually entering the number of rows but something is still wrong. No error, but it's not taking the full set into account. — Enigma, Jun 13 '18 at 15:52

Excel VBA - vlookup for a variable number of rows within a macro

0 Answers0