General VBA question

[hajiali]

Is there a way to specify Cells, columns, or rows in vba were if you had to insert a column or row it will not required change the reference to the new Cells, columns, or rows in VBA. So VBA to automatically refer to the new Cells, Columns or Row reference. Have VBA function like formulas

not sure if that is clear. feel free to ask me question to better explain.

Thanks

 

[Joe4]

not sure if that is clear.

It is not, at least not to me.
Perhaps it will make more sense if you can walk us through a simple example.

 

[hajiali]

Let’s say in VBA I have

range(“B3”).value

in later I insert a column in front of column B so the value in B3 is now in C3 is there another way to reference
range(“B3”).value in VBA where I did not have to go back to VBA to change it to C3

its easy to remember just to go back change C3 but if you have multiple reference in VBA that is effected by that one insert.

 

[Joe4]

You can use named ranges.

For example, let's name Range("B3") "MyRange".
Then, we can see its value in a message box like this:

Sub Test()
    MsgBox Range("MyRange").Value
    MsgBox Range("MyRange").Address
End Sub

The first message box returns the value. The second returns the address.

As you insert row/columns, you will see that it is still connected to that cell. So the address of "MyRange" will change, and the value stays the same.

 

[hajiali]

Thanks Joe Never thought or knew I could use name range in VBA. That will definitely do the trick.

 

[Joe4]

You are welcome.