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

%3CLINGO-SUB%20id%3D%22lingo-sub-1894695%22%20slang%3D%22en-US%22%3EI%20need%20new%20data%20entry%20entered%20adjacent%20to%20each%20repeated%20value%20in%20table%20on%20VBA%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1894695%22%20slang%3D%22en-US%22%3E%3CDIV%20class%3D%22lia-message-subject-wrapper%20lia-component-subject%20lia-component-message-view-widget-subject-with-options%22%3E%3CSPAN%3EThe%20problem%20i%20am%20trying%20to%20solve%20is%20the%20following%3A%26nbsp%3B%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%20class%3D%22lia-message-body%20lia-component-message-view-widget-body%20lia-component-body-signature-highlight-escalation%20lia-component-message-view-widget-body-signature-highlight-escalation%22%3E%3CDIV%20class%3D%22lia-message-body-content%22%3E%3CP%3EI%20have%20a%202*97%20(r*c)%20table.%3C%2FP%3E%3CP%3Ethe%20table%20has%20repeating%20entries%20at%20different%20locations.%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20to%20add%20the%20new%20data%20entry%20for%20the%20second%20columns%20so%20that%20the%202nd%20column%20data%20entry%20is%20entered%20adjacent%20to%20each%20repeated%20entry%20on%201st%20column.%20i%20have%20a%20button%20to%20do%20this%20but%20it%20is%20not%20working%20here%20is%20the%20code%20which%20i%20have%20adapted%20from%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fwww.xelplus.com%2Freturn-multiple-match-values-in-excel%2F%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3Ehttps%3A%2F%2Fwww.xelplus.com%2Freturn-multiple-match-values-in-excel%2F%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EDim%20sh%20As%20Worksheet%3CBR%20%2F%3ESet%20sh%20%3D%20ThisWorkbook.Sheets(%22sheet1%22)%3CBR%20%2F%3EDim%20y%20As%20Variant%3CBR%20%2F%3EDim%20n%20As%20Variant%3CBR%20%2F%3EDim%20c%20As%20Integer%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ec%20%3D%20Application.WorksheetFunction.CountIf(sh.Range(%22A4%3AA101%22)%2C%20Me.TextBox1.Value)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20n%20%3D%201%20To%20c%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ey%20%3D%20Application.WorksheetFunction.Aggregate(15%2C%203%2C%20((sh.Range(%22A4%3AA101%22)%20%3D%20Me.TextBox1.Value)%20%2F%20(sh.Range(%22A4%3AA101%22)%20%3D%20Me.TextBox1.Value)%20*%20Application.WorksheetFunction.Row(sh.Range(%22A4%3AA101%22)))%2C%20n)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Esh.Range(%22B%22%20%26amp%3B%20y%20%2B%202).Value%20%3D%20Me.TextBox2.Value%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENext%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIts%20coming%20up%20with%20error%2013%3A%20type%20mismatch%2C%3C%2FP%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1894695%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1896154%22%20slang%3D%22en-US%22%3ERe%3A%20I%20need%20new%20data%20entry%20entered%20adjacent%20to%20each%20repeated%20value%20in%20table%20on%20VBA%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1896154%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F871583%22%20target%3D%22_blank%22%3E%40Linesol%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20may%20like%20this%20approach%20better...%3C%2FP%3E%3CPRE%3EPrivate%20Sub%20CommandButton1_Click()%3CBR%20%2F%3E%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Application.ScreenUpdating%20%3D%20False%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%5BTable1%5D.AutoFilter%20Field%3A%3D1%2C%20Criteria1%3A%3DMe.TextBox1.Value%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%5BTable1%5Bcolumn%202%5D%5D.SpecialCells(xlCellTypeVisible)%20%3D%20Me.TextBox2.Value%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%5BTable1%5D.AutoFilter%20Field%3A%3D1%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Application.ScreenUpdating%20%3D%20True%3CBR%20%2F%3E%3CBR%20%2F%3E%20%20%20%20UserForm1.Hide%3CBR%20%2F%3E%3CBR%20%2F%3EEnd%20Sub%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20if%20not...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20y%20%3D%20Evaluate(%22%3DAGGREGATE(15%2C3%2C((Table1%5Bcolumn%201%5D%3D%22%22%22%20%26amp%3B%20Me.TextBox1.Value%20%26amp%3B%20%22%22%22)%2F(Table1%5Bcolumn%201%5D%3D%22%22%22%20%26amp%3B%20Me.TextBox1.Value%20%26amp%3B%20%22%22%22)*ROW(Table1%5Bcolumn%201%5D))%2C%22%20%26amp%3B%20n%20%26amp%3B%20%22)%22)%3C%2FPRE%3E%3CP%3EThis%20will%20avoid%20some%20of%20the%20differences%20between%20how%20VBA%20handles%20arrays%20and%20Excel%20GUI%20handles%20ranges.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor
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,

2 Replies

@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.