Invalid forward reference, or reference to uncompiled type
My friend and I had the same issue. I enabled the “AccessibilitycplAdmin 1.0 type admin” under Tools > References (within the VBA editor), that fixed the issue in both computers
excel
index match returns 0 for blank cell, want it to be “-“
=Index(…) & “” it would convert 0 (Blank value) to an empty string.
How do I make a progress bar for loading pandas DataFrame from a large xlsx file?
The following is a one-liner solution utilizing tqdm: import pandas as pd from tqdm import tqdm df = pd.concat([chunk for chunk in tqdm(pd.read_csv(file_name, chunksize=1000), desc=”Loading data”)]) If you know the total lines to be loaded, you can add that information with the parameter total to the tqdm fuction, resulting in a percentage output.
How to put Excel VBA into git
If you use Rubberduck VBA, after clicking You can use the file menu to “Export Active Project”, which exports the form binaries and the code as BAS objects, which are just plain text. Then you can commit to git.
My Excel row is locked at height of 409.5 and I want to increase the size
Create a row below the row you want taller than 409.5 Select cell of top row, and cell of bottom row Click arrow next to the Merge & Center button in the Alignment section of the Home tab on the ribbon Click Merge Cells You can repeat this process multiple times if two cells worth … Read more
If Cell Starts with Text String… Formula
I’m not sure lookup is the right formula for this because of multiple arguments. Maybe hlookup or vlookup but these require you to have tables for values. A simple nested series of if does the trick for a small sample size Try =IF(A1=”a”,”pickup”,IF(A1=”b”,”collect”,IF(A1=”c”,”prepaid”,””))) Now incorporate your left argument =IF(LEFT(A1,1)=”a”,”pickup”,IF(LEFT(A1,1)=”b”,”collect”,IF(LEFT(A1,1)=”c”,”prepaid”,””))) Also note your usage of left, … Read more
How to add data validation to a cell using VBA
Use this one: Dim ws As Worksheet Dim range1 As Range, rng As Range ‘change Sheet1 to suit Set ws = ThisWorkbook.Worksheets(“Sheet1”) Set range1 = ws.Range(“A1:A5”) Set rng = ws.Range(“B1″) With rng.Validation .Delete ‘delete previous validation .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _ Formula1:=”='” & ws.Name & “‘!” & range1.Address End With Note that when you’re using Dim … Read more
how to write to a new cell in python using openpyxl
Try this: import openpyxl wb = load_workbook(filename=”xxxx.xlsx”) ws = wb.worksheets[0] ws[‘A1’] = 1 ws.cell(row=2, column=2).value = 2 This will set Cells A1 and B2 to 1 and 2 respectively (two different ways of setting cell values in a worksheet). The second method (specifying row and column) is most useful for your situation: import openpyxl wb … Read more
python-win32com excel com model started generating errors
I had the same issue and I resolved it by following the instructions here: https://mail.python.org/pipermail/python-win32/2007-August/006147.html Deleting the gen_py output directory and re-running makepy SUCCEEDS and subsequently the test application runs OK again. So the symptom is resolved, but any clues as to how this could have happened. This is a VERY long running application (think … Read more
Imitating the “IN” Operator
I don’t think there is a very elegant solution. However, you could try: If Not IsError(Application.Match(x, Array(“Me”, “You”, “Dog”, “Boo”), False)) Then or you could write your own function: Function ISIN(x, StringSetElementsAsArray) ISIN = InStr(1, Join(StringSetElementsAsArray, Chr(0)), _ x, vbTextCompare) > 0 End Function Sub testIt() Dim x As String x = “Dog” MsgBox ISIN(x, … Read more