CODEWITHSUNDEEP
excelvbanamed-ranges
Tejkaran Samra
Tejkaran Samra

Reputation: 96

Using a named range letter for a starting point

I am using columns R, ES or KE as starting points for different things. However, I am sure this workbook will develop, as it already has done so and these starting points will change. I have a fair few instances of these starting points, and would therefore like to make it so I can change these values very quickly, rather than going through all the code.

To do so, I made a named range and called it 'vb_PercentLetter' . This is for the column R starting point. The value of vb_PercentLetter is 'R' In vba I used it as such:

    x = ws1.Range("vb_PercentLetter" & rowCounter).Offset(0, colCounter) * 100

however, that does not work Is it possible for me to use this method, or should I revert to doing something else?

Upvotes: 0

Views: 66

Answers (1)

TinMan
TinMan

Reputation: 7759

Here is your comment to Jeeped: "the vb_PercentLetter is literally just the letter R, nothing else, so it isn't assigned to any worksheet"

If this is the case then vb_PercentLetter is actually a Workbook Name and not named Range. "R" is not a range.

Names("vb_PercentLetter") will return ="R"

Using a Constant Expression will give the desired result

In a Public Code Module

Public vb_PercentLetter = "R"

I prefer to enumerate the columns of each worksheet. This makes it very easy to maintain the code. 

Public Enum WSCoumns1
    ws1Percent = 10
    ws1ES = 11
    ws1KE = 12
End Enum

enter image description here

Enum Syntax

 ws1.Cells(rowCounter, ws ).Offset(0, colCounter).Offset(0, colCounter) * 100

Upvotes: 1

Related Questions