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

wbstadeli

Board Regular
Joined
Mar 11, 2016
Messages
143
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:
Rich (BB 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?
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

wbstadeli

Board Regular
Joined
Mar 11, 2016
Messages
143
Actually i ended using it like this,
& "*sum(Total_Qty_Shipped_" & i & ")," &
 

Watch MrExcel Video

Forum statistics

Threads
1,123,379
Messages
5,601,272
Members
414,439
Latest member
norideen

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top