Value won't assign

Mr930

Well-known Member
Joined
Aug 31, 2006
Messages
583
I have this in my VBA code:

Dim TargetEUR as double
TargetEUR = Range("M3").Value

I can see the number sitting in M3 (its the result of a formula)
After the line executes, TargetEUR is zero. Why would it not do the assignment? Is it because M3 is a formula?

thanks
Fred
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
L

Legacy 98055

Guest
That should work. I don't know why it does not???

Try,
TargetEUR = Val(Range("M3").Text)

Keep in mind that the Text property will only give you what is actually displayed. So it will depend on your number formatting.
 

Mr930

Well-known Member
Joined
Aug 31, 2006
Messages
583
In my code I am actually trying to change to a different sheet. The button that activates this code is on one sheet, I need to change to another, and the cells referenced here are on the second sheet. If I step through the code I see the sheet changing (I use the Activate method) but the cells in the formulas I posted are still referencing back to the first sheet. The VBA code is in the first sheets object (I am not real familiar with how the code location works).

Guess I need to move my code (to where?) or be able to change sheets within VBA and have it reference the proper cells.

thanks
Fred
 
L

Legacy 98055

Guest
TargetEUR = Sheets("You Sheets Name").Range("M3").Value
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,899
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

If you have activated the sheet of interest, then try:
TargetEUR = ActiveSheet.Range("M3").Value
 

Mr930

Well-known Member
Joined
Aug 31, 2006
Messages
583
Both these work, thanks.

Is the problem with mine the fact that my VBA code is in the object associated with the sheet with the trigger button, as opposed to the sheet where the cells of interest are located?

thanks
Fred
 
L

Legacy 98055

Guest
Answer = Yes.
Range("M3").Value alone does not provide enough information. Who does this Range belong to? How does the procedure determine if this is Range("M3") located in sheet1 or sheet2 or SomeOtherSheet? The parent is implied by the physical location of your code. If this code is located within sheet1, it is assumed that this range is actually Sheets("Sheet1").Range("M3"). I would only use ActiveSheet , ActiveWorkbook, or ActiveWindow if the location is meant to be dynamic. If you know the name of the worksheet and this is where your source data will be coming from, use the sheetname or sheet codename.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,384
Messages
5,547,634
Members
410,804
Latest member
bluepinky
Top