Loop & Offset assistance required

Hi all – hoping you may be able to offer advice; apologise if numpty question but its got me flummoxed

I have a number (>100) of excel workbooks (consistent format) on a server and I am trying to create a master spreadsheet with links to specific cells. I started using formulae based on Concatenate and Indirect functions but always had to use F2 & F9 to get a result. I am now working on a macro to build semi dynamic links, I am some way there but need some expert help please.

In Col A I have the path and file name for all my source files - I have used the Filename *.xls & FoundFiles for this.
In Col B is my specific file name(s) extracted from Col A using an If , Len and Text function.
My macro declares variables; File = Range (“B#”) and Data(21) - an array of 21 specific worksheet cell references
I then select C1 and with active.formula insert the consistent path, File and Data(1). Then C2 with path name, File and Data(2). Then C3 path name, File and Data(3). & So on for the 21 variables in the array - not elegant but it works okay

I am now at the point where I need to move down the rows and repeat this for each source. I am wondering (hoping ) there is a simple way of offsetting this for the 100 rows / source files. I would also like to be able to set the offset repeat by counting the number of populated rows in Col A.

The code (from memory so syntax may be a bit wonkey):

Please Login or Register  to view this content.

Any pointers on this much appreciated
Hope this make sense - ta muchly D:-)

This should point you in the right direction

Please Login or Register  to view this content.

I have change the iRow as integer to l4Row as long because integers have a max value of 32,767

Please Login or Register  to view this content.

Hi Digory,

I wanted to answer your 14Row question. It is actually L4Row with a small L. Mudraker declared this variable to be a Long thus the small L. This is simply a naming convention used be some programmers to help identify what type of variable they are using. It helps when you are trying to debug code.

Not sure about the 4, but I would imagine it is because the l4Row is being used as a counter in a For loop. Just my guess, Mudraker will have to answer that one for us to be sure.

I was working with someone's code the other day and we were looping through over 50,0000 values. The row counter had been declared as an Integer so the program crashed 32,767. Changed the declaration to a Long and everything worked fine.

Examples

Please Login or Register  to view this content.

This definitely not something you have to do, but it might help your debugging efforts in the future.

Sorry, probably more information than you were looking for.

boylejob

You are correct in your assumtions on my variable l4Row

l is lowercase L for long type

4 is because I use it in a for loop

Row is because it will hold a row number

This makes it easy for me to see at a glance what I intend or are using the variable for & helps with debugging

Digory

The following part of the for loop command looks up column A to get the last used row in column A. If this is row 1 then the for loop will only loop once.

As you are populating column A & B witha a value I think that tha column A in the following command will need to be changed to another column or the whole command changed to a number (see the code in my 1st reply)

Please Login or Register  to view this content.

I don't use hyperlinks so had to play around with your code & do a bit of googgling

Is this what you were trying to achieve

Please Login or Register  to view this content.

A couple of things to note when declaring variables

Certain words are used and reserved by Excel for its own use and should not be used for variable names e.g Path, Cells, Rows, Row, Columns, Date & hundreds more of them. Using them as variable names can give unexpected results

In your code

Please Login or Register  to view this content.

Path is declared as a variable type & mssg is declared as a string type

To declare them correctly you need to use As Type after each variable name.

It is also a good idea to use the 1st letter or several letters to denote what type the variable is.

When I declare variables I always use the following 1st letter for
s = String
i = Integer
l = Long
b = Boolean
r = Range
ws = Worksheets/Sheets

This helps to eliminate coding errors in complex macros & also helps to eliminate the accidental use of reserved words

It works ok for me on Excel 2003 with 1 minor change

After getting it working I modified the delcaring of variable Path to sPath but did not change it within the rest of the code

Please Login or Register  to view this content.