Vlookup VBA Help

[cmschmitz24]

Can someone help me update my code to not bug when doing the VLOOKUP if there is only 1 row of data on the sheet? It has no issues if there is no data or if there is more than 1 row of data, but I don't know why it bugs with only 1 row.

Sheets("Summer Audit").Select
                
    Range("O2").FormulaR1C1 = "=VLOOKUP(RC[-12],'Load Status'!C[-12],1,FALSE)"
    Range("O2").AutoFill Destination:=Range("O2:O" & Cells(Rows.Count, "A").End(xlUp).Row)
    
    Range("P2").FormulaR1C1 = "=VLOOKUP(RC[-13],'Not Taken'!C[-13],1,FALSE)"
    Range("P2").AutoFill Destination:=Range("P2:P" & Cells(Rows.Count, "A").End(xlUp).Row)
    
    Range("Q2").FormulaR1C1 = "=VLOOKUP(RC[-14],'Zero Earnings'!C[-14],1,FALSE)"
    Range("Q2").AutoFill Destination:=Range("Q2:Q" & Cells(Rows.Count, "A").End(xlUp).Row)
    
    Range("R2").FormulaR1C1 = "=VLOOKUP(RC[-15],'Not Processed'!C[-15],1,FALSE)"
    Range("R2").AutoFill Destination:=Range("R2:R" & Cells(Rows.Count, "A").End(xlUp).Row)
    
    Range("S2").FormulaR1C1 = "=VLOOKUP(RC[-16],'Mismatch'!C[-16],1,FALSE)"
    Range("S2").AutoFill Destination:=Range("S2:S" & Cells(Rows.Count, "A").End(xlUp).Row)
    
    Range("T2").FormulaR1C1 = "=VLOOKUP(RC[-17],'Deduction'!C[-17],1,FALSE)"
    Range("T2").AutoFill Destination:=Range("T2:T" & Cells(Rows.Count, "A").End(xlUp).Row)

 

[Fluff]

Try it like

   Dim Usdrws As Long
   Sheets("Summer Audit").Select
   Usdrws = Range("A" & Rows.Count).End(xlUp).Row
   If Usdrws >= 2 Then
      Range("O2:O" & Usdrws).FormulaR1C1 = "=VLOOKUP(RC[-12],'Load Status'!C[-12],1,FALSE)"
      Range("P2:P" & Usdrws).FormulaR1C1 = "=VLOOKUP(RC[-13],'Not Taken'!C[-13],1,FALSE)"
      Range("Q2:Q" & Usdrws).FormulaR1C1 = "=VLOOKUP(RC[-14],'Zero Earnings'!C[-14],1,FALSE)"
      Range("R2:R" & Usdrws).FormulaR1C1 = "=VLOOKUP(RC[-15],'Not Processed'!C[-15],1,FALSE)"
      Range("S2:S" & Usdrws).FormulaR1C1 = "=VLOOKUP(RC[-16],'Mismatch'!C[-16],1,FALSE)"
      Range("T2:T" & Usdrws).FormulaR1C1 = "=VLOOKUP(RC[-17],'Deduction'!C[-17],1,FALSE)"
   End With

 

[cmschmitz24]

I just changed the End With to End If otherwise it seems to work great! Is this a short/condensed/cleaned up version that I can update all my vlookups to on the other sheets I have?

 

[Fluff]

I just changed the End With to End If

Oops ?

Is this a short/condensed/cleaned up version that I can update all my vlookups to on the other sheets I have?

Yes it is.