• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Refer Column by their Field Names

maheshmurthi

New Member
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
 
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.
 
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 :)
 
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
 
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
 
Back
Top