Value won't assign

Mr930

Well-known Member
Joined
Aug 31, 2006
Messages
585
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
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
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.
 
Upvote 0
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
 
Upvote 0
If you have activated the sheet of interest, then try:
TargetEUR = ActiveSheet.Range("M3").Value
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,217,681
Messages
6,137,952
Members
450,100
Latest member
mscetr

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