Hello,
I'm searching for code which will show me who has the file opened to write. I have it on a network drive with 9 people having access. Can you help me?
s.
Hello,
I'm searching for code which will show me who has the file opened to write. I have it on a network drive with 9 people having access. Can you help me?
s.
Re: add to cell after select from form
I managed to change the code so that the user can update a string in the cell but on data input (pressing ENTER) it doubles the existing string
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)
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...............
Re: 255 character limit with VBA array formula
unfortunately gives me 0.
I tried to check via Immediate window but don't know how (for array formula)
Anyway thanks for help
s.
Hello.
Could you please help.
Below code is working
[/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
[/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
many thanks for help. It worked!!!
Re: Autofilter not refreshing on change
yes. I must have othe bugs in the code
Sub insert()
Dim i As Long, j As Long, k As Long, no_rows_filter As Integer
With Application
.DisplayAlerts = False
.ScreenUpdating = False
.EnableEvents = False
.Calculation = xlManual
LastRow_dane = Worksheets("dane").Range("A" & Rows.Count).End(xlUp).Row
wbk.Worksheets("dane").Activate
With ActiveSheet.ListObjects.Add(xlSrcRange, ActiveSheet.Range(Cells(1, 1), Cells(LastRow_dane, 7)), , xlYes)
.Name = "Tabela1"
.TableStyle = "TableStyleLight1"
End With
LastRow_konta = Worksheets("konta").Range("A" & Rows.Count).End(xlUp).Row
Sheets("konta").Range("C4:G" & LastRow_konta).Clear
On Error Resume Next
Worksheets("konta").Columns("A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
ost = Worksheets("konta").Cells(.Rows.Count, "A").End(xlUp).Row
Dim accounts() As String
Dim sAccNo As String
Dim rFind As Range, rCopy As Range
With Worksheets("dane")
ostD = .Cells(.Rows.Count, "C").End(xlUp).Row
For i = 8 To ostD
accounts() = Split(Worksheets("konta").Range("B" & i).Value, ",")
For j = 0 To UBound(accounts)
sAccNo = Trim(accounts(j))
Set rFind = .Cells.Find(sAccNo)
If Not rFind Is Nothing Then
.ShowAllData
.Range("A1:G" & ostD).AutoFilter Field:=3, Criteria1:=rFind
no_rows_filter = .Range("A1:G" & ostD).SpecialCells(xlCellTypeVisible).Rows.Count - 1
Sheets("konta").Rows(i + 1).EntireRow.Resize(no_rows_filter).Insert
.Range("I2").Value = rFind
Dim r As Range
Set r = Sheets("konta").Range("C" & i + 1 & ":I" & i + 1)
.Range("Tabela1[#All]").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:= _
.Range("I1:I2"), CopyToRange:=r, Unique:=False
r.Delete xlShiftUp
End If
Next j
i = i + no_rows_filter + 1
Next i
End With
.DisplayAlerts = True
.ScreenUpdating = True
.EnableEvents = True
.Calculation = xlAutomatic
End With
End Sub
Display More
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)
For i = 8 To ostD
accounts() = Split(Worksheets("k").Range("B" & i).Value, ",")
For j = 0 To UBound(accounts)
sAccNo = Trim(accounts(j))
Set rFind = .Cells.Find(sAccNo)
If Not rFind Is Nothing Then
.AutoFilterMode = True
.Range("A1:G" & ostD).AutoFilter Field:=3, Criteria1:=rFind
no_filtered_rows = .Range("A1:G" & ostD).SpecialCells(xlCellTypeVisible).Rows.Count - 1
Display More
.....
Would be grateful for help
Re: copy rows from another sheet
Hello Apo,
did you have time to look into my subject?
Re: copy rows from another sheet
sure, I understand. Just wanted to know if you come back one day. Thanks !
Re: copy rows from another sheet
Hello "apo" are you able to provide any solution for my code?
Re: copy rows from another sheet
thanks. File attachedforum.ozgrid.com/index.php?attachment/70734/
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"]
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]
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]
402-009
[/td]
[TD="align: right"]2016-01-29
[/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]
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]
402-010
[/td]
[TD="align: right"]2016-01-16
[/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]
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]
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]
402-012
[/td]
[TD="align: right"]2016-01-29
[/TD]
inv _21
[/td][td]Kontr21
[/td][td]Text21
[/td]
[TD="align: right"]48,00
[/TD]
SUM: 402-012
[/td][td][/td][td][/td][td][/td][td][/td]
[TD="align: right"]48,00
[/TD]
402-023
[/td]
[TD="align: right"]2016-01-29
[/TD]
inv _22
[/td][td]Kontr22
[/td][td]Text22
[/td]
[TD="align: right"]92,00
[/TD]
402-023
[/td]
[TD="align: right"]2016-01-29
[/TD]
inv _24
[/td][td]Kontr24
[/td][td]Text24
[/td]
[TD="align: right"]31,00
[/TD]
402-023
[/td]
[TD="align: right"]2016-01-29
[/TD]
inv _26
[/td][td]Kontr26
[/td][td]Text26
[/td]
[TD="align: right"]90,00
[/TD]
SUM: 402-023
[/td][td][/td][td][/td][td][/td][td][/td]
[TD="align: right"]213,00
[/TD]
Account2_name
[/td][td]402-001
[/td][td][/td][td][/td][td][/td][td][/td]
[TD="align: right"]190,00
[/TD]
402-001
[/td]
[TD="align: right"]2016-01-29
[/TD]
inv _1
[/td][td]Kontr27
[/td][td]Text27
[/td]
[TD="align: right"]190,00
[/TD]
SUM: 402-001
[/td][td][/td][td][/td][td][/td][td][/td]
[TD="align: right"]190,00
[/TD]
[/TABLE]
Sheet2
[TABLE="width: 521"]
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]
inv _23
[/td][td]402-009
[/td][td]Kontr23
[/td][td]no_Kontr23
[/td][td]Text23
[/td]
[TD="align: right"]87,00
[/TD]
[TD="align: right"]2016-01-29
[/TD]
inv _25
[/td][td]402-009
[/td][td]Kontr25
[/td][td]no_Kontr25
[/td][td]Text25
[/td]
[TD="align: right"]85,00
[/TD]
[TD="align: right"]2016-01-16
[/TD]
inv _2
[/td][td]402-010
[/td][td]Kontr2
[/td][td]no_Kontr2
[/td][td]Text2
[/td]
[TD="align: right"]62,00
[/TD]
[TD="align: right"]2016-01-21
[/TD]
inv _6
[/td][td]402-010
[/td][td]Kontr6
[/td][td]no_Kontr6
[/td][td]Text6
[/td]
[TD="align: right"]84,00
[/TD]
[TD="align: right"]2016-01-27
[/TD]
inv _13
[/td][td]402-010
[/td][td]Kontr13
[/td][td]no_Kontr13
[/td][td]Text13
[/td]
[TD="align: right"]97,00
[/TD]
[TD="align: right"]2016-01-29
[/TD]
inv _21
[/td][td]402-012
[/td][td]Kontr21
[/td][td]no_Kontr21
[/td][td]Text21
[/td]
[TD="align: right"]48,00
[/TD]
[TD="align: right"]2016-01-29
[/TD]
inv _22
[/td][td]402-023
[/td][td]Kontr22
[/td][td]no_Kontr22
[/td][td]Text22
[/td]
[TD="align: right"]92,00
[/TD]
[TD="align: right"]2016-01-29
[/TD]
inv _24
[/td][td]402-023
[/td][td]Kontr24
[/td][td]no_Kontr24
[/td][td]Text24
[/td]
[TD="align: right"]31,00
[/TD]
[TD="align: right"]2016-01-29
[/TD]
inv _26
[/td][td]402-023
[/td][td]Kontr26
[/td][td]no_Kontr26
[/td][td]Text26
[/td]
[TD="align: right"]90,00
[/TD]
[TD="align: right"]2016-01-29
[/TD]
inv _1
[/td][td]402-001
[/td][td]Kontr27
[/td][td]no_Kontr27
[/td][td]Text27
[/td]
[TD="align: right"]190,00
[/TD]
[/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