Finding range

S

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

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
 
S

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
 
R

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

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

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top