Excel VBA Multiply

Dears,
First of all. This is an awesome forum and I have been learning from this from long time.

I have a question.
I have cell range which are linked to external excel file and containing different numbers in various currency. Cell Range ( A1:A30)
I have a currency range with which I want to convert the above range and conversion rate is given in B1
and I am applying the following code.

Please Login or Register  to view this content.

It works perfectly fine however after multiplying it is removing the links of external file and giving me hard figures.
Is it possible If I can modify the above code and keep the external links * figure in B1 and get a product?

Regards,
N

Hi, welcome to the forum

Why do you need VBA for this? Do you intend to replace the existing values? If not, you could probably use a regular formula to do this

You would need to change the column A "links" to a larger formula. For instance, if the link in A1 reads;
=Sheet5!A1

...change that to:
=Sheet5!A1 * B1

My suggestion above stands.

I wouldn't recommend trying to change the linked cells themselves. There's no need to do that, they are your "source" references and should be left alone, in my opinion. You have tons of free space on your worksheet, so I would create my final "summary" in other cells referencing those source cells.

=A1 * B1
=A2 * B1
.etc...



This way any edits to B1 would instantly update your summary results with no VBA.

try some basic approach..

Something like..

Please Login or Register  to view this content.

The solution from Debraj Roy doesn't remove the original links as well?

Hi JB..

Excel native paste special function has a special ability.. to preserve .FORMULA and then Concat the Multiply with the Old one..
If original value is FORMULA then it concat else it HardCoded Multiply the VALUE..