Hello, could someone please help me with the following:

Column M of my worksheet (Named: Register) contains fund numbers. The
algorithm below is currently in cells: M3:M4.

The VLOOKUP looks up the table on the worksheet (Named: Fund) that is
also contained in the same workbook.

The named range "Fund" is as follows: =Fund!$A$2:$B$51

What I need is that when the below subroutine (New_Line) is run, then
the VLOOKUP algorithm is inserted in the next row in column M.

For example: The VLOOKUP algorithm is at the moment only in cells M3 and
M4. When the subroutine (New_Line) is next run, I need the VLOOKUP
algorithm to be inserted in cell M5. After that, when I run the

subroutine again, then cell M6 needs the VLOOKUP algorithm in it and so
on.

I tried placing the VLOOKUP algorithm in all the cells from M3:M50000,
however the spreadsheet size went from 70K to over 5Mb.


Any help would be greatly appreciated.

Thanks,

Chris.


=IF(ISNA(VLOOKUP(L3,Fund,2,0)),"",VLOOKUP(L3,Fund, 2,FALSE))


LastCell=offset(Register!$A$3,COUNTA(Register!$A$3 :$A$50000)-1,0)


Sub New_Line()

Range("A2").Select

Application.ScreenUpdating = False

Range("LastCell").Select

ActiveCell.Offset(1, 0).Range("A1").Select

ActiveCell.Rows("1:1").EntireRow.Select

Selection.RowHeight = 25.5

ActiveCell.Range("A1:P1").Select

Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone

With Selection.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 8
End With

With Selection
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.ShrinkToFit = False
.MergeCells = False
End With

With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = xlAutomatic
End With

With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = xlAutomatic
End With

With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = xlAutomatic
End With

With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = xlAutomatic
End With

With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = xlAutomatic
End With

With Selection
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.ShrinkToFit = False
.MergeCells = False
End With

Range("LastCell").Select

ActiveWorkbook.Names.Add Name:="LastCell", RefersToR1C1:= _
"=OFFSET(Register!R3C1,COUNTA(Register!R3C1:R50000 C1)-1,0)"

ActiveCell.Activate

Sheets("Register").Select

Range("LastCell").Select

ActiveCell.Offset(1, 0).Range("A1").Select

End Sub





*** Sent via Developersdex http://www.developersdex.com ***