I am working with about 15 large files that I am importing
and performing various functions. Needless to say my
system slows down like you would not believe and I am
trying to cut down my overhead. Here is what I need to
do. I have a File that varies in length from being Empty,
to One Row of Data, to sometimes over 12000 rows.

I need to run various formulas including about 9 VLOOKUP
formulas. I need to find the Last row using a Macro (Ctrl-
Down-Arrow, or Ctrl-End), and reference that number in my
formulas (see below, replacing M65536 with the Last Row
Number found)

Set frng = Range("M65536:M" & Range("M2").End(xlUp).Row)
With frng
.Formula = "=VLOOKUP(RC1,'[9006 Name Report.xls]Names'!
C1:C4,3,FALSE)"
.Formula = .Value
End With

I need to replace the M65536 with the last row number in
the document. This will save enormous amounts of time.

In addition if the file is empty, I want to do a
Workbook.add and save as "c:\temp\NEW Name Report.xls"

I have received help on this issue in the past and am
trying to customize the information provided to my exact
needs. I appreciate all those individuals who have gotten
me this far.

Any and all assistance will be greatly appreciated.

Is there data in columm M? if so then your approach will work and you will
overwrite that data with the formula. If there isn't, then you will write
your formula in M1:M2. Perhaps you should be using another column to
determine the last row. (then change "M65536" to "A65536" as an example)


Set frng = Range("M2:M" & Range("M65536").End(xlUp).Row)
With frng
.Formula = "=VLOOKUP(RC1,'[9006 Name Report.xls]Names'!
C1:C4,3,FALSE)"
.Formula = .Value
End With

--
Regards,
Tom Ogilvy


"Ralph Hill" wrote in message
...
I am working with about 15 large files that I am importing
and performing various functions. Needless to say my
system slows down like you would not believe and I am
trying to cut down my overhead. Here is what I need to
do. I have a File that varies in length from being Empty,
to One Row of Data, to sometimes over 12000 rows.

I need to run various formulas including about 9 VLOOKUP
formulas. I need to find the Last row using a Macro (Ctrl-
Down-Arrow, or Ctrl-End), and reference that number in my
formulas (see below, replacing M65536 with the Last Row
Number found)

Set frng = Range("M65536:M" & Range("M2").End(xlUp).Row)
With frng
.Formula = "=VLOOKUP(RC1,'[9006 Name Report.xls]Names'!
C1:C4,3,FALSE)"
.Formula = .Value
End With

I need to replace the M65536 with the last row number in
the document. This will save enormous amounts of time.

In addition if the file is empty, I want to do a
Workbook.add and save as "c:\temp\NEW Name Report.xls"

I have received help on this issue in the past and am
trying to customize the information provided to my exact
needs. I appreciate all those individuals who have gotten
me this far.

Any and all assistance will be greatly appreciated.

Tom,

The problem is that it goes through every single row,
I need to start at the last row with data and work up.


-----Original Message-----
Is there data in columm M? if so then your approach will
work and you will
overwrite that data with the formula. If there isn't,
then you will write
your formula in M1:M2. Perhaps you should be using
another column to
determine the last row. (then change "M65536"
to "A65536" as an example)


Set frng = Range("M2:M" & Range("M65536").End(xlUp).Row)
With frng
.Formula = "=VLOOKUP(RC1,'[9006 Name Report.xls]Names'!
C1:C4,3,FALSE)"
.Formula = .Value
End With

--
Regards,
Tom Ogilvy


"Ralph Hill" wrote in message
...
I am working with about 15 large files that I am
importing
and performing various functions. Needless to say my
system slows down like you would not believe and I am
trying to cut down my overhead. Here is what I need to
do. I have a File that varies in length from being
Empty,
to One Row of Data, to sometimes over 12000 rows.

I need to run various formulas including about 9 VLOOKUP
formulas. I need to find the Last row using a Macro
(Ctrl-
Down-Arrow, or Ctrl-End), and reference that number in
my
formulas (see below, replacing M65536 with the Last Row
Number found)

Set frng = Range("M65536:M" & Range("M2").End(xlUp).Row)
With frng
.Formula = "=VLOOKUP(RC1,'[9006 Name Report.xls]
Names'!
C1:C4,3,FALSE)"
.Formula = .Value
End With

I need to replace the M65536 with the last row number in
the document. This will save enormous amounts of time.

In addition if the file is empty, I want to do a
Workbook.add and save as "c:\temp\NEW Name Report.xls"

