View pquery.m
(Haystack as text, Needle as text) as number => | |
List.Count(Text.Split(Haystack, Needle)) - 1 | |
(Rec as record, Lambda as function) as record => | |
let | |
Keys = Record.FieldNames(Rec), | |
Values = Record.FieldValues(Rec), | |
Renamed = List.Transform(Keys, each Lambda(_, Record.Field(Rec, _))), | |
Recorded = Record.FromList(Values, Renamed), | |
Return = Recorded |
View Text.Count.m
(Haystack as text, Needle as text) as number => | |
List.Count(Text.Split(Haystack, Needle)) - 1 |
View australian-postcodes.m
This file has been truncated, but you can view the full file.
/* | |
Taken and cribbed from blog.datalicious.com/free-download-all-australian-postcodes-geocod | |
May contain errors where latitude and longitude are off. Use at own non-validated risk. | |
*/ | |
let | |
Source = Table.FromList( | |
{ |
View init
Option Explicit | |
'Call this method from your main VBA module using 'Call init' or 'Call init.init' (if placed in a module called 'init') | |
'Declare the activeworkbook and any other related workbooks required | |
Public wb_1 As Workbook 'this is your activeworkbook where this VBA will be hosted | |
Public wb_2 As Workbook | |
'Delcare the worksheets that you will be interactive with | |
Public ws_1 As Worksheet |
View init
Public ws_unique As Worksheet | |
Public ws_copy_to As Worksheet | |
Public ws_table1 As Worksheet | |
Public lr_table1 As Long | |
Public lr_unique As Long | |
Sub init() | |
Set ws_unique = Sheets("unique") | |
Set ws_copy_to = Sheets("copy_to") |
View gist:892d0f0eed87b9f8e6b0
'copy the content of this gist into a new module in VBA (get to VBA by pressing Alt+F11) | |
'you then have access to the functions below from anywhere else in your workbook VBA scripts | |
'these scrips can be found at Gist on Github at https://gist.github.com/jaykilleen/892d0f0eed87b9f8e6b0 | |
Option Explicit | |
Function get_last_row(sheetname As String, column_number As Integer) As Long | |
get_last_row = Sheets(sheetname).Cells(Rows.Count, column_number).End(xlUp).row |
View gist:66aa778a315495819c52
'Just copy the function below into a new module | |
'I usually call this module 'functions' | |
'You can then call anywhere in your scripts `find_in_array("dummy text", some_array) | |
'Where some_array might be something like ["camp","light bulb", "dummy text") | |
'This function will return TRUE if it finds the value in the array | |
`checkout https://gist.github.com/jaykilleen/892d0f0eed87b9f8e6b0 for other functions that I like to add into my 'functions' module | |
Function find_in_array(FindMe As String, myArray() As String) | |
Dim C As String |
View get_row_number.txt
'Just copy the function below into a new module | |
'I usually call this module 'functions' | |
'You can then call anywhere in your scripts `get_row_number("dummy text", 1) | |
'The integer '1' will tell this to do a ctrl+f on column A | |
'When it finds the first value it will return the row number of that value | |
'Best used on columns that contain unique values | |
'You might need to change the xlPart to xlWhole if you are going for an exact match | |
'You could also extend this function to pass in a boolean to look for whole or partial value and replace xlPart with the value passed in | |
`checkout https://gist.github.com/jaykilleen/892d0f0eed87b9f8e6b0 for other functions that I like to add into my 'functions' module. |
View gist:294d41c3b774bc88f82d
'Just copy the function below into a new module | |
'I usually call this module 'functions' | |
'You can then call anywhere in your scripts `get_column_number("header column name", 15, TRUE) | |
'This will look in row 1 (usually where your headers are on a table) for a value and return the column number | |
'If your headers start on a different row (ie you have slicers above your table) you can pass in the row number | |
`This will match a partial word by default, pass in TRUE if you want to match by the whole word | |
`checkout https://gist.github.com/jaykilleen/892d0f0eed87b9f8e6b0 for other functions that I like to add into my 'functions' module. | |
Function get_column_number(sheet As Worksheet, value As String, Optional row As Variant, Optional whole As Boolean) As Integer | |
View gist:cce6afd13d2bd2a8c70b
'Just copy the function below into a new module | |
'I usually call this module 'functions' | |
'You can then call anywhere in your scripts `get_last_row(ThisWorkbook.Sheets("sheet_1"), 1) | |
'It will then find you the last row of column A | |
`Change the interger for the second argument to use any other column | |
`checkout https://gist.github.com/jaykilleen/892d0f0eed87b9f8e6b0 for other functions that I like to add into my 'functions' module. | |
Option Explicit | |
Function get_last_row(sheetname As String, column_number As Integer) As Long |
NewerOlder