Posts by solti1

    Hello, when cell is clicked a form is opened allowing user to select couple of options. I need to change the code so that: it still opens a form allowing user to select options but also when cell is clicked it let user to add his comment to the existing string (without doublling it)

    Code
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)	Dim rngDV As Range	Dim oldVal As String	Dim newVal As String	Dim strList As String	On Error Resume Next	Application.EnableEvents = False	 	   Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)	   On Error GoTo exitHandler	 	   If rngDV Is Nothing Then GoTo exitHandler	   	   If Not Intersect(Target, rngDV) Is Nothing Then	      If Target.Validation.Type = 3 Then	         	         strList = Target.Validation.Formula1	         strList = Right(strList, Len(strList) - 1)	         strDVList = strList	         frmDVList.Show	      End If	   End If	 	exitHandler:	  Application.EnableEvents = True	  	End Sub	 	Private Sub Worksheet_Change(ByVal Target As Range)	Dim rngDV As Range	Dim oldVal As String	Dim newVal As String	Dim strSep As String	strSep = ", "	  Application.EnableEvents = False	On Error Resume Next	If Target.Count > 1 Then GoTo exitHandler	 	 	Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)	On Error GoTo exitHandler	 	If rngDV Is Nothing Then GoTo exitHandler	 	If Intersect(Target, rngDV) Is Nothing Then	    	    	    'here I'd like to make change	    Range(rngDV).ClearContents	    	    	    	Else	 	  newVal = Target.Value	  Application.Undo	  oldVal = Target.Value	  Target.Value = newVal	   If newVal = "" Then	      	   Else	         If oldVal = "" Then	            Target.Value = newVal	         Else	            Target.Value = oldVal & strSep & newVal	         End If	    End If	 	End If	 	 	exitHandler:	  Application.EnableEvents = True	End Sub

    Could you please help? s.

    Re: 255 character limit with VBA array formula


    clicking on "Evaluate formula" from the toolbar I get "Value not Available Error" which is strange because when I manually edit the formula on the sheet and confirm it with ENTER it works
    Hmmm...............

    Hello.

    Could you please help.

    Below code is working


    Code
    [/FONT]Dim Index1 As VariantDim Index2 As VariantIndex1 = "=INDEX(lista!R20C2:R2000C2000,MATCH(""Pła"",lista!R20C1:R2000C1,0),MATCH(RC2&RC3,lista!R17C2:R17C2000&lista!R13C2:R13C2000,0))"Index2 = "=INDEX(lista!R20C2:R2000C2000,MATCH(""Hon"",lista!R20C1:R2000C1,0),MATCH(RC2&RC3,lista!R17C2:R17C2000&lista!R13C2:R13C2000,0))".Cells(i, 13).FormulaArray = Index1



    Below code is not working

    Code
    [/FONT]Dim Index1 As VariantDim Index2 As VariantIndex1 = "=INDEX(lista!R20C2:R2000C2000,MATCH(""Pła"",lista!R20C1:R2000C1,0),MATCH(RC2&RC3,lista!R17C2:R17C2000&lista!R13C2:R13C2000,0))"Index2 = "=INDEX(lista!R20C2:R2000C2000,MATCH(""Hon"",lista!R20C1:R2000C1,0),MATCH(RC2&RC3,lista!R17C2:R17C2000&lista!R13C2:R13C2000,0))".Cells(i, 13).FormulaArray = “=sum(Index1, Index2)”



    s.

    Re: Autofilter not refreshing on change


    yes. I must have othe bugs in the code

    Hi,


    my code is not refreshing the autofilter. For i = 1 it's working but on increment code : " no_filtered_rows" shows 0 rows (while there are couple)

    .....


    Would be grateful for help

    Hello,


    I have 2 Sheets:
    In the first sheet in col B I have a unique account number
    In the second sheet I have 10 columns. In column 3 I have the same number of in many rows (details on. F-R). It can be up to 5 thousand records in second sheet.


    For each account from the first sheet I have to look up a matching number of account in second sheet and copy rows under a searched row from first sheet. It can be ca. 100 unique accounts in first sheet.


    Then, for each account in first sheet I have to insert subtotals and grouping.
    I have done that with "for... next" implemented many times. For sure to many and takes ages.


    Probably better to use an array. Can somebody suggested me a solution with the use of arrays in this example.


    Sheet1
    [TABLE="width: 720"]

    [tr]


    [td]

    COSTS

    [/td]


    [td]

    Account

    [/td]


    [td]

    Data

    [/td]


    [td]

    Invoice no

    [/td]


    [td]

    Client name

    [/td]


    [td]

    Transaction

    [/td]


    [td]

    Amount

    [/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    I. TOTAL costs

    [/td]


    [td]

    -

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [TD="align: right"]866,00
    [/TD]

    [/tr]


    [tr]


    [td]

    Account1_name

    [/td]


    [td]

    402-009, 402-010, 402-012, 402-023

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [TD="align: right"]676,00
    [/TD]

    [/tr]


    [tr]


    [td][/td]


    [td]

    402-009

    [/td]


    [TD="align: right"]2016-01-29
    [/TD]

    [td]

    inv _23

    [/td]


    [td]

    Kontr23

    [/td]


    [td]

    Text23

    [/td]


    [td]

    87,00

    [/td]


    [/tr]


    [tr]


    [td][/td]


    [td]

    402-009

    [/td]


    [TD="align: right"]2016-01-29
    [/TD]

    [td]

    inv _25

    [/td]


    [td]

    Kontr25

    [/td]


    [td]

    Text25

    [/td]


    [td]

    85,00

    [/td]


    [/tr]


    [tr]


    [td][/td]


    [td]

    SUM: 402-009

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [TD="align: right"]172,00
    [/TD]

    [/tr]


    [tr]


    [td][/td]


    [td]

    402-010

    [/td]


    [TD="align: right"]2016-01-16
    [/TD]

    [td]

    inv _2

    [/td]


    [td]

    Kontr2

    [/td]


    [td]

    Text2

    [/td]


    [td]

    62,00

    [/td]


    [/tr]


    [tr]


    [td][/td]


    [td]

    402-010

    [/td]


    [TD="align: right"]2016-01-21
    [/TD]

    [td]

    inv _6

    [/td]


    [td]

    Kontr6

    [/td]


    [td]

    Text6

    [/td]


    [td]

    84,00

    [/td]


    [/tr]


    [tr]


    [td][/td]


    [td]

    402-010

    [/td]


    [TD="align: right"]2016-01-27
    [/TD]

    [td]

    inv _13

    [/td]


    [td]

    Kontr13

    [/td]


    [td]

    Text13

    [/td]


    [td]

    97,00

    [/td]


    [/tr]


    [tr]


    [td][/td]


    [td]

    SUM: 402-010

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [TD="align: right"]243,00
    [/TD]

    [/tr]


    [tr]


    [td][/td]


    [td]

    402-012

    [/td]


    [TD="align: right"]2016-01-29
    [/TD]

    [td]

    inv _21

    [/td]


    [td]

    Kontr21

    [/td]


    [td]

    Text21

    [/td]


    [TD="align: right"]48,00
    [/TD]

    [/tr]


    [tr]


    [td][/td]


    [td]

    SUM: 402-012

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [TD="align: right"]48,00
    [/TD]

    [/tr]


    [tr]


    [td][/td]


    [td]

    402-023

    [/td]


    [TD="align: right"]2016-01-29
    [/TD]

    [td]

    inv _22

    [/td]


    [td]

    Kontr22

    [/td]


    [td]

    Text22

    [/td]


    [TD="align: right"]92,00
    [/TD]

    [/tr]


    [tr]


    [td][/td]


    [td]

    402-023

    [/td]


    [TD="align: right"]2016-01-29
    [/TD]

    [td]

    inv _24

    [/td]


    [td]

    Kontr24

    [/td]


    [td]

    Text24

    [/td]


    [TD="align: right"]31,00
    [/TD]

    [/tr]


    [tr]


    [td][/td]


    [td]

    402-023

    [/td]


    [TD="align: right"]2016-01-29
    [/TD]

    [td]

    inv _26

    [/td]


    [td]

    Kontr26

    [/td]


    [td]

    Text26

    [/td]


    [TD="align: right"]90,00
    [/TD]

    [/tr]


    [tr]


    [td][/td]


    [td]

    SUM: 402-023

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [TD="align: right"]213,00
    [/TD]

    [/tr]


    [tr]


    [td]

    Account2_name

    [/td]


    [td]

    402-001

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [TD="align: right"]190,00
    [/TD]

    [/tr]


    [tr]


    [td][/td]


    [td]

    402-001

    [/td]


    [TD="align: right"]2016-01-29
    [/TD]

    [td]

    inv _1

    [/td]


    [td]

    Kontr27

    [/td]


    [td]

    Text27

    [/td]


    [TD="align: right"]190,00
    [/TD]

    [/tr]


    [tr]


    [td][/td]


    [td]

    SUM: 402-001

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [TD="align: right"]190,00
    [/TD]

    [/tr]


    [/TABLE]




    Sheet2
    [TABLE="width: 521"]

    [tr]


    [td]

    Data

    [/td]


    [td]

    Invoice no

    [/td]


    [td]

    Account no

    [/td]


    [td]

    Client name

    [/td]


    [td]

    Client no

    [/td]


    [td]

    Transaction

    [/td]


    [td]

    Amount

    [/td]


    [/tr]


    [tr]


    [TD="align: right"]2016-01-29
    [/TD]

    [td]

    inv _23

    [/td]


    [td]

    402-009

    [/td]


    [td]

    Kontr23

    [/td]


    [td]

    no_Kontr23

    [/td]


    [td]

    Text23

    [/td]


    [TD="align: right"]87,00
    [/TD]

    [/tr]


    [tr]


    [TD="align: right"]2016-01-29
    [/TD]

    [td]

    inv _25

    [/td]


    [td]

    402-009

    [/td]


    [td]

    Kontr25

    [/td]


    [td]

    no_Kontr25

    [/td]


    [td]

    Text25

    [/td]


    [TD="align: right"]85,00
    [/TD]

    [/tr]


    [tr]


    [TD="align: right"]2016-01-16
    [/TD]

    [td]

    inv _2

    [/td]


    [td]

    402-010

    [/td]


    [td]

    Kontr2

    [/td]


    [td]

    no_Kontr2

    [/td]


    [td]

    Text2

    [/td]


    [TD="align: right"]62,00
    [/TD]

    [/tr]


    [tr]


    [TD="align: right"]2016-01-21
    [/TD]

    [td]

    inv _6

    [/td]


    [td]

    402-010

    [/td]


    [td]

    Kontr6

    [/td]


    [td]

    no_Kontr6

    [/td]


    [td]

    Text6

    [/td]


    [TD="align: right"]84,00
    [/TD]

    [/tr]


    [tr]


    [TD="align: right"]2016-01-27
    [/TD]

    [td]

    inv _13

    [/td]


    [td]

    402-010

    [/td]


    [td]

    Kontr13

    [/td]


    [td]

    no_Kontr13

    [/td]


    [td]

    Text13

    [/td]


    [TD="align: right"]97,00
    [/TD]

    [/tr]


    [tr]


    [TD="align: right"]2016-01-29
    [/TD]

    [td]

    inv _21

    [/td]


    [td]

    402-012

    [/td]


    [td]

    Kontr21

    [/td]


    [td]

    no_Kontr21

    [/td]


    [td]

    Text21

    [/td]


    [TD="align: right"]48,00
    [/TD]

    [/tr]


    [tr]


    [TD="align: right"]2016-01-29
    [/TD]

    [td]

    inv _22

    [/td]


    [td]

    402-023

    [/td]


    [td]

    Kontr22

    [/td]


    [td]

    no_Kontr22

    [/td]


    [td]

    Text22

    [/td]


    [TD="align: right"]92,00
    [/TD]

    [/tr]


    [tr]


    [TD="align: right"]2016-01-29
    [/TD]

    [td]

    inv _24

    [/td]


    [td]

    402-023

    [/td]


    [td]

    Kontr24

    [/td]


    [td]

    no_Kontr24

    [/td]


    [td]

    Text24

    [/td]


    [TD="align: right"]31,00
    [/TD]

    [/tr]


    [tr]


    [TD="align: right"]2016-01-29
    [/TD]

    [td]

    inv _26

    [/td]


    [td]

    402-023

    [/td]


    [td]

    Kontr26

    [/td]


    [td]

    no_Kontr26

    [/td]


    [td]

    Text26

    [/td]


    [TD="align: right"]90,00
    [/TD]

    [/tr]


    [tr]


    [TD="align: right"]2016-01-29
    [/TD]

    [td]

    inv _1

    [/td]


    [td]

    402-001

    [/td]


    [td]

    Kontr27

    [/td]


    [td]

    no_Kontr27

    [/td]


    [td]

    Text27

    [/td]


    [TD="align: right"]190,00
    [/TD]

    [/tr]


    [/TABLE]




    So I need to copy accounts from Sheet2 into Sheet1
    Then insert subtotals and group the rows (1 level grouping on subtotals (Sheet1, col B), 2 level grouping on accounts (Sheet1, col A).
    Please let me know if you need more info,


    s