Hi All,
I'm new to VBA, and have been searching for threads with similar issue but can't seem to find exactly what i'm trying to do.
I have price data in column A, and a text column in column F. I am searching for 2 specific text "BUYOPEN" and "SELLCLOSE" in column F. Once those are found, i am attempting to find its corresponding price from the same row in column A.
The macro i have so far, is finding the text "BUYOPEN" and placing the corresponding price in the next column. but i would like to add/update the code to summarize this data in a 2nd worksheet.
So everytime there is a "BUYOPEN" or "SELLCLOSE", it will list each occurence along with the corresponding price in a new worksheet.
appreciate any help. thanks
sample data[TABLE="width: 336"]
<TBODY>[TR]
[TD="width: 64, bgcolor: transparent, align: right"]51.6323
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]0
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]0
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]0
[/TD]
[TD="width: 64, bgcolor: transparent"]SELL
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]0
[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]52.08857
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent"]SELL
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]52.1537
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent"]BUY
[/TD]
[TD="bgcolor: transparent"]BUYOPEN
[/TD]
[TD="bgcolor: transparent, align: right"]52.1537
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]52.29881
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent"]BUY
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]52.42449
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent"]BUY
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]52.60843
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent"]BUY
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</TBODY>[/TABLE]
</PRE>
I'm new to VBA, and have been searching for threads with similar issue but can't seem to find exactly what i'm trying to do.
I have price data in column A, and a text column in column F. I am searching for 2 specific text "BUYOPEN" and "SELLCLOSE" in column F. Once those are found, i am attempting to find its corresponding price from the same row in column A.
The macro i have so far, is finding the text "BUYOPEN" and placing the corresponding price in the next column. but i would like to add/update the code to summarize this data in a 2nd worksheet.
So everytime there is a "BUYOPEN" or "SELLCLOSE", it will list each occurence along with the corresponding price in a new worksheet.
appreciate any help. thanks
Code:
Sub FindWord()
Dim FindString As String
Dim FindCell As Range
FindString = ("BUYOPEN")
If FindString = "" Then Exit Sub
For Each FindCell In Range(ActiveSheet.Range("F1"), ActiveSheet.Range("F300").End(xlUp)).Cells
If InStr(FindCell, FindString) > 0 Then FindCell.Offset(, 1) = FindCell.Offset(, -5)
Next FindCell
End Sub
sample data[TABLE="width: 336"]
<TBODY>[TR]
[TD="width: 64, bgcolor: transparent, align: right"]51.6323
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]0
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]0
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]0
[/TD]
[TD="width: 64, bgcolor: transparent"]SELL
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]0
[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]52.08857
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent"]SELL
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]52.1537
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent"]BUY
[/TD]
[TD="bgcolor: transparent"]BUYOPEN
[/TD]
[TD="bgcolor: transparent, align: right"]52.1537
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]52.29881
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent"]BUY
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]52.42449
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent"]BUY
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]52.60843
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent"]BUY
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</TBODY>[/TABLE]
</PRE>