End Of File - For use in Formulas

[Ralph Hill]

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 Ogilvy]

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

 

[Ralph Hill]

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

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.

.

 

[Guest]

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.

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

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 Hill]

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.

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


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 Ogilvy]

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

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

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 Ogilvy]

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

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.

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


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.



.

 

[Guest]

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

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


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.



.

.

 

[Guest]

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.

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

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


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 Ogilvy]

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

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.

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



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


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 Ogilvy]

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

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

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


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 Hill]

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

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



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


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 Hill]

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

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



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


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 Hill]

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

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



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


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.



.



.

.