Formulas to work when only 1 row of data - VBA

[cmschmitz24]

Can someone help adjust my formulas to work when there is only 1 row of data on a spreadsheet? This works when there is 2 or more rows.

'adds ADD formulas
    Range("DK3").FormulaR1C1 = "=LEFT(RC[-89],3)"
    Range("DK3").AutoFill Destination:=Range("DK3:DK" & Cells(Rows.Count, "A").End(xlUp).Row)
          
    Range("DL3").FormulaR1C1 = "=DATEDIF(RC[-81],R2C119,""M"")"
    Range("DL3").Select
    Selection.Copy
    Application.CutCopyMode = False
    Selection.AutoFill Destination:=Range("DL3:DL" & Cells(Rows.Count, "A").End(xlUp).Row)
        
    Range("DM3").FormulaR1C1 = _
        "=VLOOKUP(RC[-91],'Reason Translator'!C[-111]:C[-110],2,FALSE)*RC[-1]"
    Range("DM3").AutoFill Destination:=Range("DM3:DM" & Cells(Rows.Count, "A").End(xlUp).Row)
    
    Range("DN3").FormulaR1C1 = _
        "=VLOOKUP(RC[-92],'Reason Translator'!C[-112]:C[-111],2,FALSE)"
    Range("DN3").AutoFill Destination:=Range("DN3:DN" & Cells(Rows.Count, "A").End(xlUp).Row)
    
    Range("DO3").FormulaR1C1 = "=RC[-84]+60"
    Range("DO3").AutoFill Destination:=Range("DO3:DO" & Cells(Rows.Count, "A").End(xlUp).Row)
    
    Range("DP3").FormulaR1C1 = "=RC[-85]+1"
    Range("DP3").AutoFill Destination:=Range("DP3:DP" & Cells(Rows.Count, "A").End(xlUp).Row)
    
    'adds DltDnt formulas
    Range("DQ3").FormulaR1C1 = "=IF(RC[-91]=""Term"",RC[-85],"""")"
    Range("DQ3").AutoFill Destination:=Range("DQ3:DQ" & Cells(Rows.Count, "A").End(xlUp).Row)
    Range("DR3").FormulaR1C1 = "=IF(RC[-92]=""Retirement"",RC[-86],"""")"
    Range("DR3").AutoFill Destination:=Range("DR3:DR" & Cells(Rows.Count, "A").End(xlUp).Row)
    Range("DS3").FormulaR1C1 = _
        "=IF(RC[-93]=""Divorce"",EDATE(RC[-119],36),EDATE(RC[-119],18))"
    Range("DS3").AutoFill Destination:=Range("DS3:DS" & Cells(Rows.Count, "A").End(xlUp).Row)
    Columns("DQ:DS").NumberFormat = "m/d/yyyy"

 

[DanteAmor]

Try this:

Sub Formulas()
  Dim lr As Long
  
  'adds ADD formulas
  Range("DK3").Formula = "=LEFT(RC[-89],3)"
  Range("DL3").Formula = "=DATEDIF(RC[-81],R2C119,""M"")"
  Range("DM3").Formula = "=VLOOKUP(RC[-91],'Reason Translator'!C[-111]:C[-110],2,FALSE)*RC[-1]"
  Range("DN3").Formula = "=VLOOKUP(RC[-92],'Reason Translator'!C[-112]:C[-111],2,FALSE)"
  Range("DO3").Formula = "=RC[-84]+60"
  Range("DP3").Formula = "=RC[-85]+1"
  
  'adds DltDnt formulas
  Range("DQ3").Formula = "=IF(RC[-91]=""Term"",RC[-85],"""")"
  Range("DR3").Formula = "=IF(RC[-92]=""Retirement"",RC[-86],"""")"
  Range("DS3").Formula = "=IF(RC[-93]=""Divorce"",EDATE(RC[-119],36),EDATE(RC[-119],18))"
  
  lr = Cells(Rows.Count, "A").End(xlUp).Row
  If lr > 3 Then Range("DK3:DS3").AutoFill Range("DK3:DS" & lr)
  Columns("DQ:DS").NumberFormat = "m/d/yyyy"
