Tag Archives: vba

VBA: cannot automatically recalculate Excel formula after updating it — needs manual interaction

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 »

Excel Vba – Using an apostrophe in an sql string

Questions: I’m using excel to input data into an Access database and some of my data strings contain an apostrophe for measurements. This is my SQL input string stSQL = “INSERT INTO Products (ProductName, ProductDescription, ProductUnit, SupplierID) ” & _ “Values (‘” & cboxItemNum & “‘, ‘” & txtDescription & “‘, ‘” & txtUnit &… Read More »

Issue with string separation in VBA

Questions: I would appreciate your help with the following please. I need to separate some data in VBA so it can be set as separate variables to be used in a file naming system. I have the following code: Sub StripSlash1() Dim strVal As String, strVal2 As String, strVal3 As String, strVal4 As String ‘strVal… Read More »

Reference a named Excel variable using VBA

Questions: Situation: I’m using Excel 10. I have a named variable that uses a formula to compute it’s value. This is a named variable, not a named range (in the ‘Name Manager’ the name “MaxDate” refers to “=MAX(Sheet1!B:B)” where column B is a list of dates). The computed value does not appear in any cells… Read More »

Insert New Column in Table Excel VBA

Questions: I’m running into some issues on a worksheet that I’m building. I want to insert a column at a specific location in a table and then set the header. I searched around for a while, and found (some version of) the following code: ActiveSheet.Columns(2).Insert This works to insert the column, but I’m not sure… Read More »

Excel VBA Textbox font size

Questions: I have a userform to display my terms and conditions which is picked up directly from a cell. Upon Activation I call TandC.Text = Worksheets(“Master”).Range(“L41”) which works perfectly when I run the form directly — correct font size, multiple lines, word wrap, etc. However, when I run the form from another userform, the text… Read More »

VBA vlookup reference in different sheet

Questions: In Excel 2007, I am looping through the values of column 4 in Sheet 2. Still in Sheet 2, I want to output the result of my vlookup formula into column 5. The vlookup formula needs to refer to Sheet 1 where the reference columns are. In order to do so I have the… Read More »

Excel isn't saving VBA reference

Questions: I created an Excel .xlsm file with a bunch of functionality in some VBA Modules/Classes and now I’ve decided to separate out the code because it will be shared across 4 different sites. I saved the vba_code.xlsm to a shared location and created my 4 different siteXYZ.xlsm files. In each siteXYZ.xlsm file I would… Read More »

Counting non blank cell results without looping in Excel VBA — e.g., with Specialcells

Questions: Here is the code I’m trying to count with in VBA, hoping to return a count return variable of “3” from ‘FormulaResultCount’. Why can’t I count what is visibly returned by the formulas within each cell; from the grey box (see photo below)? Sub countNonBlanks() Worksheets(“Sheet1”).Select Range(“C:C”).Select FormulaResultCount = Selection.SpecialCells(xlCellTypeFormulas).Count ‘SpecialCells options from MSFT… Read More »

VBA macro to save excel file using path from cell

Questions: I’m trying to save out a file using a combination of hard line and cell value to determine the file path. In cell A29, I have a formula that outputs this: 2014\January\High Cash 1.7.14 I’m getting an Expected: end of statement error. The code is: ActiveWorkbook.SaveAs Filename:=”S:\IRD\Tamarac\Daily High Cash Reporting\& Range(“A29”).Text & “.xlsx”, FileFormat:=… Read More »