I have received help on this issue in the past and am
trying to customize the information provided to my exact
needs. I appreciate all those individuals who have
gotten
me this far.

Any and all assistance will be greatly appreciated.



.

Ralph,

Would something like this help?

Function Macro1(WS As Long) As Long

Macro1 = 0
Macro1 = ThisWorkbook.Worksheets(WS).UsedRange.Rows.Count

End Function

Dean.
"Ralph Hill" wrote:

Tom,

The problem is that it goes through every single row,
I need to start at the last row with data and work up.


-----Original Message-----
Is there data in columm M? if so then your approach will
work and you will
overwrite that data with the formula. If there isn't,
then you will write
your formula in M1:M2. Perhaps you should be using
another column to
determine the last row. (then change "M65536"
to "A65536" as an example)


Set frng = Range("M2:M" & Range("M65536").End(xlUp).Row)
With frng
.Formula = "=VLOOKUP(RC1,'[9006 Name Report.xls]Names'!
C1:C4,3,FALSE)"
.Formula = .Value
End With

--
Regards,
Tom Ogilvy


"Ralph Hill" wrote in message
...
I am working with about 15 large files that I am
importing
and performing various functions. Needless to say my
system slows down like you would not believe and I am
trying to cut down my overhead. Here is what I need to
do. I have a File that varies in length from being
Empty,
to One Row of Data, to sometimes over 12000 rows.

I need to run various formulas including about 9 VLOOKUP
formulas. I need to find the Last row using a Macro
(Ctrl-
Down-Arrow, or Ctrl-End), and reference that number in
my
formulas (see below, replacing M65536 with the Last Row
Number found)

Set frng = Range("M65536:M" & Range("M2").End(xlUp).Row)
With frng
.Formula = "=VLOOKUP(RC1,'[9006 Name Report.xls]
Names'!
C1:C4,3,FALSE)"
.Formula = .Value
End With

I need to replace the M65536 with the last row number in
the document. This will save enormous amounts of time.

In addition if the file is empty, I want to do a
Workbook.add and save as "c:\temp\NEW Name Report.xls"

I have received help on this issue in the past and am
trying to customize the information provided to my exact
needs. I appreciate all those individuals who have
gotten
me this far.

Any and all assistance will be greatly appreciated.



.

Dean,

How would I incorporate this into my existing script
or call it?

-----Original Message-----
Ralph,

Would something like this help?

Function Macro1(WS As Long) As Long

Macro1 = 0
Macro1 = ThisWorkbook.Worksheets
(WS).UsedRange.Rows.Count

End Function

Dean.
"Ralph Hill" wrote:

Tom,

The problem is that it goes through every single
row,
I need to start at the last row with data and work up.


-----Original Message-----
Is there data in columm M? if so then your approach
will
work and you will
overwrite that data with the formula. If there isn't,
then you will write
your formula in M1:M2. Perhaps you should be using
another column to
determine the last row. (then change "M65536"
to "A65536" as an example)


Set frng = Range("M2:M" & Range("M65536").End
(xlUp).Row)
With frng
.Formula = "=VLOOKUP(RC1,'[9006 Name Report.xls]
Names'!
C1:C4,3,FALSE)"
.Formula = .Value
End With

--
Regards,
Tom Ogilvy


"Ralph Hill" wrote in message
...
I am working with about 15 large files that I am
importing
and performing various functions. Needless to say my
system slows down like you would not believe and I am
trying to cut down my overhead. Here is what I need
to
do. I have a File that varies in length from being
Empty,
to One Row of Data, to sometimes over 12000 rows.

I need to run various formulas including about 9
VLOOKUP
formulas. I need to find the Last row using a Macro
(Ctrl-
Down-Arrow, or Ctrl-End), and reference that number
in
my
formulas (see below, replacing M65536 with the Last
Row
Number found)

Set frng = Range("M65536:M" & Range("M2").End
(xlUp).Row)
With frng
.Formula = "=VLOOKUP(RC1,'[9006 Name Report.xls]
Names'!
C1:C4,3,FALSE)"
.Formula = .Value
End With

I need to replace the M65536 with the last row
number in
the document. This will save enormous amounts of
time.

In addition if the file is empty, I want to do a
Workbook.add and save as "c:\temp\NEW Name
Report.xls"

I have received help on this issue in the past and am
trying to customize the information provided to my
exact
needs. I appreciate all those individuals who have
gotten
me this far.

