Finding range

[Swingleft]

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..

 

[Don Guillett]

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

 

[Swingleft]

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

 

[Ron Rosenfeld]

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
===================

 

[Ron Rosenfeld]

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
=======================

 

[Swingleft]

Thanks!!!!!!


this is exactly what i need....

gr.

Swingleft



"Ron Rosenfeld" schreef in bericht

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
=======================

 

[Ron Rosenfeld]

Thanks!!!!!!


this is exactly what i need....

gr.

Swingleft

Glad to help. Thanks for the feedback.