Search Column then Paste Data at end of Row?
 

Hi,
I am going nowhere quick searching for this, so hopefully someone can
help me.

I have a sheet with "x" number of rows of data. I would like to search

the second column for a specific string. Each time the string is
found, I need to paste the value of variable "var1" and "var2" into
column Q and R of that row. I would also like to know how many times
the string was found at paste value into the first column of that row.


I'm sure I could use a For loop to do this, but the data sheet can be
very long and I was wondering if there was a better way to do this with

the ".find()" or ".advancedfilter()" functions.


Thanks,
Logan

Search Column then Paste Data at end of Row?
 

You can try setting all of the cells containing the strings to be found in a
range using something like this:

Lastrow= Range("P65536").end(xlup).row
set Rng = Range("P1:P" & Lastrow)
Then loop through each occurrence comparing it to what is in your Column A
for example, and if found use the cell.offset functionality to place the var1
or var2 value. During the same loop you can insert a counter x=x+1 or
something similar. And when it gets out of the loop reset the x value to 0,
taking first the current value of x and placing it into the cell of your
choice.

" wrote:

Hi,
I am going nowhere quick searching for this, so hopefully someone can
help me.

I have a sheet with "x" number of rows of data. I would like to search

the second column for a specific string. Each time the string is
found, I need to paste the value of variable "var1" and "var2" into
column Q and R of that row. I would also like to know how many times
the string was found at paste value into the first column of that row.


I'm sure I could use a For loop to do this, but the data sheet can be
very long and I was wondering if there was a better way to do this with

the ".find()" or ".advancedfilter()" functions.


Thanks,
Logan

Search Column then Paste Data at end of Row?
 

You could loop through each find until it wrapped to the top:

Sub Macro2()
Dim intFirstHit As Integer

'you can change this range to be what you want
With Range("A1:A50")
.Select
'find first hit
.Find(What:="b", After:=ActiveCell, LookIn:=xlFormulas, LookAt
_
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False).Activate
'mark first hit
intFirstHit = ActiveCell.Row
Range("Q" & ActiveCell.Row).Value = var1
Range("R" & ActiveCell.Row).Value = var2
Range("A1:A50").FindNext(After:=ActiveCell).Activa te
'do until you wrap around to the top of the selection
Do While ActiveCell.Row intFirstHit
Range("Q" & ActiveCell.Row).Value = var1
Range("R" & ActiveCell.Row).Value = var2
'find each other instance
.FindNext(After:=ActiveCell).Activate
Loop
End With
End Sub

I didn't understand exactly what you wanted with the second request.
Are you looking to keep a running tally of how many times you found the
string you were looking for or were you looking to see how many times a
single string appeared in a cell?

Search Column then Paste Data at end of Row?
 

Logan,
Is the Second Column, Column B? and how var1 and var2 determined? Also
what determines the value your searching in column 2. The rest is pretty
much straight by using Find, and Find Next method. Posted me back and see if
I can give you a hand...

Rick (Fbks, AK)


wrote in message
oups.com...
Hi,
I am going nowhere quick searching for this, so hopefully someone can
help me.

I have a sheet with "x" number of rows of data. I would like to search

the second column for a specific string. Each time the string is
found, I need to paste the value of variable "var1" and "var2" into
column Q and R of that row. I would also like to know how many times
the string was found at paste value into the first column of that row.


I'm sure I could use a For loop to do this, but the data sheet can be
very long and I was wondering if there was a better way to do this with

the ".find()" or ".advancedfilter()" functions.


Thanks,
Logan

Search Column then Paste Data at end of Row?
 

Thanks. That looks like it should work. Except I don't know what the
range is. I will just have a variable telling me how many rows are in
the sheet.

I would like a running tally of how many times the string was found.
And paste that in column 1 of each found instance or maybe just column
1 of the first hit.

Search Column then Paste Data at end of Row?
 

You could just include a counter variable in the loop and put it in the
column. Something like this:

Sub Macro2()
Dim intFirstHit As Integer
Dim i as double
'you can change this range to be what you want
With Range("A1:A50")
.Select
'find first hit
.Find(What:="b", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False).Activate
'mark first hit
intFirstHit = ActiveCell.Row
i = 1
Range("Q" & ActiveCell.Row).Value = var1
Range("R" & ActiveCell.Row).Value = var2
Range("S" & ActiveCell.Row).Value = i
.FindNext(After:=ActiveCell).Activate
'do until you wrap around to the top of the selection
Do While ActiveCell.Row intFirstHit
i = i + 1
Range("Q" & ActiveCell.Row).Value = var1
Range("R" & ActiveCell.Row).Value = var2
Range("S" & ActiveCell.Row).Value = i
'find each other instance
.FindNext(After:=ActiveCell).Activate
Loop
End With
End Sub

