Lodbrok.be
Be aware: all versions of Excel contain far more rows than the Application can ever handle in a decent way. Newer versions, far exceed [with millions of rows] the internal Applications capabilities of 65536. Excel can only track 65536 dependencies to unique references for automatic calculation and filtering. Need more? Use access or a real database!
See also google "excel bad practices" -- some are a bit silly, but not the ones above :-). Off-course, there are situations where the best practices may not apply; but only if you know why and did think about it without getting tricked by convenience.
weeknumber:
=WEEKNUM(G4;2)
=INT((TODAY()-(DATE(YEAR(TODAY());1;1)-(WEEKDAY(DATE(YEAR(TODAY());1;1))-2)))/7)+1
in-cell graph (does not work wit negative numbers):
=REPT("|";A2*A1)
het verschil:
delta=B-A%delta=-(1-B/A)*100OR((B3/A3)-1)*100<= het verschil in procent pct(%)=B/A*100
alternating rowcolors:
=MOD(ROW();2)=IF(OR(((MOD(ROW();4))=1);((MOD(ROW();4))=2));1;0)= bad math but it works=MOD(MOD(ROW();4);3)= bad math but it works
convert EURO format:
=VALUE(IF(ISERR(FIND("-";A1));LEFT(A1;FIND(" ";A1)-1);CONCATENATE("-";LEFT(A1;FIND("-";A1)-1))))
convert example:
A B C D E F G
1 text string: position(-) length extract number? value number?
2 formula: =FIND("-",A3) =LEN(A3) =MID(A3,B3+1,C3) =ISNUMBER(D3) =VALUE(D3) =ISNUMBER(F3)
3 apple - 2,100,000 7 17 2,100,000 FALSE 2100000 TRUE
4 red orange - 915,000 12 20 915,000 FALSE 915000 TRUE
in this example =MID(A3,B3+1,C3) could also be written as =RIGHT(A3,C3-B3-1).
=MID(E5;1;FIND("-";E5)-1)
=MID(E5;FIND("-";E5)+1;LEN(E5))
encapsulate left and right values xxxxx = MID(...)
=IF(ISBLANK(E5);0;VALUE(xxxxx))
Paste in appropriate excel sheet object
Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Row As Long
Dim myrange As Range
Cells.Interior.ColorIndex = xlNone
Cells.Borders.ColorIndex = xlNone
Row = Target.Row
Set myrange = Range("A" & Row, "II" & Row)
myrange.Interior.ColorIndex = 15
myrange.Borders.ColorIndex = 1
End Sub
Paste in appropriate excel sheet object
Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim RngRow As Range
Dim RngCol As Range
Dim RngFinal As Range
Dim Row As Long
Dim Col As Long
Cells.Interior.ColorIndex = xlNone
Row = Target.Row
Col = Target.Column
Set RngRow = Range("A" & Row, Target)
Set RngCol = Range(Cells(1, Col), Target)
Set RngFinal = Union(RngRow, RngCol)
RngFinal.Interior.ColorIndex = 6
End Sub
see:
Option Explicit Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) If ActiveCell.Interior.ColorIndex = xlNone Then ActiveCell.Interior.ColorIndex = 40 ' tan...sort of Else ActiveCell.Interior.ColorIndex = xlNone End If End Sub
Macro to hide columns and the form Scroll Bar objects
Sub hide1()
Dim counter As Long
'Range("hideme").Columns(1).Select
' When columns is applied to a Range object that's a multiple-area selection,
' this property returns columns from only the first area of the range.
For counter = 1 To 4
Range("hideme").Columns(counter).Select
Selection.EntireColumn.Hidden = True
Next counter
Range("A1").Select
Dim sOptBut As Shape
For Each sOptBut In ActiveSheet.Shapes
If sOptBut.FormControlType = xlScrollBar Then
sOptBut.Visible = msoFalse
End If
Next sOptBut
End Sub
sort columns individually of each other for box and wiskers diagram sample
Sub SORTCOL()
' sort columns individually of each other for box and wiskers diagram sample
' make sure data samples are transposed from top to bottom
' select top row from left to right accross the dataset
' 5 6 7 8 <= select from left to right
' 4 4 8 9
' 5 8 6 7
For Counter = 1 To Selection.Count
Columns(ActiveCell.EntireColumn.AddressLocal).Select
Selection.Sort Key1:=Range(ActiveCell.AddressLocal), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
' if headers ar posing problems user 'Header:=xlNo' instead of 'Header:=xlGuess'
ActiveCell.Next.Select
Next Counter
End Sub
Times are stored internally by Excel as fractions of a 24-hour day, so that 12:00:00 would be stored as 0.5 and 18:00:00 as 0.75. Consequently, to convert a time to seconds, you must multiply by the number of seconds in a day - it is easier to remember this as *24*60*60 rather than the actual number (86400).
Tools > Macro > VB Editor
actions: double click on sheet2 > paste below subroutine and [run] > then switch back to sheet2 in excel to display results.
Sub test()
Dim EnvString As String
Indx = 1
Do
EnvString = Environ(Indx)
Cells(Indx, 1) = EnvString
Indx = Indx + 1
Loop Until EnvString = ""
End Sub
Get only 1 var and optionally show in msgbox
Dim ENVIR As String
ENVIR = Environ("SNC_LIB")
MsgBox ENVIR
' find text "END OF TODO" and jump to it
'Cells.Find(What:="END OF TODO", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
' => alternative to above: goto last row with data
'Range("a65536").End(xlUp).Select
'lastrow = ActiveCell.Row
' => alternative to above: no select required
lastrow = Range("a65536").End(xlUp).Row
' => example usage for lastrow: Range("B" & lastrow).Select
' => example usage for lastrow: Range("A" & lastrow & ":B" & lastrow).Select
Cells.Find(What:="sometext", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
True, SearchFormat:=False).Activate
curcol = Left(ActiveCell.EntireColumn.Address(columnabsolute:=False), Application.WorksheetFunction.Find(":", ActiveCell.EntireColumn.Address(columnabsolute:=False)) - 1)
AddIns("RowLiner 4.1").Installed = True
AddIns("RowLiner 4.1").Installed = False
attach shortcut ctrl key combination: tools > macro > macros > select macro > options
Sub row_up()
curcol = ActiveCell.EntireColumn.Address(columnabsolute:=False)
curcolleftsize1 = (Len(curcol) - 1) / 2
curcolnum = Left(curcol, curcolleftsize1)
currow = ActiveCell.EntireRow.Address(rowabsolute:=False)
currowleftsize1 = (Len(currow) - 1) / 2 ' currowleftsize2 = Application.WorksheetFunction.Find(":", currow) - 1 'alternative
currownum = Left(currow, currowleftsize1)
currownum_finalposition = currownum - 1
currownum_insertrow = currownum - 1
Rows(currow).Select ' Range(currow).Select 'alternative to row select
Selection.Cut
Rows(currownum_insertrow & ":" & currownum_insertrow).Select
Selection.Insert Shift:=xlDown
Range(curcolnum & currownum_finalposition).Select
End Sub
Sub row_down()
curcol = ActiveCell.EntireColumn.Address(columnabsolute:=False)
curcolleftsize1 = (Len(curcol) - 1) / 2
curcolnum = Left(curcol, curcolleftsize1)
currow = ActiveCell.EntireRow.Address(rowabsolute:=False)
currowleftsize1 = (Len(currow) - 1) / 2 'currowleftsize2 = Application.WorksheetFunction.Find(":", currow) - 1 'alternative
currownum = Left(currow, currowleftsize1)
currownum_finalposition = currownum + 1
currownum_insertrow = currownum + 2
Rows(currow).Select 'Range(currow).Select 'alternative to row select
Selection.Cut
Rows(currownum_insertrow & ":" & currownum_insertrow).Select
Selection.Insert Shift:=xlDown
Range(curcolnum & currownum_finalposition).Select
End Sub
A B C 1 Filesystem curr_GB new_GB 2 /sapmnt 1 1 3 /usr/sap 4 5 4 /sapdb 1 1 5 /usr/sap/ACC 5 7 6 /sapmnt/ACC 3 4 7 /sapdb/ACC 20 29 8 SUM 34 50 B8=SUM(B2:B7) C8=<new total> C7=ROUNDDOWN(B7*C$8/B$8;0)
=YEAR(F2)&"Q"&LOOKUP(MONTH(F2);{1;4;7;10};{1;2;3;4})
1.Type a number, such as 395.54 into cell D1 in Excel. 2.Press and hold down the Shift key on the keyboard 3.Press and hold down the Down Arrow key on the keyboard to extend the cell highlight from cell D1 to D7. 4.Release both keys. 5.Press and hold down the Ctrl key on the keyboard. 6.Press and release the " D " key on the keyboard. 7.Cells D2 to D7 should now be filled with the same data as cell D1.
20/03/2012 13:48 - 10/02/2012 11:49 = 937:58 where formula for 937:58 is =B1-B2 with formatting [u]:mm or [hh]:mm you can also use the function =days360(B1;B2) and format as number =DAYS360(B2;IF(B1="";NOW();B1))
=TRIM(CLEAN(A2))
Function HLink(rng As Range) As String 'extract URL from hyperlink 'posted by Rick Rothstein If rng(1).Hyperlinks.Count Then HLink = rng.Hyperlinks(1).Address End Function
Set new conditional format rule and use a formula
=and(ISNUMBER(SEARCH($C$1;A4));$C$1<>"")
REF: http://easy-excel.com/create-a-search-field-in-excel-in-5-minutes/
=MATCH("*"&E2&"*";'tabsheet'!F:F;0)
Function ShowF(Rng As Range) ShowF = Rng.Formula End Function
Sub SetHexColors()
Dim i, LastRow
LastRow = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LastRow
Cells(i, "B").Interior.Color = HEXCOL2RGB(Cells(i, "A"))
Next
End Sub
Public Function HEXCOL2RGB(ByVal HexColor As String) As String
Dim Red As String, Green As String, Blue As String
HexColor = Replace(HexColor, "#", "")
Red = Val("&H" & Mid(HexColor, 1, 2))
Green = Val("&H" & Mid(HexColor, 3, 2))
Blue = Val("&H" & Mid(HexColor, 5, 2))
HEXCOL2RGB = RGB(Red, Green, Blue)
End Function
# simply multiply it by 24. Set formatting to number and two places of decimal. =(B1-A1)*24
PS: and back... /24 for to convert decimal hours back to days and format as time field.
http://www.extendoffice.com/documents/excel/771-excel-fill-blank-cells-with-value-above.html
calculate AVERAGE
of 1 column to the left of the current column "COLUMN()-1"
starting 2 rows down "ROW()+2"
for the next 10 rows, ending 12 rows down "ROW()+2+10"
=AVERAGE(INDIRECT(ADDRESS(ROW()+2;COLUMN()-1;4)):INDIRECT(ADDRESS(ROW()+2+10;COLUMN()-1;4)))
notes
ADDRESS(;;4) = returns relative adresses like A1 instead of absolute $A$1
INDIRECT evaluates the ADDRESS as a reference, otherwise the result of ADDRESS would be seen as a text string.
find row "MATCH"
containing next string "*Ref#"
down in current column "COLUMN()"
starting 2 rows down "ROW()+2"
ending at row "1000"
=ROW()+1+MATCH("*Ref#";INDIRECT(ADDRESS(ROW()+2;COLUMN();4)):INDIRECT(ADDRESS(1000;COLUMN();4));0)
notes
ADDRESS(;;4) = returns relative adresses like A1 instead of absolute $A$1.
INDIRECT evaluates the ADDRESS as a reference, otherwise the result of ADDRESS would be seen as a text string.
MATCH(;;0) = finds the first value that is exactly equal to the lookup value.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim keycells As Range
Set keycells = Range("b4")
If Not Application.Intersect(keycells, Range(Target.Address)) _
Is Nothing Then
If WorksheetFunction.CountA(Range("b4")) = 0 Then
On Error Resume Next
ActiveSheet.ShowAllData
Else
On Error Resume Next
ActiveSheet.ShowAllData
ActiveSheet.Range("$A$4:$DO$5000").AutoFilter Field:=3, Criteria1:="TRUE"
End If
End If
End Sub
https://support.office.com/en-us/article/Number-format-codes-5026bbd6-04bc-48cd-bf33-80f18b4eae68
if the date is not set and therefore 0 you can use the custom format: yyyy-mm-dd;;;@
Month in English (409), no matter what language setting you have:
[$-409]mmm;@
=SUMIFS(I:I;C:C;"="&C11)
...sums all fields in i:i if the field in column c:c is the same as on your current line in row 11 (field c11).
AM/PM date week start end hh:mm TOT/day h h/day h/week
AM 5/09/2016 36 9:14 15:59 6:45 6,75 / /
PM 5/09/2016 36 17:14 20:55 3:41 10:26 3,68 10,43 /
AM 6/09/2016 36 11:04 12:00 0:56 0,93 / /
PM 6/09/2016 36 12:30 17:31 5:01 5:57 5,02 5,95 /
AM 7/09/2016 36 9:04 12:00 2:56 2,93 / /
PM 7/09/2016 36 12:30 18:35 6:05 9:01 6,08 9,02 /
AM 8/09/2016 36 9:17 12:00 2:43 2,72 / /
PM 8/09/2016 36 12:30 18:41 6:11 8:54 6,18 8,90 /
AM 9/09/2016 36 9:14 12:00 2:46 2,77 / /
PM 9/09/2016 36 12:30 18:45 6:15 9:01 6,25 9,02 43,32
AM 12/09/2016 37 9:14 15:59 6:45 6,75 / /
PM 12/09/2016 37 17:14 20:55 3:41 10:26 3,68 10,43 /
AM 13/09/2016 37 11:04 12:00 0:56 0,93 / /
PM 13/09/2016 37 12:30 17:31 5:01 5:57 5,02 5,95 16,38
=INT((B2-(DATE(YEAR(B2),1,1)-(WEEKDAY(DATE(YEAR(B2),1,1))-2)))/7)
=E2-D2
=IF(B2=B1,SUM(F1:F2),"") <=== this causes problems if you insert or have +- é lines, use indirect!
=(E2-D2)*24
=IF(B2<>INDIRECT(ADDRESS(ROW(B2)+1,COLUMN(B2),4)),SUMIFS(H:H,B:B,"="&B2),"/")
=IF(C2<>INDIRECT(ADDRESS(ROW(C2)+1,COLUMN(C2),4)),SUMIFS(I:I,C:C,"="&C2),"/")
To extract the integer value, use the formula: =INT(A1) To extract the fractional value, use the formula: =MOD(A1,1) or =(A1-INT(A1))
http://excelsemipro.com/2013/02/extracting-integers-and-fractions-in-microsoft-excel/
the selection will cycle through the possible assignments...
=E7 =$E$7 =E$7 =$E7
Conditional range with following formula where A1 is the list drop down for values in C:C
=$C2=$A$1
This is usually done with a VBA solution but we can do it without VBA.
notes:
=ROW()=CELL("row") <=optional, set conditional format for example to: "red top border"
=COLUMN()=CELL("col") <=optional
Application.ScreenUpdating = True <=optional, alternate use F9 to refresh
Automate the above (for row with top red border) - oh the irony of implementing a non-VBA solution with a VBA macro :-)
Sub SetHighlights()
Columns("A:AZ").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=ROW()=CELL(""row"")"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Borders(xlTop)
.LineStyle = xlContinuous
.Color = -16776961
.TintAndShade = 0
.Weight = xlThin
End With
Selection.FormatConditions(1).StopIfTrue = False
Range("A1").Select
End Sub
Alternatives:
create macro; add macro option for shortcut ctrl+t (tab).
Sub ShowSheetLists()
Application.CommandBars("Workbook tabs").ShowPopup
End Sub
Saving this to be an add-in.
ATTENTION, custom add-ins may be restricted, see here for policy and registry keys: https://support.microsoft.com/en-us/help/2733070/no-add-ins-loaded-due-to-group-policy-settings-for-office-2013-and-off (typical error: The add-in you have selected is disabled by your system administrator.)
Application.Dialogs(xlDialogFormulaFind).Show , 2
Apply conditional format with formula to column B:B to find the values in column A:A:
=ISNUMBER(MATCH(B1;$A:$A;0))
Basicaly INDEX&MATCH can replace VLOOKUP like so:
=VLOOKUP(A1;I:J;2) is the same as =INDEX(J:J;MATCH(A1;I:I;0))
But when using INDEX&MATCH you can use multiple criteria using the flexibility of MATCH:
Contrary to what i found on the internets, this does not work for me: =INDEX(G:G,MATCH(1,(E:E=A1)*(F:F=B1),0)) <=== DO NOT press [ENTER]... press [ctrl]+[shift]+[enter] But the internets are full of alternatives, and this does work for me: =INDEX(G:G;MATCH(A1&B1;E:E&F:F;0)) <=== DO NOT press [ENTER]... press [ctrl]+[shift]+[enter] The below lookup is working great but is not scalable; it can truly slow down your excel to a grinding halt!! =INDEX(sheet!J:J&"d, "&sheet!G:G;MATCH(G172;sheet!D:D;0)) speedup is to use 2 columns, like so: =INDEX(sheet!J:J;MATCH(G172;sheet!D:D;0)) =INDEX(sheet!G:G;MATCH(G172;sheet!D:D;0))
Sub TogglePageBreaks() ActiveSheet.DisplayPageBreaks = Not ActiveSheet.DisplayPageBreaks End Sub
Sub ApplyF2()
Selection.Value = Selection.FormulaR1C1
End Sub
PS: FormulaR1C1 function is magic to me - so don't ask.
=subtotal(3;range)
To resolve future issues:
ribbon tab:Home ribbon section: Styles Right-click: "NORMAL"-style Select: Modify Click: Format button tab: Number select: General
It did the trick with SOME cells, but most cells (that used to be in "General" format) remained in "Date" format.
UNTESTED: If you still have some cells in date format you want to change back to general:
1- Right-click on any black cell 2- Choose "Format Cells..." 3- In the "Number" tab, select "Custom" 4- Delete formats starting with "[$-409]..." of whatever the format is that is causing issues
UNTESTED: you can also try this
Sub CleanStyles() Dim sty As Style, wbTemp As Workbook ' First, remove all styles other than Excel's own. ' they may have arrived from pasting from other workbooks For Each sty In ThisWorkbook.Styles If Not sty.BuiltIn Then sty.Delete Next 'Second, revert the remaining styles to Excel's default for a new workbook Set wbTemp = Workbooks.Add Application.DisplayAlerts = False ThisWorkbook.Styles.Merge wbTemp wbTemp.Close SaveChanges:=False Application.DisplayAlerts = True End Sub
UNTESTED: another simple solution
click "cell styles" select "merge styles". then select an existing file that has the desired normal format. I simply opened a new spreadsheet and saved it, then used this file as the merge file. it fixed all the formatting in the messed up file. (I did save a spare version of my target file before doing this.)
UNTESTED related?
...alternatively we need a vbs macro that can: convert all empty fields having a date format to general format.
Option Explicit
Private WithEvents App As Application
Private Sub Class_Initialize()
Set App = Application
End Sub
Private Sub App_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Dim Ctype As String
If Selection.Cells.Count > 1 Then
Ctype = "Multiple cells"
Else
Ctype = Target.NumberFormat
If Ctype = "General" Then
If Target.Value = Target.Text Then
Ctype = "General(TEXT)"
Else
Ctype = "General(NUM)"
End If
ElseIf Ctype = "@" Then
Ctype = "@(TEXT)"
End If
End If
Application.StatusBar = Ctype
End Sub
Option Explicit
Dim oApp As clsAppEvents
Sub InitAppEvents()
Set oApp = New clsAppEvents
End Sub
'sample 1 Debug.Print Format(Now(), "yyyyMMddThhmmss") & " debug line trigger 999 " & Sh.Parent.Name & "/" & Sh.Name 'sample2 Private Sub somemacro(ByVal Target As Range) Debug.Print Format(Now(), "yyyyMMddThhmmss") & " selection on: " & Target.Address(0, 0, , True)
To display messages in VBA editor: view > immediate window. Sample2 output:
(personally using excel 2016 power query)
You could achieve this with a separate query which would export the notes and the corresponding item code to a separate table. This table would be merged with your source data so that you would include the previously captured notes each time you perform a refresh.
Be aware: a more simple solution is possible, by sorting the table "CommentDate". This cannot be guaranteed and is not desired in this use-case; So a helper column is used for combination of 2 criteria.
1) We want in table TableView1 to add the most recent comment for column Number.
2) Sample lookup TableComments1 with comments history:
T.Number | Comment | CommentDate | Helper1 ==============+==============+=============+=================== SCTASK0073347 | correction | 22/07/2018 | 43303SCTASK0073347 SCTASK0073347 | find this! | 25/07/2018 | 43306SCTASK0073347 SCTASK0073347 | update 2 | 21/07/2018 | 43302SCTASK0073347 TASK9999 | comment | 25/07/2018 | 43306TASK9999
3) Formula breakdown
The formula for the Helper column just does CONCATENATE 2 columns: =[@CommentDate]&[@[T.Number]] Lets say we want: SCTASK0073347 Note: in the helper column we have value "43306SCTASK0073347"; where "43306" is the numerical representation of date "25/07/2018". This will search for a match in the "Number" and return the most recent "Date": <<MostRecentDate>> =MAXIFS(TableComments1[CommentDate];TableComments1[T.Number];TableView1[@Number]) (abbreviated to for readability) ..returning "25/07/2018". This will search for a combination of above formula <<MostRecentDate>> & "Number" in the Helper column: =MATCH(<<MostRecentDate>>&TableView1[@Number];TableComments1[Helper1];0) ..returning row (2) with helper table "43306SCTASK0073347". From this point forward we use MATCH (now returning the wanted row) and INDEX in a style VLOOKUP would do: =INDEX(TableComments1[Comment];MATCH(<<MostRecentDate>>&TableView1[@Number];TableComments1[Helper1];0)) ...returning the wanted column with desired comment "find this!". Full formula, includes IFNA to clear blank lookups with no comments: =IFNA(INDEX(TableComments1[Comment];MATCH(MAXIFS(TableComments1[CommentDate];TableComments1[T.Number];TableView1[@Number])&TableView1[@Number];TableComments1[Helper1];0));"")
application.macrooptions macro:="showsheetlists", hasshortcutkey:=true, shortcutkey:="t"
application.onkey "%{c}", "subroutine"
Sub CreateShortcuts()
Application.OnKey "^%{c}", "InsertDate"
Application.OnKey "^%{i}", "InsertDate"
Application.OnKey "^+{I}", "InsertDate"
End Sub
Sub DeleteShortcuts()
Application.OnKey "^%{c}"
Application.OnKey "^%{i}"
Application.OnKey "^+{I}"
End Sub
see also
Attribute VB_Name = "Module1"
Attribute Filtervalue_TableComments1.VB_Description = "macro filter value in comments table"
Attribute Filtervalue_TableComments1.VB_ProcData.VB_Invoke_Func = "F\n14"
Attribute Update_TableComments1.VB_ProcData.VB_Invoke_Func = "u\n14"
Attribute InsertDate.VB_ProcData.VB_Invoke_Func = "i\n14"
Application.OnKey "^%{c}", "InsertDate"
Application.OnKey "^%{i}", "InsertDate"
Application.OnKey "^+{I}", "InsertDate"
Ignore the first 2 lines for now... the resulting shortcuts (using 2 different assignment methods) are
ctrl+shift+F Sub Filtervalue_TableComments1()
ctrl+u Sub Update_TableComments1()
ctrl+i Sub InsertDate()
ctrl+alt+c Sub InsertDate()
ctrl+alt+i Sub InsertDate()
ctrl+shift+I Sub InsertDate()
context: https://www.rondebruin.nl/win/s9/win002.htm
Error:
"VBIDE.VBComponent user defined type not defined".
In VBA, go to the Tools menu, choose References, and select:
"Microsoft Visual Basic For Applications Extensibility Library".
Error:
91: "object variable or with block variable not set"
Are you developing an add-in? if so temporary set:
workbook > property > isaddin = False
Error:
cannot execute, no access trust: "programmatic access to Visual Basic Project is not trusted"
enable access to the VBA Object Model
excel (2016) > trust center > macro settings > Developer Macro settings > Trust access to the VBA project object model
this option may be greyed out, goto registry and search for diabled (0) accessvbom:
Registry Hive HKEY_CURRENT_USER Registry Path software\policies\microsoft\office\16.0\excel\security Value Name accessvbom Value Type REG_DWORD Enabled Value 1 Disabled Value 0
change to 1 and restart excel.
it works!
https://weeknumber.net/how-to/excel
example C1=2020 (year) D1=1 (monday) D3=2 (week 2) (custom date format:mmm) Jan = DATE($C$1, 1, -3 + 7 * D3 - WEEKDAY(DATE($C$1, 1, 4), 2) + $D$1)
assumption
you are using a typical European localisation using , for decimals and . for grouping thousands.
field A1 = "5.000.000.000,12 €" (5000000000,1234 formatted as EURO currency or number with 2 decimal places)
field A2 = " £" (post-fix string, formatted as text)
=SUBSTITUTE(LEFT(TEXT(A1;"#.##0,00"); FIND(",";TEXT(A1;"#.##0,00")) -1);".";",") & "." & MID(TEXT(A1;"#.##0,00"); FIND(",";TEXT(A1;"#.##0,00")) +1;10) &A2
result = "5,000,000,000.12 £"
Unfortunately, The result is only a "representation" for display usage as it is formatted as text. The way the formula works is that we identify a split for decimal notation and substitute left string grouping before concatenating new decimal, right string and post-fix.
PS: I assume the new LET function for Office 365 insiders could simplify this. For now i leave it
This formula searches down towards the next formula and sets the range automatically between the current and next formula in the column.
=AVERAGE( A3 : INDIRECT( "R" & ROW()-1+IFNA( MATCH("=*";FORMULATEXT(A3:INDIRECT("R"&$A$1&"C"&COLUMN();FALSE));0); IFNA( MATCH("x";A3:INDIRECT("R"&$A$1&"C"&COLUMN();FALSE);0); $A$1 ) ) &"C"&COLUMN();FALSE ))
breakdown...
=sum( <=== you can use sum, min, max etc...
A3 : INDIRECT( "R" & ROW()-1+ <=== here you start your range
IFNA( MATCH("=*";FORMULATEXT(A3:INDIRECT("R"&$A$1&"C"&COLUMN();FALSE));0); <=== match until next cell w. formula
IFNA( MATCH("x";A3:INDIRECT("R"&$A$1&"C"&COLUMN();FALSE);0); <=== match until end-marker for end of data list (using "x" here)
$A$1 ) <=== looking forward a specified tunable number of cells available in A1 (1000 is often a good number) - in case there is no end-marker.
) &"C"&COLUMN();FALSE ) <=== here you and your range with dynamic change of current column
)
usage:
last data line...
example dataset (start in A1:A7, 1 row is 1 field)
31258 1
591258,1256
941256, 12858
81256 - 1256 - 12
5498 - 787 > 54564
4525,45;5/48
45878,455,55,6,8
Lookup range is G:H containing only this data for testing
4561 aa
31258 bb
787 cc
8 dd
45 ee
1256 ff
5498 gg
in B1:B7, first substitute sepparators and cleanup data...
=CLEAN(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1;" ";" ");"-";" ");"<";" ");">";" ");",";" ");"/";" ");";";" ") ))
in C1:C7, we do magic to use B1 as array for lookup_value in XLOOKUP (you can also use VLOOKUP).
Once all data is available we transpose & join all data.
=TEXTJOIN(", ";TRUE;
TRANSPOSE(XLOOKUP(
IFERROR(--(TRIM(MID(SUBSTITUTE(B1;" ";REPT(" ";100));100*(ROW(INDIRECT("1:"&(LEN(B1)-LEN(SUBSTITUTE(B1;" ";""))+1)))-1)+1;100)));TRIM(MID(SUBSTITUTE(B1;" ";REPT(" ";100));100*(ROW(INDIRECT("1:"&(LEN(B1)-LEN(SUBSTITUTE(B1;" ";""))+1)))-1)+1;100)));G:G;H:H;"x";0
)))
in D1:D7 we do another cleanup for data not matched by XLOOKUP / VLOOKUP (remove duplicate "x")
=SUBSTITUTE(SUBSTITUTE(C1;", x";"");"x, ";"")
result...
column:A column:D
31258 1 bb
591258,1256 ff
941256, 12858 x
81256 - 1256 - 12 ff
5498 - 787 > 54564 gg, cc
4525,45;5/48 ee
45878,455,55,6,8 dd
you can make a hyperlink to another cell in the same sheet. For example, to insert a hyperlink that will take you to cell A1 in the same worksheet, use a formula similar to this:
=HYPERLINK("#A1", "Go to cell A1")
or something more dynamic...
jump to range of columns on row 3 using dynamic friendly text
=HYPERLINK("#"&AI3&"3";AI3&") "&AH3)
Problem
=IF((SUMIFS_formula)=0,"",SUMIFS_formula)
Solution: force an error like #DIV/0! and then use IFERROR, e.g.
=IFERROR(1/(1/ SUMIFS_formula ),"")
This also works for dates where some cells are empty or have 0 (when using an xlookup or something to avoid return of 00/01/1900).
=IFERROR(1/(1/ A1 );"-")