My vba variable dimentioned as range does not change value with the range I set it to. It is just this one range I am haveing trouble with

Question

Dim p1n As Range   'selected pipe

Dim p1v As Range

Set p1n = Range("RP1SA")   'selected pipe

Set p1v = Range("RP1S")

RP1SA has formula: =VLOOKUP(RP1S,LThrdLineLookup,2,FALSE) in cell

so when RP1S has the value of 1 1/2" then RP1SA has the value of 1.5

The previous value of RP1S was 2" and the previous value of RP1SA was 2

When RP1S changes from 2" to 1 1/2" then RP1SA changes to 1.5

However piv does change to 1 1/2" but p1n retains the value of 2 when RP1SA changes to 1.5

There are 9 other items just like this and they all work fine.

Only this item does not Work.

Something is definitely wrong with vba.

Does anyone know what it is? It works fine in all the other simular code I have. It is just this one that is messing up.

Answer 1

p1n and p1v are Range objects. When they are set like this:

Dim p1n As Range   'selected pipe
Dim p1v As Range

Set p1n = Range("RP1SA")   'selected pipe
Set p1v = Range("RP1S")

they always refer to the same cells, not to the values that were in those cells at the time of Set. Changing the value or formula in RP1SA or RP1S does not require resetting the Range variable; the Range object still points to that cell.

If later code appears to show p1n “keeping” the old value (2 instead of 1.5), the usual causes are:

1. Reading .Value before the worksheet has recalculated.
2. Accidentally working with a different range (for example, a different sheet or workbook where the name RP1SA also exists).
3. Using a cached variable that stored the value instead of the Range.

To ensure the value is current and from the intended sheet:

With ThisWorkbook.Worksheets("SheetName")
    .Calculate          'or Application.Calculate if needed
    Debug.Print .Range("RP1SA").Value
    Debug.Print .Range("RP1S").Value
End With

or, using the variables already declared:

Application.Calculate   'if calculation is manual
Debug.Print p1n.Value   'should now show 1.5
Debug.Print p1v.Value   'should show 1 1/2"

If there are multiple workbooks, qualify the Range with the correct workbook and worksheet when setting the variables:

Set p1n = ThisWorkbook.Worksheets("SheetName").Range("RP1SA")
Set p1v = ThisWorkbook.Worksheets("SheetName").Range("RP1S")

This guarantees that p1n.Value always returns the recalculated value of the RP1SA cell that contains the VLOOKUP formula.

---

References:

• Refer to Cells by Using a Range Object
• Range.Range property (Excel)

Answer 2

Thank you, but I really believe there is a bug in the Excel program. I did what you said and understand exactly what you were doing but Range("RP1SA") will not change from 2 to 1.5. other Subs that I have that do this same routine with 10 different other ranges work fine. Even without recalculating it. I even changed the name of the ranges (RP1S AND RP1SA) and relocated them and it still would not work. I did not have this problem until the last update from Microsoft. After I started making changes to this routine, another routines in the same workbook (that worked fine before) started going crazy. One routine in another Sub just jumped out of the Sub to another Sub in the middle of a stack of code that did not even call that Sub. This woorkbook worked fine and the VLOOKUP worked fine before the update. I had this problem happen before after a Microsoft Update within a year ago of so, then they fixed the update and everything worked fine again. I believe the update is the problem. Again, thanks for your assistance.