Offsetting the Argument of an Indirect Function in VBA

%3CLINGO-SUB%20id%3D%22lingo-sub-1970965%22%20slang%3D%22en-US%22%3EOffsetting%20the%20Argument%20of%20an%20Indirect%20Function%20in%20VBA%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1970965%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20everyone%2C%3CBR%20%2F%3E%3CBR%20%2F%3EI%20have%20a%20list%20of%20ranges%20in%20excel%2C%20and%20I'm%20trying%20to%20write%20VBA%20code%20that%20gets%20each%20range%20in%20turn%20and%20inputs%20it%20into%20solver%20constraints%2C%20so%20the%20solver%20works%20on%20a%20different%20range%20each%20iteration.%20This%20is%20where%20I'm%20up%20to%20so%20far%3A%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3ESub%20solvermacro3()%3C%2FP%3E%3CP%3EDim%20i%20As%20Integer%3CBR%20%2F%3EDim%20Z%20As%20Integer%3CBR%20%2F%3EDim%20Y%20As%20String%3CBR%20%2F%3EFor%20i%20%3D%200%20To%202%3C%2FP%3E%3CP%3EZ%20%3D%2038%20%2B%20i%3C%2FP%3E%3CP%3EY%20%3D%20CStr(Z)%3C%2FP%3E%3CP%3E%3CBR%20%2F%3E'%20solvermacro3%20Macro%3CBR%20%2F%3ESolverReset%3C%2FP%3E%3CP%3ESolverAdd%20CellRef%3A%3DSheets(%22data%22).Range(%22INDIRECT(GG%26amp%3BY)%22)%2C%20Relation%3A%3D5%2C%20FormulaText%3A%3D%22binary%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EObviously%20theres%20a%20bunch%20more%20code%20after%20but%20this%20is%20the%20relevant%20bit.%20When%20it%20tries%20to%20run%2C%20I%20get%20an%20error%201004%20object%20defined%20error%2C%20but%20it%20runs%20fine%20if%20I%20just%20use%20%22GG38%22%20as%20the%20argument%20for%20the%20indirect%2C%20so%20I%20know%20the%20problem%20is%20related%20to%20the%20%22%26amp%3BY%22%20bit.%20I'm%20very%20new%20to%20VBA%20so%20please%20go%20easy%20on%20me%20if%20I'm%20doing%20anything%20really%20stupid.%20Thanks%20in%20advance%20for%20your%20hel%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1970965%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Occasional Visitor

Hi everyone,

I have a list of ranges in excel, and I'm trying to write VBA code that gets each range in turn and inputs it into solver constraints, so the solver works on a different range each iteration. This is where I'm up to so far:

Sub solvermacro3()

Dim i As Integer
Dim Z As Integer
Dim Y As String
For i = 0 To 2

Z = 38 + i

Y = CStr(Z)


' solvermacro3 Macro
SolverReset

SolverAdd CellRef:=Sheets("data").Range("INDIRECT(GG&Y)"), Relation:=5, FormulaText:="binary"

 

Obviously theres a bunch more code after but this is the relevant bit. When it tries to run, I get an error 1004 object defined error, but it runs fine if I just use "GG38" as the argument for the indirect, so I know the problem is related to the "&Y" bit. I'm very new to VBA so please go easy on me if I'm doing anything really stupid. Thanks in advance for your hel

0 Replies