Questions: When I use excel to open a .txt file (a notepad file), the worksheet name is the file name of the notepad file that was opened by default. Therefore, the sheet name will be different when open a different notepad file. Downstream code need this worksheet name be a fixed one. Is there anyway… Read More »

Questions: I’m trying to take a dataset that looks like this: And transform the records into this format: The resulting format would have two columns, one for the old column names and one column for the values. If there are 10,000 rows then there should be 10,000 groups of data in the new format. I’m… Read More »

Questions: I have a process that builds an Excel report, then opens it for the user. The problem is if someone leaves the file open, it stays locked and nobody else can build the report until the first person exits the excel file. Is there a way to open an Excel file without locking it,… Read More »

Questions: Summary: I have a list of policy numbers I’m looping through in Column A using the for each loop Problem: Everything works except if there is an empty cell in Column A, my code deletes the entire row (as it should), but then when I try to set the policy variable I get a… Read More »

Questions: In VBA, I am updating the formula in a cell (which works ok), but automatic recalculation does not work: updated_formula = “=COUNT(Sheet1!A3:A” & nr_points & “)” Cells(x, y).Formula = updated_formula ActiveWorkbook.Save Cells(x, y).Calculate The formula simply counts the number of existing rows in another sheet. When I run the macro, the cell’s value in… Read More »

Questions: How can I format Cell (D:) 2 decimals places Cell (E:) 1 decimal place and Cell (H:)3 decimals places in excel using a macro? Answers: the numberformat property of a range will format it to any custom format sheets(“sheet1”).range(“D:D”).numberformat = “0.00” sheets(“sheet1”).range(“E:E”).numberformat = “0.0” sheets(“sheet1”).range(“H:H”).numberformat = “0.000” for numbers with a comma or currency… Read More »

Questions: I’m trying to get the column number of a cell in the first row with a specific value. The code I’m using isn’t working for some reason. Dim colNum As Integer ‘sheetName is a String With ActiveWorkbook.Sheets(sheetName) colNum = column(.Match(“ID”, 1:1, 0)) It’s telling me it’s expecting a “list separator or )”. How do… Read More »

Questions: How can I call UserForm_Initialize() in a Module instead of the UserForm code object? Answers: From a module: UserFormName.UserForm_Initialize Just make sure that in your userform, you update the sub like so: Public Sub UserForm_Initialize() so it can be called from outside the form. Alternately, if the Userform hasn’t been loaded: UserFormName.Show will end… Read More »