Search for any value within a range and copy
Solved/Closed
Sarah89
-
Dec 7, 2011 at 05:39 AM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Dec 29, 2011 at 06:45 AM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Dec 29, 2011 at 06:45 AM
Related:
- Search for any value within a range and copy
- How to search for a word within a website - Guide
- Youtube search within channel - Guide
- Google.us search - Guide
- Yahoo search history - Guide
- Safe search settings - Guide
8 responses
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Dec 7, 2011 at 09:19 PM
Dec 7, 2011 at 09:19 PM
try this macro
Sub test()
Dim r As Range, c As Range
Worksheets("A2").Cells.Clear
Worksheets("sheet1").Activate
Set r = Range(Range("K1"), Range("O1").End(xlDown))
For Each c In r
c.Select
If c = "Data Dump" Then
c.EntireRow.Copy Worksheets("A2").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
End If
Next c
End Sub
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Dec 8, 2011 at 07:41 PM
Dec 8, 2011 at 07:41 PM
"Subscript out of range" means there is not sheet as A2
I misunderstood your "new sheet within the same workbook cell A2."
change that statements wherever occurs "worksheets("A2")" (2 places)
change it to correct worksheet name ---replace only A2 by the name of the sheet where you want to have the result.
change this statement
to
I misunderstood your "new sheet within the same workbook cell A2."
change that statements wherever occurs "worksheets("A2")" (2 places)
change it to correct worksheet name ---replace only A2 by the name of the sheet where you want to have the result.
change this statement
Set r = Range(Range("K1"), Range("O1").End(xlDown))
to
Set r = Range(Range("M1"), Range("O1").End(xlDown))
Hi venkat,
This is the macro that I have now ...
Sub test()
Dim r As Range, c As Range
Worksheets("Changes").Cells.Clear
Worksheets("Data Dump").Activate
Set r = Range(Range("M1"), Range("O1").End(xlDown))
For Each c In r
c.Select
If c = "Data Dump" Then
c.EntireRow.Copy Worksheets("Changes").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
End If
Next c
End Sub
Worksheets("Changes").Cells.Clear >>> This is the worksheet i want the results to be in.
Worksheets("Data Dump").Activate >>> And this is the worksheet that contains the info i want to search for values.
Could you please explain to me this part of the macro?
For Each c In r
c.Select
If c = "Data Dump" Then
c.EntireRow.Copy Worksheets("Changes").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
End If
Next c
End Sub
If i understand it right, I want 'c' to represent any value present in the range that has been set as 'r'. Then, if a 'c' (a populated cell) has been found, copy entire row and paste to Worksheet("Changes") ?
This is the macro that I have now ...
Sub test()
Dim r As Range, c As Range
Worksheets("Changes").Cells.Clear
Worksheets("Data Dump").Activate
Set r = Range(Range("M1"), Range("O1").End(xlDown))
For Each c In r
c.Select
If c = "Data Dump" Then
c.EntireRow.Copy Worksheets("Changes").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
End If
Next c
End Sub
Worksheets("Changes").Cells.Clear >>> This is the worksheet i want the results to be in.
Worksheets("Data Dump").Activate >>> And this is the worksheet that contains the info i want to search for values.
Could you please explain to me this part of the macro?
For Each c In r
c.Select
If c = "Data Dump" Then
c.EntireRow.Copy Worksheets("Changes").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
End If
Next c
End Sub
If i understand it right, I want 'c' to represent any value present in the range that has been set as 'r'. Then, if a 'c' (a populated cell) has been found, copy entire row and paste to Worksheet("Changes") ?
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Dec 9, 2011 at 05:55 AM
Dec 9, 2011 at 05:55 AM
r is the range of cells from M1 to the last cell in column O
suppose you have five rows. then
r is range from M1 to O5
that means 5x3=15 cells
for each c in r
each of these 15 cells is considered one by one
first the first cell N1
so c is M1
c.select
this statement is not necessary . parked there only for debugging.
then it check c (that is M1 is having entry "Data Dump"
if so then it copied the whole row of M1 if not it skips and goes to next c which is N1 .
then the test and decision. etc
when all the 15 cells checked it goes out of "for -----next"
and then if there is nothing else to do closed the macro
suppose you have five rows. then
r is range from M1 to O5
that means 5x3=15 cells
for each c in r
each of these 15 cells is considered one by one
first the first cell N1
so c is M1
c.select
this statement is not necessary . parked there only for debugging.
then it check c (that is M1 is having entry "Data Dump"
if so then it copied the whole row of M1 if not it skips and goes to next c which is N1 .
then the test and decision. etc
when all the 15 cells checked it goes out of "for -----next"
and then if there is nothing else to do closed the macro
Thankyou. I am learning! ... Slowly =P
What code would I use to search for any non-blank cell? As I am not looking for specific values or words =S
What code would I use to search for any non-blank cell? As I am not looking for specific values or words =S
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Dec 9, 2011 at 09:01 PM
Dec 9, 2011 at 09:01 PM
there must be many ways of selecting non blank cells depending upon why you want to do it
suppose there are only constants (no formulas) then
range("A1").CurrentRegion.SpecialCells(xlCellTypeConstants).Select
you solve the problem when the need arise
suppose there are only constants (no formulas) then
range("A1").CurrentRegion.SpecialCells(xlCellTypeConstants).Select
you solve the problem when the need arise
Sub Changes()
Dim r As Range, c As Range
Worksheets("Changes").Cells.Clear
Worksheets("Data Dump").Activate
Set r = Range(Range("N2"), Range("Q2").End(xlDown))
For Each c In r
c.Select
If c.Value <> "" Then
c.EntireRow.Copy Worksheets("Changes").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
End If
Next c
End Sub
I searched for non-blank formula and the above has now worked =D Thanks for all your help!
Dim r As Range, c As Range
Worksheets("Changes").Cells.Clear
Worksheets("Data Dump").Activate
Set r = Range(Range("N2"), Range("Q2").End(xlDown))
For Each c In r
c.Select
If c.Value <> "" Then
c.EntireRow.Copy Worksheets("Changes").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
End If
Next c
End Sub
I searched for non-blank formula and the above has now worked =D Thanks for all your help!
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Dec 29, 2011 at 06:45 AM
Dec 29, 2011 at 06:45 AM
one small sophistication(nor correction)
quote
unquote
c is a range. the default property of a range is its value as long as it is one cell
so it is enought
try
quote
If c.Value <> "" Then
unquote
c is a range. the default property of a range is its value as long as it is one cell
so it is enought
If c<> "" Then
try
Dec 8, 2011 at 05:40 AM
Thanks for the response but unfortunately it returns 'RunTime error 9. Subscript out of range' :S
Any suggestions? Columns M-O (Correction from previous note) Provide an optional response column in which most cells are blank. The cells which are populated need the whole row to be copied to a new sheet within the workbook so all changes that need to be made are compiled.
Thanks Again