Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select Non Contigouos Ranges
I need to select all the cells underneath a "found" cell in column A and then
also the corresponding cells four columns to the right. Here is what I have, it selects the entire range including columns in between and an "Object Required" error. Dim FoundDate As Range Set FoundDate = Columns("A:A").Find(What:="Date", After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= False, SearchFormat:=False) '.Activate Union(Range(FoundDate.Offset(1, 0), Selection.End(xlDown)).Select, Range(FoundDate.Offset(0, 4), Selection.End(xlDown)).Select).Copy I also struggle to understand set, object required dilema, any reference in plain English? Thanks in advance |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select Non Contigouos Ranges
Range(FoundDate.Offset(1, 0), FoundDate.End(xlDown)).Resize(,2).Select
-- __________________________________ HTH Bob "LuisE" wrote in message ... I need to select all the cells underneath a "found" cell in column A and then also the corresponding cells four columns to the right. Here is what I have, it selects the entire range including columns in between and an "Object Required" error. Dim FoundDate As Range Set FoundDate = Columns("A:A").Find(What:="Date", After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= False, SearchFormat:=False) '.Activate Union(Range(FoundDate.Offset(1, 0), Selection.End(xlDown)).Select, Range(FoundDate.Offset(0, 4), Selection.End(xlDown)).Select).Copy I also struggle to understand set, object required dilema, any reference in plain English? Thanks in advance |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select Non Contigouos Ranges
Maybe this
Sub standard() Dim rFoundCell As Range Set rFoundCell = Range("A1") Set rFoundCell = Columns(1).Find(What:="DATE", After:=rFoundCell, _ LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=False) myrange = Range(rFoundCell.Address).Resize(65537 - rFoundCell.Row, 5).Copy End Sub "LuisE" wrote: I need to select all the cells underneath a "found" cell in column A and then also the corresponding cells four columns to the right. Here is what I have, it selects the entire range including columns in between and an "Object Required" error. Dim FoundDate As Range Set FoundDate = Columns("A:A").Find(What:="Date", After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= False, SearchFormat:=False) '.Activate Union(Range(FoundDate.Offset(1, 0), Selection.End(xlDown)).Select, Range(FoundDate.Offset(0, 4), Selection.End(xlDown)).Select).Copy I also struggle to understand set, object required dilema, any reference in plain English? Thanks in advance |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select Non Contigouos Ranges
I believe this is what you are looking for
Sub Test() Dim aWS As Worksheet Dim FoundDate As Range Dim lRow as long Set aWS = ActiveSheet Set FoundDate = Nothing On Error Resume Next Set FoundDate = aWS.Columns("A:A").Find(What:="Date", _ After:=ActiveCell, _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False, _ SearchFormat:=False) If FoundDate Is Nothing Then MsgBox ("Nothing found") Exit Sub End If lrow = FoundDate.End(xlDown).Row Set FoundDate = FoundDate.Resize(lrow - FoundDate.Row + 1, 1) Debug.Print FoundDate.Address Set FoundDate = Union(FoundDate, FoundDate.Offset(0, 4)) 'Change offset as needed Debug.Print FoundDate.Address End Sub -- HTH, Barb Reinhardt "LuisE" wrote: I need to select all the cells underneath a "found" cell in column A and then also the corresponding cells four columns to the right. Here is what I have, it selects the entire range including columns in between and an "Object Required" error. Dim FoundDate As Range Set FoundDate = Columns("A:A").Find(What:="Date", After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= False, SearchFormat:=False) '.Activate Union(Range(FoundDate.Offset(1, 0), Selection.End(xlDown)).Select, Range(FoundDate.Offset(0, 4), Selection.End(xlDown)).Select).Copy I also struggle to understand set, object required dilema, any reference in plain English? Thanks in advance |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select Non Contigouos Ranges
I think this does what you want...
Dim FoundDate As Range Set FoundDate = Columns("A").Find(What:="Date", After:=ActiveCell, _ LookIn:=xlFormulas, SearchOrder:=xlByRows, _ LookAt:=xlPart, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Offset(1, 0) With FoundDate Union(.Resize(.End(xlDown).Row - .Row + 1), _ .Resize(.End(xlDown).Row - .Row + 1).Offset(, 4)).Select End With Rick "LuisE" wrote in message ... I need to select all the cells underneath a "found" cell in column A and then also the corresponding cells four columns to the right. Here is what I have, it selects the entire range including columns in between and an "Object Required" error. Dim FoundDate As Range Set FoundDate = Columns("A:A").Find(What:="Date", After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= False, SearchFormat:=False) '.Activate Union(Range(FoundDate.Offset(1, 0), Selection.End(xlDown)).Select, Range(FoundDate.Offset(0, 4), Selection.End(xlDown)).Select).Copy I also struggle to understand set, object required dilema, any reference in plain English? Thanks in advance |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select Non Contigouos Ranges
I'm sorry I misformulated my question.
I want those cells from Columns A and E and ignore the ones in between. "Bob Phillips" wrote: Range(FoundDate.Offset(1, 0), FoundDate.End(xlDown)).Resize(,2).Select -- __________________________________ HTH Bob "LuisE" wrote in message ... I need to select all the cells underneath a "found" cell in column A and then also the corresponding cells four columns to the right. Here is what I have, it selects the entire range including columns in between and an "Object Required" error. Dim FoundDate As Range Set FoundDate = Columns("A:A").Find(What:="Date", After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= False, SearchFormat:=False) '.Activate Union(Range(FoundDate.Offset(1, 0), Selection.End(xlDown)).Select, Range(FoundDate.Offset(0, 4), Selection.End(xlDown)).Select).Copy I also struggle to understand set, object required dilema, any reference in plain English? Thanks in advance |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select Non Contigouos Ranges
I'm sorry I misformulated my question.
I want those cells from Columns A and E and ignore the ones in between. "LuisE" wrote: I need to select all the cells underneath a "found" cell in column A and then also the corresponding cells four columns to the right. Here is what I have, it selects the entire range including columns in between and an "Object Required" error. Dim FoundDate As Range Set FoundDate = Columns("A:A").Find(What:="Date", After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= False, SearchFormat:=False) '.Activate Union(Range(FoundDate.Offset(1, 0), Selection.End(xlDown)).Select, Range(FoundDate.Offset(0, 4), Selection.End(xlDown)).Select).Copy I also struggle to understand set, object required dilema, any reference in plain English? Thanks in advance |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select Non Contigouos Ranges
In what way does the code I posted not do what you want?
Rick "LuisE" wrote in message ... I'm sorry I misformulated my question. I want those cells from Columns A and E and ignore the ones in between. "LuisE" wrote: I need to select all the cells underneath a "found" cell in column A and then also the corresponding cells four columns to the right. Here is what I have, it selects the entire range including columns in between and an "Object Required" error. Dim FoundDate As Range Set FoundDate = Columns("A:A").Find(What:="Date", After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= False, SearchFormat:=False) '.Activate Union(Range(FoundDate.Offset(1, 0), Selection.End(xlDown)).Select, Range(FoundDate.Offset(0, 4), Selection.End(xlDown)).Select).Copy I also struggle to understand set, object required dilema, any reference in plain English? Thanks in advance |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select Non Contigouos Ranges
to copy columns A & E try
Sub standard() Dim rFoundCell As Range Dim myrange As Range Dim myrange1 As Range Dim copyrange As Range Set rFoundCell = Range("A1") Set rFoundCell = Columns(1).Find(What:="DATE", After:=rFoundCell, _ LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=False) Set myrange = Range(rFoundCell.Address).Resize(65537 - rFoundCell.Row, 1) Set myrange1 = Range(rFoundCell.Address).Offset(, 4).Resize(65537 - rFoundCell.Row, 1) Set copyrange = Union(myrange, myrange1) copyrange.Copy End Sub Mike "LuisE" wrote: I'm sorry I misformulated my question. I want those cells from Columns A and E and ignore the ones in between. "Bob Phillips" wrote: Range(FoundDate.Offset(1, 0), FoundDate.End(xlDown)).Resize(,2).Select -- __________________________________ HTH Bob "LuisE" wrote in message ... I need to select all the cells underneath a "found" cell in column A and then also the corresponding cells four columns to the right. Here is what I have, it selects the entire range including columns in between and an "Object Required" error. Dim FoundDate As Range Set FoundDate = Columns("A:A").Find(What:="Date", After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= False, SearchFormat:=False) '.Activate Union(Range(FoundDate.Offset(1, 0), Selection.End(xlDown)).Select, Range(FoundDate.Offset(0, 4), Selection.End(xlDown)).Select).Copy I also struggle to understand set, object required dilema, any reference in plain English? Thanks in advance |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select Non Contigouos Ranges
I went with your Subject line, but in re-looking at your code, it appears
you don't need to select the range... your ultimate goal is to simply Copy it. Change the .Select in the last code line (the Union statement) to .Copy in order to do that. Rick "Rick Rothstein (MVP - VB)" wrote in message ... I think this does what you want... Dim FoundDate As Range Set FoundDate = Columns("A").Find(What:="Date", After:=ActiveCell, _ LookIn:=xlFormulas, SearchOrder:=xlByRows, _ LookAt:=xlPart, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Offset(1, 0) With FoundDate Union(.Resize(.End(xlDown).Row - .Row + 1), _ .Resize(.End(xlDown).Row - .Row + 1).Offset(, 4)).Select End With Rick "LuisE" wrote in message ... I need to select all the cells underneath a "found" cell in column A and then also the corresponding cells four columns to the right. Here is what I have, it selects the entire range including columns in between and an "Object Required" error. Dim FoundDate As Range Set FoundDate = Columns("A:A").Find(What:="Date", After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= False, SearchFormat:=False) '.Activate Union(Range(FoundDate.Offset(1, 0), Selection.End(xlDown)).Select, Range(FoundDate.Offset(0, 4), Selection.End(xlDown)).Select).Copy I also struggle to understand set, object required dilema, any reference in plain English? Thanks in advance |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select Non Contigouos Ranges
Dim FoundDate As Range
Set FoundDate = Columns("A:A").Find(What:="Date", After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= False, SearchFormat:=False) Set rng = Range(FoundDate.Offset(1, 0), FoundDate.End(xlDown)) Set rng = Union(rng, rng.Offset(0,4)) rng.Select -- __________________________________ HTH Bob "LuisE" wrote in message ... I'm sorry I misformulated my question. I want those cells from Columns A and E and ignore the ones in between. "Bob Phillips" wrote: Range(FoundDate.Offset(1, 0), FoundDate.End(xlDown)).Resize(,2).Select -- __________________________________ HTH Bob "LuisE" wrote in message ... I need to select all the cells underneath a "found" cell in column A and then also the corresponding cells four columns to the right. Here is what I have, it selects the entire range including columns in between and an "Object Required" error. Dim FoundDate As Range Set FoundDate = Columns("A:A").Find(What:="Date", After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= False, SearchFormat:=False) '.Activate Union(Range(FoundDate.Offset(1, 0), Selection.End(xlDown)).Select, Range(FoundDate.Offset(0, 4), Selection.End(xlDown)).Select).Copy I also struggle to understand set, object required dilema, any reference in plain English? Thanks in advance |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select Non Contigouos Ranges
Your code came after I posted the reply. It works just fine thanks a lot.
Thank you ALL "Rick Rothstein (MVP - VB)" wrote: In what way does the code I posted not do what you want? Rick "LuisE" wrote in message ... I'm sorry I misformulated my question. I want those cells from Columns A and E and ignore the ones in between. "LuisE" wrote: I need to select all the cells underneath a "found" cell in column A and then also the corresponding cells four columns to the right. Here is what I have, it selects the entire range including columns in between and an "Object Required" error. Dim FoundDate As Range Set FoundDate = Columns("A:A").Find(What:="Date", After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= False, SearchFormat:=False) '.Activate Union(Range(FoundDate.Offset(1, 0), Selection.End(xlDown)).Select, Range(FoundDate.Offset(0, 4), Selection.End(xlDown)).Select).Copy I also struggle to understand set, object required dilema, any reference in plain English? Thanks in advance |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Select ranges of data with a macro | Excel Programming | |||
macro to search for & select ranges to sum | Excel Programming | |||
Inputbox - Select ranges | Excel Programming | |||
Select instersection of two ranges | Excel Programming | |||
VBA-Select several ranges using variables | Excel Programming |