i have a list of words in colA of sheet1. i want a macro to copy every row that have the string "red" and paste it to colA in sheet2. ColA can have more than 1 word per cell
i have a list of words in colA of sheet1. i want a macro to copy every row that have the string "red" and paste it to colA in sheet2. ColA can have more than 1 word per cell
Maybe this will work:
[VBA]
Option Explicit
Sub FindCodes()
Dim rngToSearch As Range
Dim cel1 As Range
Dim c As Range
Dim counter As Integer
Dim firstAddress As String
Dim MyInput As String
Sheets("Output Sheet").UsedRange.ClearContents
counter = 1 'start output in row 1
MyInput = InputBox("Search for String...", "Search", "Enter your search sting here")
Set rngToSearch = Sheets("Sheet2").Columns(1)
Set c = rngToSearch.Find(What:=MyInput, LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.EntireRow.Copy Worksheets("Output Sheet").Rows(counter)
counter = counter + 1
Set c = rngToSearch.FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
Sheets("Output Sheet").Select
End Sub
[/VBA]
example attached
Steve
"Nearly all men can stand adversity, but if you want to test a man's character, give him power."
-Abraham Lincoln
this works perfect but is it possible to modify the macro so that i can hardcode the keyword in the macro instead of being on a input box
sure, just change this line:
[VBA]MyInput = InputBox("Search for String...", "Search", "Enter your search sting here")
[/VBA]
to something like this:
[VBA]
MyInput = "xxxx"
[/VBA]
where xxxx is the string you want to search for.
Steve
"Nearly all men can stand adversity, but if you want to test a man's character, give him power."
-Abraham Lincoln
thanks this works perfect
I have a similar problem, but I need to modify the code to strip off the characters that are not numbers. I've attached the data I am working with and I need to have the name of the servo, which is on the far left, column and one row down, along with the numbers (oz, oz-in, & sec) associated with it in order to create a chart. Apparently Excel doesn't like anything in the cell but numbers when you try to make a chart.
I attempted to make sense of the code posted above in order to modify it but my coding must be really simple, because I usually can't tell what's going on. I usually work with embedded systems. In one of my attempts at modification, I wound up printing garbage in rows instead of columns, in another I created an infinite loop.
Thank you for any help you might be able to offer.