I created a macro to add three vlookup functions to three new columns within the sheet. The vlookup is dependant on the first column of the sheet's row data, which is already populated prior to applying the macro. I want the new columns created to only create the amount of rows contained within the first row of the sheet. I was thinking of making a function similiar to an end of file loop.

if row.text is not empty
then row + 1
else exit

Something simple like that, there will never be empty rows in the first column besides when it reaches the end of its data.

I'm sorry if i'm wording this incorrectly, but i used pseudo code so that i clearly get my idea accross.

Thanks in advance for any help provided.

Noc

Assuming that you need the last 'filled' row :

Set SHT = ActiveSheet

 lRow = SHT.RANGE("A:A").SpecialCells(xlCellTypeLastCell). Row
 For i = 1 To lRow

   ' Your code for VLookup can go here

 Next i

Cheers
Shasur


http://www.dotnetdud.blogspot.com

VBA Tips & Tricks (http://www.vbadud.blogspot.com)

Thankyou very much for your help, I am new to vba excel code and want to learn more.

What does the following syntax represent:

SpecialCells(xlCellTypeLastCell).

Is there more pre-defined functions in accordance with special cells?
Is 'xl' another specific excel vba code syntax?
And is CellTypeLastCell contained within that syntax?

I'm sure there is a common area to learn this, if you could point me in that direction I can read up and figure out the stuff on my own.

First let me explain your questions

Is there more pre-defined functions in accordance with special cells?

  Yes SpecialCElls is a predefined method - There are many type of special cells, like the cells that have formula, cells that have comments etc. Excel Identifies this and returns a range (where they are found) in this method

Is 'xl' another specific excel vba code syntax?

And is CellTypeLastCell contained within that syntax?

The xl prefix is for easy use. Usually there are many constants that are used as input for the specialcells method - The compiler recognizes this by enumerated numbers (0,1,2..). But for the benefit of programmers they are denoted by constants like the following

xlCellTypeAllFormatConditions
xlCellTypeAllValidation
xlCellTypeBlanks
xlCellTypeComments
xlCellTypeConstants
xlCellTypeFormulas
xlCellTypeLastCell
xlCellTypeSameFormatConditions
xlCellTypeSameValidation
xlCellTypeVisible

Now you need not worry if you need to use 3 or 6 or 0 for getting the last cell isn't it .. you will use xlCellTypeLastCell .. simple right!

Here is the VBA Reference that might be useful to you (http://www.microsoft.com/downloads/d...displaylang=en)

Also browse msdn.microsoft.com for getting more updates on Excel VBA

Welcome to VBA:)

Cheers
Shasur


http://www.dotnetdud.blogspot.com

VBA Tips & Tricks (http://www.vbadud.blogspot.com)