Forum Discussion

Linesol's avatar
Linesol
Copper Contributor
Nov 17, 2020

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 that the 2nd column data entry is entered adjacent to each repeated entry on 1st column. i have a button to do this but it is not working here is the code which i have adapted from https://www.xelplus.com/return-multiple-match-values-in-excel/

 


Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("sheet1")
Dim y As Variant
Dim n As Variant
Dim c As Integer

 

c = Application.WorksheetFunction.CountIf(sh.Range("A4:A101"), Me.TextBox1.Value)

 

For n = 1 To c

 

y = Application.WorksheetFunction.Aggregate(15, 3, ((sh.Range("A4:A101") = Me.TextBox1.Value) / (sh.Range("A4:A101") = Me.TextBox1.Value) * Application.WorksheetFunction.Row(sh.Range("A4:A101"))), n)

 

sh.Range("B" & y + 2).Value = Me.TextBox2.Value

 

Next

 

Its coming up with error 13: type mismatch,

excel
Formulas and Functions
Macros and VBA

2 Replies

  • Craig Hatmaker's avatar
    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.

Resources