Forum Discussion

CZeus's avatar
CZeus
Copper Contributor
Feb 17, 2023

Need help with macro to autofill rows

Hello, I am trying to write a macro, and I am not used to VBA nor do I have much experience writing code to begin with, however I found some code from other posts that almost gets the job done that I need, but there's one issue. I have 6 columns that I want to autofill after I plug in data, and the length of this data changes every day, so I am trying to autofill to the length of the data each day. I am using this code to do so:

 

Private Sub UpdateValues_Click()
Sheets("Data").Select
Range("R2").Select
ActiveCell.Formula2 = "=IF(ISBLANK(H2),""Good"",TEXTSPLIT(H2,""|""))"
Selection.AutoFill Destination:=Range("R2:R" & Range("H" & Rows.Count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select

Range("P2").Select
ActiveCell.Formula2 = "=IF(AND(O2>=Q$1,O2<Q$2),""1"",IF(AND(O2>=Q$2,O2<Q$3),""2"",""3""))"
Selection.AutoFill Destination:=Range("P2:P" & Range("H" & Rows.Count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select

Range("O2").Select
ActiveCell.Formula2 = "=TIMEVALUE(N2)"
Selection.AutoFill Destination:=Range("O2:O" & Range("H" & Rows.Count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select

Range("N2").Select
ActiveCell.Formula2 = "=HOUR(B2) & "":"" & RIGHT(""0"" & MINUTE(B2),2)"
Selection.AutoFill Destination:=Range("N2:N" & Range("H" & Rows.Count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select

Range("M2").Select
ActiveCell.Formula2 = "=MONTH(B2) & ""/"" & DAY(B2) & ""/"" & YEAR(B2)"
Selection.AutoFill Destination:=Range("M2:M" & Range("H" & Rows.Count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select

Range("L2").Select
ActiveCell.Formula2 = "=D2"
Selection.AutoFill Destination:=Range("L2:L" & Range("H" & Rows.Count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select
End Sub

 

There's probably a way to clean it up and make it more effecient, but I am not too concerned about that. what I am running into is I have headers in the first row of cells, but for some reason this code is replacing the headers with the formulas that I want to start from row 2 and on. How can I get the autofill to skip the first row so that I can keep the column headers? Thanks for any help!

excel
Formulas and Functions
Macros and VBA
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Feb 17, 2023

    CZeus 

    By the way, do you really want the formula in column P to return text values "1", "2" and "3"?

    If it should return numbers 1, 2, 3, change the line

        Range("P2:P" & m).Formula2 = "=IF(AND(O2>=Q$1,O2<Q$2),""1"",IF(AND(O2>=Q$2,O2<Q$3),""2"",""3""))"

    to

        Range("P2:P" & m).Formula2 = "=IF(AND(O2>=Q$1,O2<Q$2),1,IF(AND(O2>=Q$2,O2<Q$3),2,3))"
    • CZeus's avatar
      CZeus
      Copper Contributor
      Feb 17, 2023

      HansVogelaar 

       

      Haha I probably wasn't thinking when I wrote that, thanks for the tip.

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Feb 17, 2023

    CZeus 

    How about

    Private Sub UpdateValues_Click()
    Dim w As Worksheet
    Dim m As Long
    Set w = Worksheets("Data")
    m = w.Range("H" & w.Rows.Count).End(xlUp).Row
    If m = 1 Then Exit Sub
    Application.ScreenUpdating = False
    Application.ScreenUpdating = True
    Range("L2:L" & m).Formula2 = "=D2"
    Range("M2:M" & m).Formula2 = "=MONTH(B2) & ""/"" & DAY(B2) & ""/"" & YEAR(B2)"
    Range("N2:N" & m).Formula2 = "=HOUR(B2) & "":"" & RIGHT(""0"" & MINUTE(B2),2)"
    Range("O2:P" & m).Formula2 = "=TIMEVALUE(N2)"
    Range("P2:P" & m).Formula2 = "=IF(AND(O2>=Q$1,O2<Q$2),""1"",IF(AND(O2>=Q$2,O2<Q$3),""2"",""3""))"
    Range("R2:R" & m).Formula2 = "=IF(ISBLANK(H2),""Good"",TEXTSPLIT(H2,""|""))"
End Sub
    • CZeus's avatar
      CZeus
      Copper Contributor
      Feb 17, 2023
      that worked, and is a lot more concise than mine. Thank you for the help!

Resources