Any and all assistance will be greatly appreciated.



.


.

I fixed it so it would not. As you had it written it inlcuded the entire
column.


this is written backwards:
Set frng = Range("M65536:M" & Range("M2").End(xlUp).Row)


I suggested a way to fix it, but you appeared to ignore it, so not sure why
you are even posting.

--
Regards,
Tom Ogilvy



"Ralph Hill" wrote in message
...
Tom,

The problem is that it goes through every single row,
I need to start at the last row with data and work up.


-----Original Message-----
Is there data in columm M? if so then your approach will
work and you will
overwrite that data with the formula. If there isn't,
then you will write
your formula in M1:M2. Perhaps you should be using
another column to
determine the last row. (then change "M65536"
to "A65536" as an example)


Set frng = Range("M2:M" & Range("M65536").End(xlUp).Row)
With frng
.Formula = "=VLOOKUP(RC1,'[9006 Name Report.xls]Names'!
C1:C4,3,FALSE)"
.Formula = .Value
End With

--
Regards,
Tom Ogilvy


"Ralph Hill" wrote in message
...
I am working with about 15 large files that I am
importing
and performing various functions. Needless to say my
system slows down like you would not believe and I am
trying to cut down my overhead. Here is what I need to
do. I have a File that varies in length from being
Empty,
to One Row of Data, to sometimes over 12000 rows.

I need to run various formulas including about 9 VLOOKUP
formulas. I need to find the Last row using a Macro
(Ctrl-
Down-Arrow, or Ctrl-End), and reference that number in
my
formulas (see below, replacing M65536 with the Last Row
Number found)

Set frng = Range("M65536:M" & Range("M2").End(xlUp).Row)
With frng
.Formula = "=VLOOKUP(RC1,'[9006 Name Report.xls]
Names'!
C1:C4,3,FALSE)"
.Formula = .Value
End With

I need to replace the M65536 with the last row number in
the document. This will save enormous amounts of time.

In addition if the file is empty, I want to do a
Workbook.add and save as "c:\temp\NEW Name Report.xls"

I have received help on this issue in the past and am
trying to customize the information provided to my exact
needs. I appreciate all those individuals who have
gotten
me this far.

Any and all assistance will be greatly appreciated.



.

Sub BB()
Rows.Delete
ActiveSheet.UsedRange
Range("C20:F30").Value = 1
MsgBox Macro1(ActiveSheet.Index)

End Sub
Function Macro1(WS As Long) As Long

Macro1 = 0
Macro1 = ThisWorkbook.Worksheets(WS).UsedRange.Rows.Count

End Function

When run on a blank worksheet, Shows the last row as 11, but the last row is
30.

--
Regards,
Tom Ogilvy


"Dean Hinson" wrote in message
...
Ralph,

Would something like this help?

Function Macro1(WS As Long) As Long

Macro1 = 0
Macro1 = ThisWorkbook.Worksheets(WS).UsedRange.Rows.Count

End Function

Dean.
"Ralph Hill" wrote:

Tom,

The problem is that it goes through every single row,
I need to start at the last row with data and work up.


-----Original Message-----
Is there data in columm M? if so then your approach will
work and you will
overwrite that data with the formula. If there isn't,
then you will write
your formula in M1:M2. Perhaps you should be using
another column to
determine the last row. (then change "M65536"
to "A65536" as an example)


Set frng = Range("M2:M" & Range("M65536").End(xlUp).Row)
With frng
.Formula = "=VLOOKUP(RC1,'[9006 Name Report.xls]Names'!
C1:C4,3,FALSE)"
.Formula = .Value
End With

--
Regards,
Tom Ogilvy


"Ralph Hill" wrote in message
...
I am working with about 15 large files that I am
importing
and performing various functions. Needless to say my
system slows down like you would not believe and I am
trying to cut down my overhead. Here is what I need to
do. I have a File that varies in length from being
Empty,
to One Row of Data, to sometimes over 12000 rows.

I need to run various formulas including about 9 VLOOKUP
formulas. I need to find the Last row using a Macro
(Ctrl-
Down-Arrow, or Ctrl-End), and reference that number in
my
formulas (see below, replacing M65536 with the Last Row
Number found)

Set frng = Range("M65536:M" & Range("M2").End(xlUp).Row)
With frng
.Formula = "=VLOOKUP(RC1,'[9006 Name Report.xls]
Names'!
C1:C4,3,FALSE)"
.Formula = .Value
End With

