Hi,

The scenario is like this. I have one .Xla file which has a functio
called generateReport(). This function will retrieve the data fro
Database and display in excel sheet.

Multiple Queried will be executed in generateReport(). I need to kno
the last filled cell address so that I can start next value fillin
form the next row. For this purpose I have used Range.End(xlDown
method to know the last filled cell. But while debugging I found tha
xldown value is -4121 and this method will return last row cel
address. i.e. $A$65536. so if I tried to move to next row (Se
Range.Offset(2, 0)) then runtime error will be displayed. Who t
resolve this problem? Is there any other method to get last filled cel
address?

This .Xla file function will be called from VB application.

Thanks
Ve

--
vedpate
-----------------------------------------------------------------------
vedpatel's Profile: http://www.excelforum.com/member.php...fo&userid=3198
View this thread: http://www.excelforum.com/showthread.php?threadid=51720

This can happen if the column is empty. You can test for it

If Application.CountA(Columns("A:A")) = 0 Then
Range("A1").Select
Else
Range("A1").End(xlDown).Select
End If


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"vedpatel" wrote in
message ...

Hi,

The scenario is like this. I have one .Xla file which has a function
called generateReport(). This function will retrieve the data from
Database and display in excel sheet.

Multiple Queried will be executed in generateReport(). I need to know
the last filled cell address so that I can start next value filling
form the next row. For this purpose I have used Range.End(xlDown)
method to know the last filled cell. But while debugging I found that
xldown value is -4121 and this method will return last row cell
address. i.e. $A$65536. so if I tried to move to next row (Set
Range.Offset(2, 0)) then runtime error will be displayed. Who to
resolve this problem? Is there any other method to get last filled cell
address?

This .Xla file function will be called from VB application.

Thanks
Ved


--
vedpatel
------------------------------------------------------------------------
vedpatel's Profile:
http://www.excelforum.com/member.php...o&userid=31987
View this thread: http://www.excelforum.com/showthread...hreadid=517208

Yes i have tested the below code but still it was going to select the last
row cell i.e. A65536. I have created a range type variable and set the range
of the worksheet like this "Set lv_aRange = p_infoSheet.Range("A1")". Now i
have filled some row values (may be 4 or 5 rows values, it can be some thing
else). now if i execute the below statement "Set lv_aRange =
lv_aRange.End(xlDown).Select" then it was throwing an rum time error. and the
selected cell address is A65536.


"Bob Phillips" wrote:

This can happen if the column is empty. You can test for it

If Application.CountA(Columns("A:A")) = 0 Then
Range("A1").Select
Else
Range("A1").End(xlDown).Select
End If


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"vedpatel" wrote in
message ...

Hi,

The scenario is like this. I have one .Xla file which has a function
called generateReport(). This function will retrieve the data from
Database and display in excel sheet.

Multiple Queried will be executed in generateReport(). I need to know
the last filled cell address so that I can start next value filling
form the next row. For this purpose I have used Range.End(xlDown)
method to know the last filled cell. But while debugging I found that
xldown value is -4121 and this method will return last row cell
address. i.e. $A$65536. so if I tried to move to next row (Set
Range.Offset(2, 0)) then runtime error will be displayed. Who to
resolve this problem? Is there any other method to get last filled cell
address?

This .Xla file function will be called from VB application.

Thanks
Ved


--
vedpatel
------------------------------------------------------------------------
vedpatel's Profile:
http://www.excelforum.com/member.php...o&userid=31987
View this thread: http://www.excelforum.com/showthread...hreadid=517208

You cannot use Select within a Set statement. Set creates an object, Select
is going to a particular cell, they have no correlation.

You need

Set lv_aRange = lv_aRange.End(xlDown)

but this only creates a range to the cell at the end, not all cells from
start to end, for that you need

Set lv_aRange = Range(lv_aRange, lv_aRange.End(xlDown))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"ved prakash" <ved wrote in message
...
Yes i have tested the below code but still it was going to select the last
row cell i.e. A65536. I have created a range type variable and set the
range
of the worksheet like this "Set lv_aRange = p_infoSheet.Range("A1")". Now
i
have filled some row values (may be 4 or 5 rows values, it can be some
thing
else). now if i execute the below statement "Set lv_aRange =
lv_aRange.End(xlDown).Select" then it was throwing an rum time error. and
the
selected cell address is A65536.


"Bob Phillips" wrote:

This can happen if the column is empty. You can test for it

If Application.CountA(Columns("A:A")) = 0 Then
Range("A1").Select
Else
Range("A1").End(xlDown).Select
End If


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"vedpatel" wrote
in
message ...

Hi,

The scenario is like this. I have one .Xla file which has a function
called generateReport(). This function will retrieve the data from
Database and display in excel sheet.

Multiple Queried will be executed in generateReport(). I need to know
the last filled cell address so that I can start next value filling
form the next row. For this purpose I have used Range.End(xlDown)
method to know the last filled cell. But while debugging I found that
xldown value is -4121 and this method will return last row cell
address. i.e. $A$65536. so if I tried to move to next row (Set
Range.Offset(2, 0)) then runtime error will be displayed. Who to
resolve this problem? Is there any other method to get last filled
cell
address?

This .Xla file function will be called from VB application.

Thanks
Ved


--
vedpatel

------------------------------------------------------------------------
vedpatel's Profile:
http://www.excelforum.com/member.php...o&userid=31987
View this thread:
http://www.excelforum.com/showthread...hreadid=517208

Yes i am getting the Range from start to end, but my requirement is to get
the address of last filled cell address and start filling the next values
from there onwards.
i am using the below method to fill next values once i found the last filled
cell address.
Set lv_aRange = lv_aRange.Offset(2, 0)
And start filling the values to worksheet. while executing the above
statement it throws run time exception " err No :1004, Error Description :
Application-defined or object-defined error."
Please help to resolve this issue.

Thanks,
Ved

"Bob Phillips" wrote:

You cannot use Select within a Set statement. Set creates an object, Select
is going to a particular cell, they have no correlation.

You need

Set lv_aRange = lv_aRange.End(xlDown)

but this only creates a range to the cell at the end, not all cells from
start to end, for that you need

Set lv_aRange = Range(lv_aRange, lv_aRange.End(xlDown))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"ved prakash" <ved wrote in message
...
Yes i have tested the below code but still it was going to select the last
row cell i.e. A65536. I have created a range type variable and set the
range
of the worksheet like this "Set lv_aRange = p_infoSheet.Range("A1")". Now
i
have filled some row values (may be 4 or 5 rows values, it can be some
thing
else). now if i execute the below statement "Set lv_aRange =
lv_aRange.End(xlDown).Select" then it was throwing an rum time error. and
the
selected cell address is A65536.


"Bob Phillips" wrote:

This can happen if the column is empty. You can test for it

If Application.CountA(Columns("A:A")) = 0 Then
Range("A1").Select
Else
Range("A1").End(xlDown).Select
End If


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"vedpatel" wrote
in
message ...

Hi,

The scenario is like this. I have one .Xla file which has a function
called generateReport(). This function will retrieve the data from
Database and display in excel sheet.

Multiple Queried will be executed in generateReport(). I need to know
the last filled cell address so that I can start next value filling
form the next row. For this purpose I have used Range.End(xlDown)
method to know the last filled cell. But while debugging I found that
xldown value is -4121 and this method will return last row cell
address. i.e. $A$65536. so if I tried to move to next row (Set
Range.Offset(2, 0)) then runtime error will be displayed. Who to
resolve this problem? Is there any other method to get last filled
cell
address?

This .Xla file function will be called from VB application.

Thanks
Ved


--
vedpatel

------------------------------------------------------------------------
vedpatel's Profile:
http://www.excelforum.com/member.php...o&userid=31987
View this thread:
http://www.excelforum.com/showthread...hreadid=517208

where does lv_aRange point to, and what is the data in there?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"ved prakash" wrote in message
...
Yes i am getting the Range from start to end, but my requirement is to get
the address of last filled cell address and start filling the next values
from there onwards.
i am using the below method to fill next values once i found the last
filled
cell address.
Set lv_aRange = lv_aRange.Offset(2, 0)
And start filling the values to worksheet. while executing the above
statement it throws run time exception " err No :1004, Error Description :
Application-defined or object-defined error."
Please help to resolve this issue.

Thanks,
Ved

"Bob Phillips" wrote:

You cannot use Select within a Set statement. Set creates an object,
Select
is going to a particular cell, they have no correlation.

You need

Set lv_aRange = lv_aRange.End(xlDown)

but this only creates a range to the cell at the end, not all cells from
start to end, for that you need

Set lv_aRange = Range(lv_aRange, lv_aRange.End(xlDown))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"ved prakash" <ved wrote in message
...
Yes i have tested the below code but still it was going to select the
last
row cell i.e. A65536. I have created a range type variable and set the
range
of the worksheet like this "Set lv_aRange = p_infoSheet.Range("A1")".
Now
i
have filled some row values (may be 4 or 5 rows values, it can be some
thing
else). now if i execute the below statement "Set lv_aRange =
lv_aRange.End(xlDown).Select" then it was throwing an rum time error.
and
the
selected cell address is A65536.


"Bob Phillips" wrote:

This can happen if the column is empty. You can test for it

If Application.CountA(Columns("A:A")) = 0 Then
Range("A1").Select
Else
Range("A1").End(xlDown).Select
End If


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"vedpatel"
wrote
in
message
...

Hi,

The scenario is like this. I have one .Xla file which has a
function
called generateReport(). This function will retrieve the data from
Database and display in excel sheet.

Multiple Queried will be executed in generateReport(). I need to
know
the last filled cell address so that I can start next value
filling
form the next row. For this purpose I have used Range.End(xlDown)
method to know the last filled cell. But while debugging I found
that
xldown value is -4121 and this method will return last row cell
address. i.e. $A$65536. so if I tried to move to next row (Set
Range.Offset(2, 0)) then runtime error will be displayed. Who to
resolve this problem? Is there any other method to get last filled
cell
address?

This .Xla file function will be called from VB application.

Thanks
Ved


--
vedpatel


------------------------------------------------------------------------
vedpatel's Profile:
http://www.excelforum.com/member.php...o&userid=31987
View this thread:
http://www.excelforum.com/showthread...hreadid=517208

In One common template .Xla file i am opening one more excel workbook and
pointing the range to that workbook. The data will be retrieved from
database. while debugging i can see the data were filled in 2-3 cells and
when i am trying to ge the last filled cell address then it gives me last
cell address of the worksheet.



"Bob Phillips" wrote:

where does lv_aRange point to, and what is the data in there?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"ved prakash" wrote in message
...
Yes i am getting the Range from start to end, but my requirement is to get
the address of last filled cell address and start filling the next values
from there onwards.
i am using the below method to fill next values once i found the last
filled
cell address.
Set lv_aRange = lv_aRange.Offset(2, 0)
And start filling the values to worksheet. while executing the above
statement it throws run time exception " err No :1004, Error Description :
Application-defined or object-defined error."
Please help to resolve this issue.

Thanks,
Ved

"Bob Phillips" wrote:

You cannot use Select within a Set statement. Set creates an object,
Select
is going to a particular cell, they have no correlation.

You need

Set lv_aRange = lv_aRange.End(xlDown)

but this only creates a range to the cell at the end, not all cells from
start to end, for that you need

Set lv_aRange = Range(lv_aRange, lv_aRange.End(xlDown))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"ved prakash" <ved wrote in message
...
Yes i have tested the below code but still it was going to select the
last
row cell i.e. A65536. I have created a range type variable and set the
range
of the worksheet like this "Set lv_aRange = p_infoSheet.Range("A1")".
Now
i
have filled some row values (may be 4 or 5 rows values, it can be some
thing
else). now if i execute the below statement "Set lv_aRange =
lv_aRange.End(xlDown).Select" then it was throwing an rum time error.
and
the
selected cell address is A65536.


"Bob Phillips" wrote:

This can happen if the column is empty. You can test for it

If Application.CountA(Columns("A:A")) = 0 Then
Range("A1").Select
Else
Range("A1").End(xlDown).Select
End If


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"vedpatel"
wrote
in
message
...

Hi,

The scenario is like this. I have one .Xla file which has a
function
called generateReport(). This function will retrieve the data from
Database and display in excel sheet.

Multiple Queried will be executed in generateReport(). I need to
know
the last filled cell address so that I can start next value
filling
form the next row. For this purpose I have used Range.End(xlDown)
method to know the last filled cell. But while debugging I found
that
xldown value is -4121 and this method will return last row cell
address. i.e. $A$65536. so if I tried to move to next row (Set
Range.Offset(2, 0)) then runtime error will be displayed. Who to
resolve this problem? Is there any other method to get last filled
cell
address?

This .Xla file function will be called from VB application.

Thanks
Ved


--
vedpatel


------------------------------------------------------------------------
vedpatel's Profile:
http://www.excelforum.com/member.php...o&userid=31987
View this thread:
http://www.excelforum.com/showthread...hreadid=517208