finding next blank row then adding formula

Question

I have a userform that will paste the combobox1 value to the next available row in column B. There are 3 columns to the right (C,D & E) that have vlookup formulas that I would like to fill down as the combobox1 value is pasted to column B.

This is the vlookup formula and B123 would be the combobox1 value for that specific row =IFERROR(VLOOKUP(B123,'Sheet1'!$A$3:$C$370,2,0),0)/1000000

This is what I have tried so far

  Dim nextrow As Long 
  Dim nextrow1 As Long
  Dim nextrow2 As Long
  nextrow = Cells(Rows.Count, "C").End(xlUp).Row + 1
        .Range(rows.count, nextrow - 1).FillDown
  nextrow1 = Cells(Rows.Count, "D").End(xlUp).Row + 1
        .Range(rows.count, nextrow - 1).FillDown
  nextrow2 = Cells(Rows.Count, "E").End(xlUp).Row + 1
        .Range(rows.count, nextrow - 1).FillDown
  End Sub

This code will paste the combobox value into the next available row in column B

Private sub CommandButton1_click()

    With Worksheets("sheet")
        .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0) = ComboBox1.Value 
        .Cells(.Rows.Count, "B").End(xlUp).Offset(1, 0) = ComboBox2.Value 
    End With
ActiveWorkbook.RefreshAll
Unload Me

I suggest that first, you fill all values in column B, and later on you can drag down your formulas at C,D and E. Record a macro to see the code to drag down. — Foxfire And Burns And Burns
I could do that however the values of Column B are added 1-3 at a time while the formulas in C,D and E are needed right away when B is populated. @FoxfireAndBurnsAndBurns — Tmacjoshua
I see what you mean @FoxfireAndBurnsAndBurns but am not sure how to reference the range as new values are being added constantly. — Tmacjoshua
How does your code transfer the value from the ComboBox into the next empty cell in Col B? — GMalc

GMalc GMalc · Accepted Answer · 2019-05-22T16:48:48

I added a line to your code, it should autofill each time you click the button.

Private sub CommandButton1_click()

With Worksheets("sheet")
    .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0) = ComboBox1.Value 
    .Cells(.Rows.Count, "B").End(xlUp).Offset(1, 0) = ComboBox2.Value
    .Cells(Rows.Count, "B").End(xlUp).Offset(-1, 1).Resize(, 3).AutoFill .Cells(Rows.Count, "B").End(xlUp).Offset(-1, 1).Resize(, 3).Resize(2)

    With .Cells(Rows.Count, "B").End(xlUp).Offset(-1, 1).Resize(, 3).Resize(2)
        .Borders.LineStyle = xlContinuous
    End With                   

End With
ActiveWorkbook.RefreshAll
Unload Me

finding next blank row then adding formula

1 Answers