I need to replace the M65536 with the last row number in
the document. This will save enormous amounts of time.

In addition if the file is empty, I want to do a
Workbook.add and save as "c:\temp\NEW Name Report.xls"

I have received help on this issue in the past and am
trying to customize the information provided to my exact
needs. I appreciate all those individuals who have
gotten
me this far.

Any and all assistance will be greatly appreciated.



.

TOM,

I am really Sorry about that, I thought is was a re-
post of part of my message.

Do you know if this will work if there isonly ONE row
of data, or does it get hungup?

Again, sorry for the confustion (MINE).

-----Original Message-----
I fixed it so it would not. As you had it written it
inlcuded the entire
column.


this is written backwards:
Set frng = Range("M65536:M" & Range("M2").End(xlUp).Row)


I suggested a way to fix it, but you appeared to ignore
it, so not sure why
you are even posting.

--
Regards,
Tom Ogilvy



"Ralph Hill" wrote in message
...
Tom,

The problem is that it goes through every single
row,
I need to start at the last row with data and work up.


-----Original Message-----
Is there data in columm M? if so then your approach
will
work and you will
overwrite that data with the formula. If there isn't,
then you will write
your formula in M1:M2. Perhaps you should be using
another column to
determine the last row. (then change "M65536"
to "A65536" as an example)


Set frng = Range("M2:M" & Range("M65536").End
(xlUp).Row)
With frng
.Formula = "=VLOOKUP(RC1,'[9006 Name Report.xls]
Names'!
C1:C4,3,FALSE)"
.Formula = .Value
End With

--
Regards,
Tom Ogilvy


"Ralph Hill" wrote in message
...
I am working with about 15 large files that I am
importing
and performing various functions. Needless to say my
system slows down like you would not believe and I am
trying to cut down my overhead. Here is what I need
to
do. I have a File that varies in length from being
Empty,
to One Row of Data, to sometimes over 12000 rows.

I need to run various formulas including about 9
VLOOKUP
formulas. I need to find the Last row using a Macro
(Ctrl-
Down-Arrow, or Ctrl-End), and reference that number
in
my
formulas (see below, replacing M65536 with the Last
Row
Number found)

Set frng = Range("M65536:M" & Range("M2").End
(xlUp).Row)
With frng
.Formula = "=VLOOKUP(RC1,'[9006 Name Report.xls]
Names'!
C1:C4,3,FALSE)"
.Formula = .Value
End With

I need to replace the M65536 with the last row
number in
the document. This will save enormous amounts of
time.

In addition if the file is empty, I want to do a
Workbook.add and save as "c:\temp\NEW Name
Report.xls"

I have received help on this issue in the past and am
trying to customize the information provided to my
exact
needs. I appreciate all those individuals who have
gotten
me this far.

Any and all assistance will be greatly appreciated.



.



.

Tom,

I did not know that UsedRange had this flaw. Is this a 'bug' or just an
undocumented functionality?

Thank you for the lesson.

Dean.

" wrote:

TOM,

I am really Sorry about that, I thought is was a re-
post of part of my message.

Do you know if this will work if there isonly ONE row
of data, or does it get hungup?

Again, sorry for the confustion (MINE).

-----Original Message-----
I fixed it so it would not. As you had it written it
inlcuded the entire
column.


this is written backwards:
Set frng = Range("M65536:M" & Range("M2").End(xlUp).Row)


I suggested a way to fix it, but you appeared to ignore
it, so not sure why
you are even posting.

--
Regards,
Tom Ogilvy



"Ralph Hill" wrote in message
...
Tom,

The problem is that it goes through every single
row,
I need to start at the last row with data and work up.


-----Original Message-----
Is there data in columm M? if so then your approach
will
work and you will
overwrite that data with the formula. If there isn't,
then you will write
your formula in M1:M2. Perhaps you should be using
another column to
determine the last row. (then change "M65536"
to "A65536" as an example)


Set frng = Range("M2:M" & Range("M65536").End
(xlUp).Row)
With frng
.Formula = "=VLOOKUP(RC1,'[9006 Name Report.xls]
Names'!
C1:C4,3,FALSE)"
.Formula = .Value
End With

--
Regards,
Tom Ogilvy


