Problems with currency conversions and VBA!

jigglypuff

New Member
Joined
Mar 28, 2013
Messages
25
Guys I am not an expert in excel but have some experience and I foresee that I will have to work with it a lot in the future. I am facing some problems right now and would appreciate your help. Let's see them:

I am working on a sheet where I'd like to write a number in a given cell -- let's say A1 -- and them for the moment I would like that that input were converted to its value in US Dollars -- let's say in the cell B1 -- a THAT moment, and then I want the same value in dollars appeared in another cell, let's say now the C1 but now I want that value fixed and I am having a lot of trouble on it. Let me explain: I want one of the conversions changing through the time as the dollar value changes in the market, and the other one I want frozen in time, showing the value in dollars at the time I put the value in my currency.

I am not an expert in VBA (actually I know almost nothing) and work most of the time using the easier features of the excel. I will explain how I am trying to do this task: I imported from the internet a graph from the internet which shows in real time the value of a dollar compared to a 'real' (my monetary unity), then I programmed the cell B1 to always multiplies the value in the cell A1 so it would give me the value in dollars, and it really does it but it is changing all the time! I need one of them changing and the another one frozen at the time the calculations were made! Is it possible?


Another problem I have with this issue is: in order to make the things easier to me when I know I will use the formula more than once I just copy and paste it all way down a given column and some of the factors in the equations magically becomes others. e.g.: I am trying to copy this formula

=C5*Sheet4!D32

but when I copy it some lines down in the same column it becomes

=C24*Sheet4!D51

actually I need it to become C24 but I also need that the D32 stay the same and it is changing, is there any way to fix it? To decide which factor changes and which one stays the same?

Thank you in advance, I appreciate your help. If I have committed any mistakes, please forgive me, I am not a native English speaker nor am I an expert in excel :P
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
=C5*Sheet4!$D$32 and copy down. The $ sign makes it absolute.
Look for absolute and relative reference in the help file.

Regarding your other question, I have to read that a few more times to comprehend it.
 
Upvote 0
Thank you very much jolivanes, it will help me not only with this task but with several more.

I understand my first question may be misunderstood, I will try to explain it in another way.

Let's say that I want to put a given value in money in the cell A1 and I need that value to be converted in DOLLARS in the cell B1 AND C1 (but I need the value in the cell B1 to remain the same of that one given in the moment I put the value in A1, and I need the value in C1 to change through the time, as the value of the dollar changes) so the first thing I did was to import currency data from a webpage, which updates its information every 60min (I set it to do it) but now I have a problem, because the cells B1 and C1 also update their information, and I need the cell B1 to remain the same!

Ex: in the cell A1 we have 30R$ and in the cells B1 and C1 we have 15$ (30/2) and then in the next day I have 30R$ and 20$ instead of 30R$ 15$ and 20$ respectively in the cells A1 B1 and C1.

I hope I was more clear this time.

Thanks in advance and best regards !
 
Upvote 0
If the changing amount is in cell A1, the conversion number is in H1, the one time calculated number
needs to be in cell B1 and the changing calculated amount needs to be in C1, you could try


Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Target.Worksheet.Range("A1")) Is Nothing Then
    If Range("B1").Value = "" Then Range("B1").Value = Range("A1").Value * Range("H1").Value
End If
End Sub

Put that in your sheet module.

Let well that cell C1 needs to be empty before you start.
If it has anything in it, a 0 (zero) or even invisible characters like spaces, it wont work.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

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
Back
Top