Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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.. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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.. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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.. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 =================== |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 ======================= |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 ======================= |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding the corresponding value in a range? | Excel Worksheet Functions | |||
finding a value between a range | Excel Worksheet Functions | |||
Finding the End of a Range | Excel Programming | |||
Finding a value associated with a range | Excel Worksheet Functions | |||
Finding a Range Name | Excel Programming |