"Ralph Hill" wrote in message
...
I am working with about 15 large files that I am
importing
and performing various functions. Needless to say my
system slows down like you would not believe and I am
trying to cut down my overhead. Here is what I need
to
do. I have a File that varies in length from being
Empty,
to One Row of Data, to sometimes over 12000 rows.

I need to run various formulas including about 9
VLOOKUP
formulas. I need to find the Last row using a Macro
(Ctrl-
Down-Arrow, or Ctrl-End), and reference that number
in
my
formulas (see below, replacing M65536 with the Last
Row
Number found)

Set frng = Range("M65536:M" & Range("M2").End
(xlUp).Row)
With frng
.Formula = "=VLOOKUP(RC1,'[9006 Name Report.xls]
Names'!
C1:C4,3,FALSE)"
.Formula = .Value
End With

I need to replace the M65536 with the last row
number in
the document. This will save enormous amounts of
time.

In addition if the file is empty, I want to do a
Workbook.add and save as "c:\temp\NEW Name
Report.xls"

I have received help on this issue in the past and am
trying to customize the information provided to my
exact
needs. I appreciate all those individuals who have
gotten
me this far.

Any and all assistance will be greatly appreciated.



.



.

Dean,
It isn't a flaw. You (and many people) assume that UsedRange always starts
with A1, but it doesn't. UsedRange accurately reports what Excel considers
to be the cells in Use - i.e. those cells it actively stores information
about. All other cells are "virtual". The other confusion with usedrange
is that it doesn't always correspond with what we (humans) would considered
used. We would think of cells that contain data, but excel can often store
information on cells that cover a larger area than that.

Anyway, in this case, I think the user probably starts from A1, so there
wouldn't be a problem, but it is important to know the true behavior,
because someday it could "bite" you. <g

--
Regards,
Tom Ogilvy

"Dean Hinson" wrote in message
...
Tom,

I did not know that UsedRange had this flaw. Is this a 'bug' or just an
undocumented functionality?

Thank you for the lesson.

Dean.

" wrote:

TOM,

I am really Sorry about that, I thought is was a re-
post of part of my message.

Do you know if this will work if there isonly ONE row
of data, or does it get hungup?

Again, sorry for the confustion (MINE).

-----Original Message-----
I fixed it so it would not. As you had it written it
inlcuded the entire
column.


this is written backwards:
Set frng = Range("M65536:M" & Range("M2").End(xlUp).Row)


I suggested a way to fix it, but you appeared to ignore
it, so not sure why
you are even posting.

--
Regards,
Tom Ogilvy



"Ralph Hill" wrote in message
...
Tom,

The problem is that it goes through every single
row,
I need to start at the last row with data and work up.


-----Original Message-----
Is there data in columm M? if so then your approach
will
work and you will
overwrite that data with the formula. If there isn't,
then you will write
your formula in M1:M2. Perhaps you should be using
another column to
determine the last row. (then change "M65536"
to "A65536" as an example)


Set frng = Range("M2:M" & Range("M65536").End
(xlUp).Row)
With frng
.Formula = "=VLOOKUP(RC1,'[9006 Name Report.xls]
Names'!
C1:C4,3,FALSE)"
.Formula = .Value
End With

--
Regards,
Tom Ogilvy


"Ralph Hill" wrote in message
...
I am working with about 15 large files that I am
importing
and performing various functions. Needless to say my
system slows down like you would not believe and I am
trying to cut down my overhead. Here is what I need
to
do. I have a File that varies in length from being
Empty,
to One Row of Data, to sometimes over 12000 rows.

I need to run various formulas including about 9
VLOOKUP
formulas. I need to find the Last row using a Macro
(Ctrl-
Down-Arrow, or Ctrl-End), and reference that number
in
my
formulas (see below, replacing M65536 with the Last
Row
Number found)

Set frng = Range("M65536:M" & Range("M2").End
(xlUp).Row)
With frng
.Formula = "=VLOOKUP(RC1,'[9006 Name Report.xls]
Names'!
C1:C4,3,FALSE)"
.Formula = .Value
End With

I need to replace the M65536 with the last row
number in
the document. This will save enormous amounts of
time.

In addition if the file is empty, I want to do a
Workbook.add and save as "c:\temp\NEW Name
Report.xls"

I have received help on this issue in the past and am
trying to customize the information provided to my
exact
needs. I appreciate all those individuals who have
gotten
me this far.

Any and all assistance will be greatly appreciated.



.



.

It will work as long as there is data in M2.

