Adding a variable to a named reference, inside of a formula with vba

[wbstadeli]

Hi All,

I am creating new rows with a macro, and assigning formulas into certain cells of the new row. I have named cells with different "i" values at the end so that i dont get duplicate names. This "i" value is set by a seperate function in the workbook, and this working good. However im not sure how to specify a variable in a formula, is there a way to do this? Basically this used to work for my single range, now i want to be able to dynamically add as many as needed ranges, so i can't hard code a exact named, it needs to have the name plus the variable (i).. See code:

'......
.Rows(newrow).Columns(7).Resize(, 1).Select
Set Per_Piece = .Rows(newrow).Columns(7)
Set Per_Lot = .Rows(newrow).Columns(9)
Set Totals = .Rows(newrow).Columns(11)
Totals.Value = "=iferror(if(" & Per_Lot.Address & "=""""," & Per_Piece.Address & "*sum(Total_Qty_Shipped_)," & Per_Lot.Address & "),0)"
'......
'This last line is the line im having trouble with, in the code it is hardcoded to the name without the variable, i need it to be the name with the
variable so "Total_Qty_Shipped_ & i" with "i" being the value i set with the function. Is this possible?

 

[davesexcel]

& "*sum(Total_Qty_Shipped_)," &

Possibly
& "*sum(" & Total_Qty_Shipped_ & ")," &

 

[wbstadeli]

Thanks that worked!

 

[wbstadeli]

Actually i ended using it like this,
& "*sum(Total_Qty_Shipped_" & i & ")," &