Choose range

[theta]

Hi all...

I have updated a project (very large project) to accept a new file type.

The project already has fixed ranges in place e.g.

Workbooks("Main.xls).Worksheets(1).Range("A1:Z13")

The new files have different ranges. There is an identified (named range) called NEWFILE. When this is true, it should select a different range. I have produced a table in a hidden sheet as follows :

RANGE001....A1:Z13....A1:Z130
RANGE002....B2:Z7......B2:Z70
RANGE003....C4:D12....C4:C80

So I would like to go through and replace .Range("A1:Z13") maybe with a function that has an identifier "RANGE001" and returns either OLD or NEW depending on the value of NEWFILE e.g.

RangeLookup("RANGE001",NEWFILE)

Where NEWFILE = False the result would be A1:Z13
Where NEWFILE = True the result would be A1:Z130

There is a list of 200 ranges (that I have already defined) and it is littered across 10,000+ code lines, so this is the quickest fix I could think of. Ensures it will work with OLD and NEW files by looking up the relevant range based on a switch...?

[Bob Phillips]

Something like?

Public Function RangeLookup(rng As String, NewFile As Boolean) As Range

Select Case rng

Case "RANGE001"
Set RangeLookup = IIf(Not NewFile, Range("A1:Z13"), Range("A1:Z130"))

Case "RANGE002"
Set RangeLookup = IIf(Not NewFile, Range("B2:Z7"), Range("B2:Z70"))

Case "RANGE003"
Set RangeLookup = IIf(Not NewFile, Range("C4:C12"), Range("C4:C80"))

'etc.
End Select
End Function

[theta]

The logic is perfect, but I would not want to build a Select Case for 200 different items.

I have created a worksheet with all the ranges on...could this function not do a lookup based on the value of NEWFILE.

Range INDEX is in column 1
=========================
If == NEWFILE, lookup column is 2
If <> NEWFILE, lookup column is 3

I wanted this approach as it exposes the ranges to the user, allowing them to be updated on the workbook without allowing access to the VBA. The VBA should only handle the innputs fed to it from the workbook e.g.

Workbooks(me).Worksheets(1).RangeFind(Default001,NEWFILE)

This would be evaluated as an actual range ? Any thoughts...

[theta]

The logic is perfect, but I would not want to build a Select Case for 200 different items.

I have created a worksheet with all the ranges on...could this function not do a lookup based on the value of NEWFILE.

Range INDEX is in column 1
=========================
If == NEWFILE, lookup column is 2
If <> NEWFILE, lookup column is 3

I wanted this approach as it exposes the ranges to the user, allowing them to be updated on the workbook without allowing access to the VBA. The VBA should only handle the innputs fed to it from the workbook e.g.

Workbooks(me).Worksheets(1).RangeFind(Default001,NEWFILE)

This would be evaluated as an actual range ? Any thoughts...

Just further from this (to make any work easier). The lookup table can be references in VBA as :

Worksheets("LookupRanges").Range("A2:C202")

Column A = INDEX (RANGE001, RANGE002 etc)
Column B = Old range
Column C = New range

[Bob Phillips]

Maybe best to post the workbook so we can see what the data looks like.

[snb]

you can make an array of arrays:


sub snb()
sn=array(split("A1:Z13_B2:Z7_C4:C12","_"),split("A1:Z130_B2:Z70_C4:C80","_")
sq=sn(abs(newfile=true))
end sub

[theta]

Hi SNB, very clever. So you use the 0,1 to select which array to use. But the problem is they are still not using the values from the worksheet.

Sample workbook here...

http://www.mediafire.com/view/?vesv9lffze7arr5

Again, this is just a cutdown version of the problem. The real issue involves 800+ ranges, over 1,000+ modules with 10,000+ code lines. The project still uses OLD and NEW file types (with the only differences being range sizes) so want to use a simple lookup based on a switch.

Think this is probably the best approach...

[Bob Phillips]

Public Function RangeLookup(rng As String, NewFile As Boolean) As Range

Set RangeLookup = Workbooks(main).Worksheets(1).Range(Application.VLookup(rng, Worksheets("RangeLookups").Columns("A:C"), 2 - NewFile, False))
End Function

[theta]

Awesome, thanks XLD

Is there not a more elegant option available...I don't like using VLOOKUP within VBA. I imagine this could get quite slow if constantly doing VLOOKUP references to get a range.

Would an INDEX / MATCH be fast? Or simply index OldRange or NewRange based on the MATCH position of RANGE001 in INDEX (each column will be saved as a named range)

Thoughts welcome :)

[snb]

Please post a sample workbook ..
You are talking about columns, ranges, named ranges; it's rather confusing without a workbook in which you indicate the before & after situation.

Sub snb()
sn=array(split("A1:Z13_B2:Z7_C4:C12","_"),split("A1:Z130_B2:Z70_C4:C80","_")
c01=application.vlookup(c02,range(sn(abs(newfile=True))),false)
End Sub

[theta]

Please post a sample workbook ..
You are talking about columns, ranges, named ranges; it's rather confusing without a workbook in which you indicate the before & after situation.

Sub snb()
sn=array(split("A1:Z13_B2:Z7_C4:C12","_"),split("A1:Z130_B2:Z70_C4:C80","_")
c01=application.vlookup(c02,range(sn(abs(newfile=True))),false)
End Sub

Uploaded with named ranges

http://www.mediafire.com/view/?swjn4wsjd1ohi2s

I like the approach, but I am trying to remove all hard coded ranges within VBA. They are all going to be contained within OldRanges and NewRanges, and picked from the worksheet.

[snb]

In cell D22:


=INDEX(RangeLookups!A2:C6;MATCH("range001";RangeLookups!A2:A4;0);2+ABS(C2="new"))

[Bob Phillips]

Awesome, thanks XLD

Is there not a more elegant option available...I don't like using VLOOKUP within VBA. I imagine this could get quite slow if constantly doing VLOOKUP references to get a range.

Would an INDEX / MATCH be fast? Or simply index OldRange or NewRange based on the MATCH position of RANGE001 in INDEX (each column will be saved as a named range)

Thoughts welcome :)

Use FIND or even Application.Match to find the id in the list and the use

Cells(therow, 2 - Worksheets("Index").Range("NEWFILE").Value) to return the range string and plug hat in as I showed earlier.

[theta]

Hmmm, FIND would be good. I wanted to avoid using Application functions. Is there a way to achieve this method using pure VBA?

[Bob Phillips]

Public Function RangeLookup(id As String, NewFile As Boolean) As Range
Dim cell As Range
With Worksheets("RangeLookups")

Set cell = .Columns(1).Find(What:=id, After:=.Range("A1"))

If Not cell Is Nothing Then

Set RangeLookup = Workbooks(main).Worksheets(1).Range(cell.Offset(0, 1 - NewFile).Value)
End If
End With
End Function

[theta]

Thanks I will give this a try! :)

[theta]

Thanks XLD, got it working. I did find an issue....

Must be my ignorance with excel, I was hoping I could return a 'floating' references that would be bound to the rest of the reference statement e.g.

RangeLookup would return "A1" as a range that would be bound to the Workbook and Worksheet reference :

Workbooks(main).Worksheets(1).RangeLookup("RANGE001",True)

I soon found that because RangeLookup is defined as a range itself, it cannot simply be "A1" - it must be a fully defined range. This would involve feeding it the workbook and worksheet to which is belongs.

Is there any way to grab this from the calling line (as it will be the same). So in the example, it would be "main" and (1).

For now I have made RangeLookup a string so I get the following to work :

Workbooks(main).Worksheets(1).Range(RangeLookup("RANGE001",True))

This basically pumps "A1" inside the range statement.

Thoughts welcome.

[Bob Phillips]

I am somewhat confused. What does the lookup table look like, and what would you like it to look like, what is the UDF like now, and what does your code look like?