Set frng = Range("M2:M" & Range("M65536").End(xlUp).Row)
if frng(1).row = 1 then exit sub
With frng
.Formula = "=VLOOKUP(RC1, _
'[9006 Name Report.xls]Names'!C1:C4,3,FALSE)"
.Formula = .Value
End With

the added line should halt execution if the column M is blank.

Again, it seems to me you should be using another column to get the last
row, like column A

Set frng = Range("M2:M" & Range("A65536").End(xlUp).Row)
if frng(1).row = 1 then exit sub
With frng
.Formula = "=VLOOKUP(RC1, _
'[9006 Name Report.xls]Names'!C1:C4,3,FALSE)"
.Formula = .Value
End With

--
Regards,
Tom Ogilvy


wrote in message
...
TOM,

I am really Sorry about that, I thought is was a re-
post of part of my message.

Do you know if this will work if there isonly ONE row
of data, or does it get hungup?

Again, sorry for the confustion (MINE).

-----Original Message-----
I fixed it so it would not. As you had it written it
inlcuded the entire
column.


this is written backwards:
Set frng = Range("M65536:M" & Range("M2").End(xlUp).Row)


I suggested a way to fix it, but you appeared to ignore
it, so not sure why
you are even posting.

--
Regards,
Tom Ogilvy



"Ralph Hill" wrote in message
...
Tom,

The problem is that it goes through every single
row,
I need to start at the last row with data and work up.


-----Original Message-----
Is there data in columm M? if so then your approach
will
work and you will
overwrite that data with the formula. If there isn't,
then you will write
your formula in M1:M2. Perhaps you should be using
another column to
determine the last row. (then change "M65536"
to "A65536" as an example)


Set frng = Range("M2:M" & Range("M65536").End
(xlUp).Row)
With frng
.Formula = "=VLOOKUP(RC1,'[9006 Name Report.xls]
Names'!
C1:C4,3,FALSE)"
.Formula = .Value
End With

--
Regards,
Tom Ogilvy


"Ralph Hill" wrote in message
...
I am working with about 15 large files that I am
importing
and performing various functions. Needless to say my
system slows down like you would not believe and I am
trying to cut down my overhead. Here is what I need
to
do. I have a File that varies in length from being
Empty,
to One Row of Data, to sometimes over 12000 rows.

I need to run various formulas including about 9
VLOOKUP
formulas. I need to find the Last row using a Macro
(Ctrl-
Down-Arrow, or Ctrl-End), and reference that number
in
my
formulas (see below, replacing M65536 with the Last
Row
Number found)

Set frng = Range("M65536:M" & Range("M2").End
(xlUp).Row)
With frng
.Formula = "=VLOOKUP(RC1,'[9006 Name Report.xls]
Names'!
C1:C4,3,FALSE)"
.Formula = .Value
End With

I need to replace the M65536 with the last row
number in
the document. This will save enormous amounts of
time.

In addition if the file is empty, I want to do a
Workbook.add and save as "c:\temp\NEW Name
Report.xls"

I have received help on this issue in the past and am
trying to customize the information provided to my
exact
needs. I appreciate all those individuals who have
gotten
me this far.

Any and all assistance will be greatly appreciated.



.



.

TOM & DEAN,

Thank you vary much for all of your assistance

-----Original Message-----
It will work as long as there is data in M2.

Set frng = Range("M2:M" & Range("M65536").End(xlUp).Row)
if frng(1).row = 1 then exit sub
With frng
.Formula = "=VLOOKUP(RC1, _
'[9006 Name Report.xls]Names'!C1:C4,3,FALSE)"
.Formula = .Value
End With

the added line should halt execution if the column M is
blank.

Again, it seems to me you should be using another column
to get the last
row, like column A

Set frng = Range("M2:M" & Range("A65536").End(xlUp).Row)
if frng(1).row = 1 then exit sub
With frng
.Formula = "=VLOOKUP(RC1, _
'[9006 Name Report.xls]Names'!C1:C4,3,FALSE)"
.Formula = .Value
End With

--
Regards,
Tom Ogilvy


wrote in message
...
TOM,

I am really Sorry about that, I thought is was a
re-
post of part of my message.

Do you know if this will work if there isonly ONE
row
of data, or does it get hungup?

Again, sorry for the confustion (MINE).

-----Original Message-----
I fixed it so it would not. As you had it written it
inlcuded the entire
column.


this is written backwards:
Set frng = Range("M65536:M" & Range("M2").End
(xlUp).Row)


