Finding range
 

Hello,

i'm having a small problem...

i need to find the last cell with data in collum E.

and then copy a a pre determent string in a range
from G2 to Gxx where the xx the last row of collum E is.

thanks for all the help..

Swingleft..

Finding range
 

sub flrc()
dim lr as long
lr=cells(rows.count,"e").end(xlup).row
cells(2,"g").resize(lr-1).copy range("h1")
end sub


On Nov 7, 7:00*am, "Swingleft" wrote:
Hello,

i'm having a small problem...

i need to find the last cell with data in collum E.

and then copy a a pre determent string in a range
from G2 to Gxx where the xx the last row of collum E is.

thanks for all the help..

Swingleft..

Finding range
 

Hi Don,

thanks for trying to help..
can you explain what the .resize does..
and how i can get the value "help" (for instance)
in the range G2 to Gxx

the .copy range("h1") isn't working :-(..

thanks..

"Don Guillett" schreef in bericht
...

sub flrc()
dim lr as long
lr=cells(rows.count,"e").end(xlup).row
cells(2,"g").resize(lr-1).copy range("h1")
end sub


On Nov 7, 7:00 am, "Swingleft" wrote:
Hello,

i'm having a small problem...

i need to find the last cell with data in collum E.

and then copy a a pre determent string in a range
from G2 to Gxx where the xx the last row of collum E is.

thanks for all the help..

Swingleft..

Finding range
 

On Mon, 7 Nov 2011 14:00:44 +0100, "Swingleft" wrote:

Hello,

i'm having a small problem...

i need to find the last cell with data in collum E.

and then copy a a pre determent string in a range
from G2 to Gxx where the xx the last row of collum E is.

thanks for all the help..

Swingleft..




If what you want is to return the string in Gxx where xx is the last row of data in column E, that can also be done simply with a worksheet formula:

=LOOKUP(2,1/(LEN($E:$E)1),$G:$G)

If you must do it in code, using the Range.Copy method and Pasting the result to a new destination, something like:

==============
Option Explicit
Sub GString()
Dim rg As Range
Set rg = Cells(Cells.Rows.Count, "E").End(xlUp).Offset(columnoffset:=2)
rg.Copy Destination:=Range("J1")
End Sub
===================

Finding range
 

On Tue, 08 Nov 2011 07:04:06 -0500, Ron Rosenfeld wrote:

On Mon, 7 Nov 2011 14:00:44 +0100, "Swingleft" wrote:

Hello,

i'm having a small problem...

i need to find the last cell with data in collum E.

and then copy a a pre determent string in a range
from G2 to Gxx where the xx the last row of collum E is.

thanks for all the help..

Swingleft..




If what you want is to return the string in Gxx where xx is the last row of data in column E, that can also be done simply with a worksheet formula:

=LOOKUP(2,1/(LEN($E:$E)1),$G:$G)

If you must do it in code, using the Range.Copy method and Pasting the result to a new destination, something like:

==============
Option Explicit
Sub GString()
Dim rg As Range
Set rg = Cells(Cells.Rows.Count, "E").End(xlUp).Offset(columnoffset:=2)
rg.Copy Destination:=Range("J1")
End Sub
===================


I see from your response to Don that I may have misinterpreted your request. So also try:

=================
Option Explicit
Sub GString()
Dim rg As Range
Const s As String = "HELP"
Set rg = Range("E2", Cells(Cells.Rows.Count, "E").End(xlUp)).Offset(columnoffset:=2)
rg = s
End Sub
=======================

Finding range
 

Thanks!!!!!!


this is exactly what i need....

gr.

Swingleft



"Ron Rosenfeld" schreef in bericht
...

On Tue, 08 Nov 2011 07:04:06 -0500, Ron Rosenfeld wrote:

On Mon, 7 Nov 2011 14:00:44 +0100, "Swingleft" wrote:

Hello,

i'm having a small problem...

i need to find the last cell with data in collum E.

and then copy a a pre determent string in a range
from G2 to Gxx where the xx the last row of collum E is.

thanks for all the help..

Swingleft..




If what you want is to return the string in Gxx where xx is the last row of
data in column E, that can also be done simply with a worksheet formula:

=LOOKUP(2,1/(LEN($E:$E)1),$G:$G)

If you must do it in code, using the Range.Copy method and Pasting the
result to a new destination, something like:

==============
Option Explicit
Sub GString()
Dim rg As Range
Set rg = Cells(Cells.Rows.Count, "E").End(xlUp).Offset(columnoffset:=2)
rg.Copy Destination:=Range("J1")
End Sub
===================


I see from your response to Don that I may have misinterpreted your request.
So also try:

=================
Option Explicit
Sub GString()
Dim rg As Range
Const s As String = "HELP"
Set rg = Range("E2", Cells(Cells.Rows.Count,
"E").End(xlUp)).Offset(columnoffset:=2)
rg = s
End Sub
=======================

Finding range
 

On Tue, 8 Nov 2011 16:09:49 +0100, "Swingleft" wrote:

Thanks!!!!!!


this is exactly what i need....

gr.

Swingleft


Glad to help. Thanks for the feedback.