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

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

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?

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.

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

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

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

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