I suggested a way to fix it, but you appeared to ignore
it, so not sure why
you are even posting.

--
Regards,
Tom Ogilvy



"Ralph Hill" wrote in message
...
Tom,

The problem is that it goes through every single
row,
I need to start at the last row with data and work
up.


-----Original Message-----
Is there data in columm M? if so then your approach
will
work and you will
overwrite that data with the formula. If there
isn't,
then you will write
your formula in M1:M2. Perhaps you should be using
another column to
determine the last row. (then change "M65536"
to "A65536" as an example)


Set frng = Range("M2:M" & Range("M65536").End
(xlUp).Row)
With frng
.Formula = "=VLOOKUP(RC1,'[9006 Name Report.xls]
Names'!
C1:C4,3,FALSE)"
.Formula = .Value
End With

--
Regards,
Tom Ogilvy


"Ralph Hill" wrote in
message
...
I am working with about 15 large files that I am
importing
and performing various functions. Needless to
say my
system slows down like you would not believe and
I am
trying to cut down my overhead. Here is what I
need
to
do. I have a File that varies in length from
being
Empty,
to One Row of Data, to sometimes over 12000 rows.

I need to run various formulas including about 9
VLOOKUP
formulas. I need to find the Last row using a
Macro
(Ctrl-
Down-Arrow, or Ctrl-End), and reference that
number
in
my
formulas (see below, replacing M65536 with the
Last
Row
Number found)

Set frng = Range("M65536:M" & Range("M2").End
(xlUp).Row)
With frng
.Formula = "=VLOOKUP(RC1,'[9006 Name
Report.xls]
Names'!
C1:C4,3,FALSE)"
.Formula = .Value
End With

I need to replace the M65536 with the last row
number in
the document. This will save enormous amounts of
time.

In addition if the file is empty, I want to do a
Workbook.add and save as "c:\temp\NEW Name
Report.xls"

I have received help on this issue in the past
and am
trying to customize the information provided to my
exact
needs. I appreciate all those individuals who
have
gotten
me this far.

Any and all assistance will be greatly
appreciated.



.



.



.

TOM & DEAN,

Thank you vary much for all of your assistance

-----Original Message-----
It will work as long as there is data in M2.

Set frng = Range("M2:M" & Range("M65536").End(xlUp).Row)
if frng(1).row = 1 then exit sub
With frng
.Formula = "=VLOOKUP(RC1, _
'[9006 Name Report.xls]Names'!C1:C4,3,FALSE)"
.Formula = .Value
End With

the added line should halt execution if the column M is
blank.

Again, it seems to me you should be using another column
to get the last
row, like column A

Set frng = Range("M2:M" & Range("A65536").End(xlUp).Row)
if frng(1).row = 1 then exit sub
With frng
.Formula = "=VLOOKUP(RC1, _
'[9006 Name Report.xls]Names'!C1:C4,3,FALSE)"
.Formula = .Value
End With

--
Regards,
Tom Ogilvy


wrote in message
...
TOM,

I am really Sorry about that, I thought is was a
re-
post of part of my message.

Do you know if this will work if there isonly ONE
row
of data, or does it get hungup?

Again, sorry for the confustion (MINE).

-----Original Message-----
I fixed it so it would not. As you had it written it
inlcuded the entire
column.


this is written backwards:
Set frng = Range("M65536:M" & Range("M2").End
(xlUp).Row)


I suggested a way to fix it, but you appeared to ignore
it, so not sure why
you are even posting.

--
Regards,
Tom Ogilvy



"Ralph Hill" wrote in message
...
Tom,

The problem is that it goes through every single
row,
I need to start at the last row with data and work
up.


-----Original Message-----
Is there data in columm M? if so then your approach
will
work and you will
overwrite that data with the formula. If there
isn't,
then you will write
your formula in M1:M2. Perhaps you should be using
another column to
determine the last row. (then change "M65536"
to "A65536" as an example)


Set frng = Range("M2:M" & Range("M65536").End
(xlUp).Row)
With frng
.Formula = "=VLOOKUP(RC1,'[9006 Name Report.xls]
Names'!
C1:C4,3,FALSE)"
.Formula = .Value
End With

--
Regards,
Tom Ogilvy


"Ralph Hill" wrote in
message
...
I am working with about 15 large files that I am
importing
and performing various functions. Needless to
say my
system slows down like you would not believe and
I am
trying to cut down my overhead. Here is what I
need
to
do. I have a File that varies in length from
being
Empty,
to One Row of Data, to sometimes over 12000 rows.