End Sub

 

[cmschmitz24]

That update works great! Thanks!

Although, I forgot I have 2 other spots in my macro that need fixing for 1 row of data as well. Can you help me with these?

    Columns("AC:AC").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("AC2").Value = "Combined Reason"
    Range("AC3").FormulaR1C1 = "=CONCATENATE(RC[-2],RC[-1])"
    Range("AC3").AutoFill Destination:=Range("AC3:AC" & Cells(Rows.Count, "A").End(xlUp).Row)
    Columns("AD:AD").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("AD2").Value = "Reason"
    Range("AD3").FormulaR1C1 = _
        "=VLOOKUP(RC[-1],'Reason Translator'!C[-29]:C[-28],2,FALSE)"
    Range("AD3").AutoFill Destination:=Range("AD3:AD" & Cells(Rows.Count, "A").End(xlUp).Row)

and

Columns("G:G").Insert Shift:=xlToRight
  Range("G3").FormulaR1C1 = "=IF(RC[-1]=""E"",""Employee"","""")"
  Range("G3").AutoFill Destination:=Range("G3:G" & Cells(Rows.Count, "A").End(xlUp).Row)
  Range("G3:G" & Cells(Rows.Count, "A").End(xlUp).Row).Select
  Selection.Copy
  Range("F3:F" & Cells(Rows.Count, "A").End(xlUp).Row).Select
  Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone
  Columns("G:G").Select
  Application.CutCopyMode = False
  Selection.Delete Shift:=xlToLeft

 

[DanteAmor]

I forgot I have 2 other spots

Try this

Sub Formulas()
  Dim lr As Long
  
  'adds ADD formulas
  Range("DK3").Formula = "=LEFT(RC[-89],3)"
  Range("DL3").Formula = "=DATEDIF(RC[-81],R2C119,""M"")"
  Range("DM3").Formula = "=VLOOKUP(RC[-91],'Reason Translator'!C[-111]:C[-110],2,FALSE)*RC[-1]"
  Range("DN3").Formula = "=VLOOKUP(RC[-92],'Reason Translator'!C[-112]:C[-111],2,FALSE)"
  Range("DO3").Formula = "=RC[-84]+60"
  Range("DP3").Formula = "=RC[-85]+1"
  
  'adds DltDnt formulas
  Range("DQ3").Formula = "=IF(RC[-91]=""Term"",RC[-85],"""")"
  Range("DR3").Formula = "=IF(RC[-92]=""Retirement"",RC[-86],"""")"
  Range("DS3").Formula = "=IF(RC[-93]=""Divorce"",EDATE(RC[-119],36),EDATE(RC[-119],18))"
  
  lr = Cells(Rows.Count, "A").End(xlUp).Row
  If lr > 3 Then Range("DK3:DS3").AutoFill Range("DK3:DS" & lr)
  Columns("DQ:DS").NumberFormat = "m/d/yyyy"
  
  Columns("AC:AC").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
  Range("AC2").Value = "Combined Reason"
  Range("AC3").Formula = "=CONCATENATE(RC[-2],RC[-1])"
  If lr > 3 Then Range("AC3").AutoFill Range("AC3:AC" & lr)
  Columns("AD:AD").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
  Range("AD2").Value = "Reason"
  Range("AD3").Formula = "=VLOOKUP(RC[-1],'Reason Translator'!C[-29]:C[-28],2,FALSE)"
  If lr > 3 Then Range("AD3").AutoFill Range("AD3:AD" & lr)
  
  Columns("G:G").Insert Shift:=xlToRight
  Range("G3").Formula = "=IF(RC[-1]=""E"",""Employee"","""")"
  If lr > 3 Then Range("G3").AutoFill Range("G3:G" & lr)
  Range("G3:G" & lr).Copy
  Range("F3:F" & lr).PasteSpecial xlPasteValues
  Columns("G:G").Select
  Application.CutCopyMode = False
  Selection.Delete Shift:=xlToLeft
End Sub

 

[cmschmitz24]

Perfect, thank you so much!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.