Tag Archives: dynamic

How to create dynamic variable names VBA

Questions: I am trying to create a dynamic number of variables in VBA based on the value in a cell. Essentially what I’d like to end up with is something like Team1, Team2… to TeamX. Any help is greatly appreciated Dim i, x As Integer Set x = Range(“J4”).Value Dim Team(1 To x) As String… Read More »

Sum() Across Dynamic Number of Sheets

Questions: Hello all and thanks for your help ahead of time, I have an excel sheet that is solely to take the summation of multiple sheets. Best and most simply put, the formula is something like =sum(Sheet1!A1,Sheet2!A1,Sheet3!A1,Sheet4!A1). There are a few issues that complicate matters though. First off all, I do not know the number… Read More »

Naming Dynamic Range and turning it into a variable

Questions: I am trying to name a range based on a .Find then define that range as my variable so I can enter the variable into a different function. When I run the code I get a type mismatch error. Sub Faked() Dim r As Range Cells.Find(What:=”EE status”, After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _… Read More »

Excel Equi-Depth Binning (Dynamically analyze data to bin values)

Questions: I am just wondering whether anybody can help me in regards to understanding and implementing equi-depth binning dynamically in excel. I managed to achieve equi-width binning using excels data analysis histogram feature. But given that equi-depth requires bins to have an equal amount of values within them, I am slightly confused as to how… Read More »

Excel VBA value dynamicly named textboxes

Questions: Bottom Line Fix: Changing my dynamicly named textbox to have a _ separator between the column and row to get rid of the ambiguity of the names. Previous Code: Set cCntrl = PickTicketForm.Controls.Add(“Forms.TextBox.1”, “PalletNumber” & i & r, True) Fix: Set cCntrl = PickTicketForm.Controls.Add(“Forms.TextBox.1”, “PalletNumber” & i & “_” & r, True) I have… Read More »

How do I edit my sum formula to select a dynamic range of cells?

Questions: I recorded a macro making sure Use Relative References was selected but when running the macro, the sum function always selects the 8 cells above the cell where the total will appear, even though I recorded it using Ctrl+Shift+Arrow Up to select all non-blank cells directly above : ActiveCell.FormulaR1C1 = “=SUM(R[-8]C:R[-1]C) I’ve looked at… Read More »

Dynamic WHERE condition when calling SQL from VBA

Questions: In Excel I have a column of IDs. I would like to SELECT values from a table conditional on the values in the ID column. So far I’ve got this, where ws.Cells(i,10) refers to the values of each of the rows in column K. With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _ “ODBC;DSN=xxxx;Description=xxxxx;UID=xxx;APP=2007 Microsoft Office system;WSID=xxx” _ ),… Read More »