You could you a simple userform to select and parse data selection into the row you clicked. First use an Event when you click in the column you want the reminder to pop up in. In this case I choose “D”. Private Sub Worksheet_Change(ByVal Target As Range) Dim arr If Not Intersect(Target, Range(“D:D”)) Is Nothing … Read more
Here is a working code of a VBA function that fits your needs: Sub RedistributeData() Dim X As Long, LastRow As Long, A As Range, Table As Range, Data() As String Const Delimiter As String = vbLf Const DelimitedColumn As String = “B” Const TableColumns As String = “A:B” Const StartRow As Long = 1 … Read more
If able to sort by date within task and to ensure that any subsequent sheet starts at a new task and the last ColumnB and ColumnC values on each sheet are repeated in the row immediately below, a formula might suit: =IF(A1<>A2,A1&”|”&VLOOKUP(A1,A:B,2,0)&”|”&C1&”|”&B2-VLOOKUP(A1,A:B,2,0),””) with the columns where the formula output is evident filtered to exclude blanks, … Read more
Why not a simple formula? Presuming your range is C1:H3and the first value-to-check is in C6 =COUNTIF($C$1:$H$3, C$6) Will produce the expected result 1 solved How to count the occurrences of cell contents?
I reversed it, I made it into an if function, if anyone else is wanting to know how to get a steady Plus/Minus from Gains and Losses this formula worked for me =IF(C4<=B4,1-B4/C4,C4/B4-1) 5 solved Calculate Gain Loss Excel Percentage [closed]
try this Option Explicit Sub main() Dim data As Variant With Range(“A1”).CurrentRegion data = .Resize(.Rows.Count – 1, 4).Offset(1).Value End With ReDim wynik(1 To UBound(data)) As Variant Dim i As Variant For i = 1 To UBound(data) data(i, 3) = Format(data(i, 3), “yyyy-mm-dd”) wynik(i) = Join(Application.index(data, i, 0), “_”) Next Range(“E2”).Resize(UBound(data)).Value = Application.Transpose(wynik) End Sub 3 … Read more
Here is an example for columns A and B. Insert this event macro in the worksheet code area: Private Sub Worksheet_Change(ByVal Target As Range) Dim A As Range, OldValue As Variant, NewValue As Variant, Delta As Variant Set A = Range(“A:A”) If Intersect(Target, A) Is Nothing Then Exit Sub If Target.Count > 1 Then Exit … Read more
With the current code you have you don’t even need to use a counter. Instead you can load FoundAt into an array and then use the Ubound to get the total count. Note you have to add 1 since the array is 0 based. Add these lines before your final Msgbox Dim iCount() as String … Read more
add this into a seperate module Function sixdigits(amount) As String Dim strAmount As String strAmount = Replace(Left(amount, 6), “.”, “”) ‘ use replace and left to create a string of 6 characters Do Until Len(strAmount) >= 6 ‘ loop until 6+ strAmount = “0” & strAmount Loop: sixdigits = strAmount End Function then in your … Read more
You can add a column where you could check if PPFROM (the value) is the min of the group (ARTICLE). Please adjust the references inside the formula. This is the formula: =IF(MINIFS($E$4:$E$10;$M$4:$M$10;M4)=E4;”Keep”;”Delete”) After that you can filter by the new column and delete the rows marked with “Delete”. solved Deleting duplicates, keeping lowest value in … Read more
Try the code below, explanations inside the code’s comments: Option Explicit Sub ExtractAfterShowOnly() Dim WordsArr() As String Dim i As Long Dim MatchString As String ‘ use Split to read each section between “https://stackoverflow.com/” as arra element WordsArr = Split(Range(“A2”).Value2, “https://stackoverflow.com/”) ‘ loop through array For i = 1 To UBound(WordsArr) ‘ if there’s a … Read more
You will need a helper column with this formula: =24*(SUMPRODUCT((TEXT(ROW(INDEX(AAA:AAA,$F$1):INDEX(AAA:AAA,$F$2)),”dddd”)=A1)*(C1-B1))-IF(TEXT($F$1,”dddd”)=A1,MOD($F$1,1)-B1,0)-IF(TEXT($F$2,”dddd”)=A1,C1-MOD($F$2,1),0)) Then sum that column. Here it is in one formula using NETWORKDAYS.INTL =IF(DATEDIF(F1,F2,”d”)>1,NETWORKDAYS.INTL(F1+1,F2-1,”0000011″)*12+NETWORKDAYS.INTL(F1+1,F2-1,”1111101″)*4,0)+IF(DATEDIF(F1,F2,”d”)>0,(MOD(F2,1)-IF(WEEKDAY(F2,2)<6,TIME(7,0,0),TIME(9,0,0)))*24+(IF(WEEKDAY(F1,2)<6,TIME(19,0,0),TIME(13,0,0))-MOD(F1,1))*24,(F2-F1)*24) 4 solved how to calculate time difference in excel working hours only
Sounds like you just need a countifs formula, so the yellow cell would have: =COUNTIFS($B$2:$B$10001,”AAA”,$C$2:$C$10001, “AA”) 4 solved Calculate a Generator Matrix with VBA [closed]
You have to set the variable: Sub bordersTest() Dim theRange As Range Set theRange = Range(ActiveCell, ActiveCell.End(xlDown).End(xlToRight)) theRange.Select End Sub 1 solved How to overcome “object variable or with block variable not set” in excel VBA? [duplicate]
Why use vba? Assuming all worksheet are the same format, select all worksheet tabs and make a change to one. It would take you longer to write the code then to just do it. solved How to remove borders in first row in multiple sheets using vba