Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search Range for string, then copy that cell to another worksheet
On Worksheet1 I've got 6 sets of ranges (D34:D36, D50:D62, D66:D78, and so on
for a total of 6 ranges) that may contain the string "ACS" somewhere in the cell. I want to search these ranges, and copy both cells to the left of the cell containing "ACS", the actual cell containing "ACS", and the cell directly to the right to Worksheet2. For example, Worksheet1!D50 contains "ACS" so the cells in Worksheet1!B50:E50 are copied to a range in Worksheet2 (A8:D20) specifically in the first row of the range A8:D8, because it is the first instance of "ACS" found in the source cells. When "ACS" is found again in Worksheet1 (say in cell D60), the cells in Worksheet1!B60:E60 are copied to the 2nd row of the range in Worksheet2 (A9:D9). And so on until all the ranges in Worksheet1 have been searched. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search Range for string, then copy that cell to another worksheet
Sub Macro1()
Dim c As Range Dim rng As Range Dim iRow As Integer Set rng = Worksheets(1).Range("D34:D36, D50:D62, D66:D78") iRow = 8 For Each c In rng If InStr(c, "ACS") 0 Then Worksheets(1).Range("B" & c.Row & ":E" & c.Row).Copy _ Destination:=Worksheets(2).Range("A" & iRow & ":D" & iRow) iRow = iRow + 1 End If Next c End Sub Hth, Merjet |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Search text string for ssn and copy to new column | Excel Discussion (Misc queries) | |||
Loop through range of cells, string search, take action | Excel Programming | |||
Search within range for first populated cell and copy to new workbook | Excel Programming | |||
How to macro: 1.Search for string - 2.Copy row - 3.Paste in new sheet | Excel Programming | |||
search range, if exist, copy cell | Excel Programming |