How to use relative names in Excel VBA

0 votes

Many "advanced" (aka: VBA) excel tutorials on the web or even excel's vba help encurage us to use the

Range("B2:B10")

method (to be precise: object) for selecting cells or getting values. In the same place they often add it's totally ok to use predefined names as well:

Range("valuesabove")

On the other hand I fell in love with the incredible power of relatively defined cell names. They make it so much easier to write and handle big composite formulas, and basically to refer to nearly anything.

However, relative names don't work in the Range("valuesabove") method the way we are used to it.

Relative names are typically used on worksheets to refer to the presently chosen cell or the cell in which they are utilised.

This is not true for the Range() object in VBA. Range is a function that is related to a WorkSheet object, often the ActiveSheet. But A1 in the left upper cell of ActiveSheet represents ActiveSheet. Range proves to be relevant to this, as well. And for this reason, relative names ("one column to the left, two rows above") do not work with it but absolute names ($C$23) do.

So, I have the following question: How can I use relative names effectively in VBA?

Dec 24, 2022 in Others by Kithuzzz
• 38,020 points
413 views

1 answer to this question.

0 votes

It appears you are looking for Range.Offset() http://msdn.microsoft.com/en-us/library/office/ff840060%28v=office.15%29.aspx

However, you could do it as:

'Your example Range(Col_B_in_current_row) as
Range("B" & ActiveCell.Row).Select

'Your example Range("B2:B10") -> Range("valuesabove") as
Range("B2:B10").Offset(-1, 0).Select

Just seems like a relatively simple syntax already exists for this.

answered Dec 24, 2022 by narikkadan
• 63,720 points

Related Questions In Others

0 votes
1 answer

Excel-VBA - How to identify Target range (more than 1 cell) is deleted in a Worksheet_Change function?

You misunderstand the purpose of the function ...READ MORE

answered Sep 23, 2022 in Others by narikkadan
• 63,720 points
3,567 views
0 votes
1 answer

How to print an Excel Sheet using VBA with Nitro PDF Creator in Excel 2016

you can use the built-in excel facilities ...READ MORE

answered Sep 24, 2022 in Others by narikkadan
• 63,720 points
1,228 views
0 votes
1 answer

How to use the INDIRECT function in Excel

Try this: =INDEX(INDIRECT($A$1&"!$1:$1048576"),MATCH($B$1,INDI ...READ MORE

answered Sep 29, 2022 in Others by narikkadan
• 63,720 points
810 views
0 votes
1 answer

How to use a named column in Excel formulas

Let's say I have the following figures ...READ MORE

answered Oct 24, 2022 in Others by narikkadan
• 63,720 points
504 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
1,098 views
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
3,520 views
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 23,260 points
823 views
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,690 points
983 views
+1 vote
1 answer

How to use VBA in Excel for Google Search?

Try this: Private Const LicenseRegistration As String = ...READ MORE

answered Oct 7, 2022 in Others by narikkadan
• 63,720 points
1,629 views
0 votes
1 answer

How do I use the Indirect Function in Excel VBA to incorporate the equations in a VBA Macro Function

Try this: Sub Test() Dim str As String: str ...READ MORE

answered Jan 19, 2023 in Others by narikkadan
• 63,720 points
940 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP