Refer Column by their Field Names

[maheshmurthi]

I've written a few macros using VBA and always refer to a particular column using

Cells(x,y) or ActiveCell.offset(0,x)


However, when some new fields were added in-between or some fields were removed from the base file - everything went haywire


Is there anyway I can change my coding style and ensure that changing the sequence of the fields does not impact my code ?


Am stuck on this one really

 

[dan_l]

I do this sometimes with SAP output. I've got some reliability issues.


------

Sub sumsales()

Dim SalesRange As Range

Dim ticker As Integer

Dim total As Long


For ticker = 1 To 10


If Cells(1, ticker).Value = "Sales" Then

Set SalesRange = Range(Cells(2, ticker), Cells(10, ticker))

End If

Next ticker


total = Application.WorksheetFunction.sum(SalesRange)


MsgBox total


End Sub

--------


It's not particularly efficient.

 

[maheshmurthi]

Yes - I do agree.


I've also tried naming the columns as a range with their field names - but dealing with over 150 fields and across multiple workbooks - am afraid it consumes memory and makes the processing slow


Upto all the Ninja's I guess


With the King of Ninja's back from Australia - am hoping to find a solution

 

[Hui]

Maheshmurthi


Firstly, Welcome to the Chandoo.org forums.


Where did you get the idea that Named Formula (Ranges) are slow and consume memory?

I can show you the exact opposite !


I have recently made a file with 2500 named formulas and it was feeding an animated chart, I could not do that in cell formulas as it would be too slow!


Named Formulas are a great way around this issue as they can be set to adjust automagically (that's not a real word) to the expanding or changing ranges sizes

 

[maheshmurthi]

Hui,


Maybe I am doing it the wrong way ? Rows are around 80 K and fields are around 150-200

 

[Hui]

I generally setup one Named Formula which will adjust its range based on criteria eg: No of Rows

Then offset other fields from that

eg: Date: =Offset(A1,1,,counta($a:$A)-1)

then field XYZ (120 columns across) will be

XYZ: =Offset(Date,,120)


Without a sample file it is a bit hard to assist much further

Refer: http://chandoo.org/forums/topic/posting-a-sample-workbook


The other technique you may want to look at with such a data set is to have a Advanced Filter pre-filter the data first and then run reports on the filtered data.

This way the reports aren't being run on a huge amount of data but a selective subset, this will substantially speed up calculations