I need new data entry entered adjacent to each repeated value in table on VBA

The problem i am trying to solve is the following: I have a 2*97 (r*c) table. the table has repeating entries at different locations. I want to add the new data entry for the second columns so...

TEST.xlsm23 KB

excel

Formulas and Functions

Macros and VBA

Craig Hatmaker

Iron Contributor

Nov 17, 2020

Linesol

You may like this approach better...

Private Sub CommandButton1_Click()

    Application.ScreenUpdating = False
    [Table1].AutoFilter Field:=1, Criteria1:=Me.TextBox1.Value
    [Table1[column 2]].SpecialCells(xlCellTypeVisible) = Me.TextBox2.Value
    [Table1].AutoFilter Field:=1
    Application.ScreenUpdating = True

    UserForm1.Hide

End Sub

But if not...

    y = Evaluate("=AGGREGATE(15,3,((Table1[column 1]=""" & Me.TextBox1.Value & """)/(Table1[column 1]=""" & Me.TextBox1.Value & """)*ROW(Table1[column 1]))," & n & ")")

This will avoid some of the differences between how VBA handles arrays and Excel GUI handles ranges.

Linesol
Copper Contributor
Nov 18, 2020
Craig Hatmaker

Thank you. this works perfectly

Forum Discussion

I need new data entry entered adjacent to each repeated value in table on VBA

Resources