Search Column then Paste Data at end of Row?
 

Here is a more elegant way to do this code that I found at ozgrid:

http://www.ozgrid.com/VBA/find-method.htm

Dim lCount As Long
Dim rFoundCell As Range

Set rFoundCell = Range("A1")

For lCount = 1 To WorksheetFunction.CountIf(Columns(1), "Cat")
Set rFoundCell = Columns(1).Find(What:="Cat",
After:=rFoundCell, _
LookIn:=xlValues, LookAt:=xlPart,
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)

With rFoundCell
.ClearComments
.AddComment Text:="Cat lives here"
End With

Next lCount
End Sub

Search Column then Paste Data at end of Row?
 

Thanks for all the help guys. I tried Brian's suggestion before I left
work, but I was getting an error. I'll try again on Monday and let you
know how it works.

BTW, I saw the tip at ozgrid, but had no idea how to use that and add
the data at the end of the row.


Also, dmthornton gave me this solution:

Quote:
I'm glad I saw your posting. I never thought of using the
advancedfilter
function for something like this, but I think it could save time. I
played
arround with it and came up with this. I think if you tweek it, it
should
work.

Selection.AutoFilter Field:=2, Criteria1:="FindThisString"
Dim lngCount As Long
lngCount = Range("B2",
Range("B65536").End(xlUp)).SpecialCells(xlCellType Visible).Count
'lngCount
will give you the totoal number of rows


'Do for only the visible cells
For Each c In Range("B2",
Range("B65536").End(xlUp)).SpecialCells(xlCellType Visible)
c.Offset(0, 15) = "new value"
c.Offset(0, 16) = "new value"
Next

Search Column then Paste Data at end of Row?
 

I tried dmthorton's solution and it seems to be working. But it put
makes all of the columns into sorting lists. How do I prevent this
from happening?

Also, I am running this in a while loop for several different criteria
with different values being inserted for each criteria. But it only
works when I try one value. When it searches for the next value, I
clears the values that were previously inserted. Do I need to reset
the previous AutoFilter state?

Thanks,
Logan

Search Column then Paste Data at end of Row?
 

Did you switch out the search string for what you were looking for? I
put "b" in as my search string.

To manipulate the ozgrid code to do what you want, you would just need
to use the range variable rFoundCell. Something like this:

sub IsntThisSwell

Dim lCount As Long
Dim rFoundCell As Range
Dim strSearch as String

strSearch = "Whatever it is you are looking for"
Set rFoundCell = Range("A1") 'This should be the first cell in the
range you are looking in

For lCount = 1 To WorksheetFunction.CountIf(Columns(1), strSearch)
Set rFoundCell = Columns(1).Find(What:=strSearch, _
After:=rFoundCell, LookIn:=xlValues, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False)
With rFoundCell
Range("Q" & .Row).Value = var1
Range("R" & .Row).Value = var2
Range("S" & .Row).Value = lCount 'this is a running tally of
hits
End With
Next lCount

End Sub

Search Column then Paste Data at end of Row?
 

Brian,
I was able to get your solution working. Thanks!

Here's my final code. (I have implemented the running tally.) I also
had to change the search criteria b/c it would give me an error if it
found nothing.

Dim intFirstHit As Integer
Dim eFinder As Variant

'you can change this range to be what you want
With Worksheets(strName).Range("B1:B" & Num_eRows)
.Select
'Find first hit
Set eFinder = .Find(What:=eNameVar, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)

If Not eFinder Is Nothing Then
'Mark First Hit
intFirstHit = eFinder.Row
Range("Q" & eFinder.Row).Value = e_Min_val
Range("R" & eFinder.Row).Value = e_Max_val
Set eFinder = .FindNext(After:=eFinder)

'Do until you wrap around to the top of the selection
Do While eFinder.Row intFirstHit
Range("Q" & eFinder.Row).Value = e_Min_val
Range("R" & eFinder.Row).Value = e_Max_val
'Find each other instance
Set eFinder = .FindNext(After:=eFinder)
Loop
End If
End With

Search Column then Paste Data at end of Row?
 

Glad it worked out.