Modifying vba code .Formula to cell range

[Excelnoub]

Good day,
I am trying to modify my code to fit my need but I don't quite remember how I got there. I need to change this code to have a cell range on another sheet that I have.

Here is the original code:

Please Login or Register  to view this content.

I need to change the following to instead of having /HS to have / & Sheets("Controls").Range("D3")

I need to have this inserted and I tried different tactics but I am getting errors.

Please Login or Register  to view this content.

To

Please Login or Register  to view this content.

Please help

[Excelnoub]

Here is the full code in worksheet:

Please Login or Register  to view this content.

You need to write information in A5 to A30 (Doesn't matter what) and insert a number in B5 to B30 (Any number but don't go to high) and the result will be in C.

[Norie]

Is it the value from B3 on Controls you want, or a reference to that cell?

[Excelnoub]

I need the Value I presume...

The Final value in my C will be the following if I add Test in Column A and 001 in Column B: Test/001/HS

But HS is my section I need to change HS to lets say HP if I transfer my Workbook to another division So I am using a "Controls" sheet to manage this.

[Excelnoub]

Yeah I guess that it would be a reference instead of a Value... The Value will already be inserted in D3 therefore I need to have the code run then add what's in A then / then the number then / then Cell D3 from my "controls" sheet.

How would I go about to do this?

[Norie]

I think it would be this to include the value,

Please Login or Register  to view this content.

and this to include the cell reference.

Please Login or Register  to view this content.

Mind you I'm a bit confused by your code and the formula.

In the code you first put values in a range then you overwrite those values with a fomula, and that formula seems to have way too many "s in it.

So the question is, why are you putting values in the range and ovewriting them?

Also, what should the formula look like on a sheet?

[Norie]

Actually, just had a rethink on the formula.

This would include the sheet reference.

Please Login or Register  to view this content.

[Excelnoub]

Perfect Thank you so much Norie