Value won't assign

[Mr930]

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

 

[Legacy 98055]

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]

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

 

[Legacy 98055]

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

 

[Peter_SSs]

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

 

[Mr930]

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

 

[Legacy 98055]

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.