I need to run various formulas including about 9
VLOOKUP
formulas. I need to find the Last row using a
Macro
(Ctrl-
Down-Arrow, or Ctrl-End), and reference that
number
in
my
formulas (see below, replacing M65536 with the
Last
Row
Number found)

Set frng = Range("M65536:M" & Range("M2").End
(xlUp).Row)
With frng
.Formula = "=VLOOKUP(RC1,'[9006 Name
Report.xls]
Names'!
C1:C4,3,FALSE)"
.Formula = .Value
End With

I need to replace the M65536 with the last row
number in
the document. This will save enormous amounts of
time.

In addition if the file is empty, I want to do a
Workbook.add and save as "c:\temp\NEW Name
Report.xls"

I have received help on this issue in the past
and am
trying to customize the information provided to my
exact
needs. I appreciate all those individuals who
have
gotten
me this far.

Any and all assistance will be greatly
appreciated.



.



.



.

TOM & DEAN,

Thank you vary much for all of your assistance

-----Original Message-----
It will work as long as there is data in M2.

Set frng = Range("M2:M" & Range("M65536").End(xlUp).Row)
if frng(1).row = 1 then exit sub
With frng
.Formula = "=VLOOKUP(RC1, _
'[9006 Name Report.xls]Names'!C1:C4,3,FALSE)"
.Formula = .Value
End With

the added line should halt execution if the column M is
blank.

Again, it seems to me you should be using another column
to get the last
row, like column A

Set frng = Range("M2:M" & Range("A65536").End(xlUp).Row)
if frng(1).row = 1 then exit sub
With frng
.Formula = "=VLOOKUP(RC1, _
'[9006 Name Report.xls]Names'!C1:C4,3,FALSE)"
.Formula = .Value
End With

--
Regards,
Tom Ogilvy


wrote in message
...
TOM,

I am really Sorry about that, I thought is was a
re-
post of part of my message.

Do you know if this will work if there isonly ONE
row
of data, or does it get hungup?

Again, sorry for the confustion (MINE).

-----Original Message-----
I fixed it so it would not. As you had it written it
inlcuded the entire
column.


this is written backwards:
Set frng = Range("M65536:M" & Range("M2").End
(xlUp).Row)


I suggested a way to fix it, but you appeared to ignore
it, so not sure why
you are even posting.

--
Regards,
Tom Ogilvy



"Ralph Hill" wrote in message
...
Tom,

The problem is that it goes through every single
row,
I need to start at the last row with data and work
up.


-----Original Message-----
Is there data in columm M? if so then your approach
will
work and you will
overwrite that data with the formula. If there
isn't,
then you will write
your formula in M1:M2. Perhaps you should be using
another column to
determine the last row. (then change "M65536"
to "A65536" as an example)


Set frng = Range("M2:M" & Range("M65536").End
(xlUp).Row)
With frng
.Formula = "=VLOOKUP(RC1,'[9006 Name Report.xls]
Names'!
C1:C4,3,FALSE)"
.Formula = .Value
End With

--
Regards,
Tom Ogilvy


"Ralph Hill" wrote in
message
...
I am working with about 15 large files that I am
importing
and performing various functions. Needless to
say my
system slows down like you would not believe and
I am
trying to cut down my overhead. Here is what I
need
to
do. I have a File that varies in length from
being
Empty,
to One Row of Data, to sometimes over 12000 rows.

I need to run various formulas including about 9
VLOOKUP
formulas. I need to find the Last row using a
Macro
(Ctrl-
Down-Arrow, or Ctrl-End), and reference that
number
in
my
formulas (see below, replacing M65536 with the
Last
Row
Number found)

Set frng = Range("M65536:M" & Range("M2").End
(xlUp).Row)
With frng
.Formula = "=VLOOKUP(RC1,'[9006 Name
Report.xls]
Names'!
C1:C4,3,FALSE)"
.Formula = .Value
End With

I need to replace the M65536 with the last row
number in
the document. This will save enormous amounts of
time.

In addition if the file is empty, I want to do a
Workbook.add and save as "c:\temp\NEW Name
Report.xls"

I have received help on this issue in the past
and am
trying to customize the information provided to my
exact
needs. I appreciate all those individuals who
have
gotten
me this far.

Any and all assistance will be greatly
appreciated.



.



.



.