Range/ Rows question
Posted by Greg Woods on August 29, 2001 2:45 PM
Sub Search()
Dim xlObject
Dim xlBook
Dim xlSheet
Dim MyRange As Range
Dim SheetNum As String
SheetNum = InputBox("Enter Date")
Set xlObject = CreateObject("Excel.Application")
Set xlBook = xlObject.Workbooks.Open("C:\WINNT\Profiles\gwoods\Desktop\Daily agents report.xls")
Set xlSheet = xlBook.Sheets
For Each sht In xlBook.Worksheets
If sht.Name = SheetNum Then
Set MyRange = Worksheets("Sheet1").[A2:K2]
For Each c In sht.Range("A1:A126").Cells
If (c.Value) = ("Greg Woods") Then MyRange = ???????
How can I set get my range to be = to the row or range
that contains c. I'm new to this VBA so if there is some better way to do this clue me in.
Posted by Damon Ostrander on August 29, 2001 3:48 PM
Hi Greg,
I'm not absolutely sure what your question is, but I'm going to assume that you want to extend the range A2:K2 to include all the cells whose value contains "Greg Woods". If this is correct then your If...Then... should look like this:
If (c.Value) = ("Greg Woods") Then
Set MyRange = Application.Union(c,MyRange)
EndIf
This performs the range set union of the cell with the previous range defined by MyRange.
Damon
Posted by Greg Woods on August 30, 2001 7:28 AM
What I'm trying to do is go look in another workbook loop through the first coloum until I get the cell with my name and then take that whole row and put it into myrange in a different workbook. So I loop threw and get my name but then I need to assign the row that contains c.Value to MyRange.
Posted by Damon Ostrander on August 30, 2001 1:04 PM
Hi again Greg,
I'm still not sure I completely understand. Do you want the values of the cells in columns A:K of the row that contains the cell c to be placed in the range defined by MyRange? If so, here is the code:
For Each sht In xlBook.Worksheets
If sht.Name = SheetNum Then
Set MyRange = Worksheets("Sheet1").[A2:K2]
For Each c In sht.Range("A1:A126").Cells
If (c.Value) = ("Greg Woods") Then
For iCol = 1 To 11
MyRange.Cells(iCol) = c.EntireRow.Cells(iCol)
Next iCol
Exit For 'Exit c loop
End If
Next c
...
Am I getting closer?
Damon
