Hi Fin,
Yes, it's definitely possible!
As Sam suggested, if you could post a sample workbook, it would help get you
an exact solution.
I know nothing about REXX script. And your other logic about the stocks,
and where the information is coming from isn't clear to me.
Here, though, is one way to parse a string/cell value out into elements
seperated with "+", extract the Stock symbol before the "_" and then extract
POS/NEG to True/False:
Dim StockPosNeg As Variant, Stock As String, PosNeg As Boolean
Dim pos As Integer
For Each StockPosNeg In Split(CellVal,"+")
pos=Instr(CellVal,"_")
Stock=Left(CellVal,pos-1)
PosNeg=Right(CellVal,Len(CellVal)-pos)="POS"
' Do something with above values here.
' Store in spreadsheet or array variable if the context of all stocks in
cell
' are required to process.
Next StockPosNeg
' If stored, process the cell as a whole with it's parsed contents here.
No error checking / assumes cell value is predictably as you described and
in all upper case.
Asa
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of Fin
Sent: Monday, October 31, 2011 1:43 PM
To: excel-macros@googlegroups.com
Subject: $$Excel-Macros$$ Test Cell String for Element match and report
Accuracy
-- here we write out the macro to highlight active stocks in
yellow
v.line_out = 'Sub Color_Active_Yellow()'
v.line_out = 'Dim CellVal As Variant'
v.line_out = ' Selection.CurrentRegion.Select'
v.line_out = ' iRows = Selection.Rows.Count'
v.line_out = ' iColumns = Selection.Columns.Count'
v.line_out = ' For iC = 1 To iColumns'
v.line_out = ' For iR = 1 To iRows'
v.line_out = ' Select Case Selection.Item(iR, iC).Value'
do i = 1 to ctl.0
v.line_out = ' Case "'||ctl.1.i||'"'
fo~lineout(v.line_out)
v.line_out = ' Selection.Item(iR,
iC).Interior.ColorIndex = 6'
fo~lineout(v.line_out)
end
v.line_out = ' End Select'
v.line_out = ' Next iR'
v.line_out = ' Next iC'
v.line_out = ' Range("A2").Select'
v.line_out = 'End Sub'
I have a Daily Stock worksheet that in one specific column, I have various
strings containing multiple
combinations of stocks in various conditions separated by '+' ie:
ABC_POS+DEF_NEG+GHI_POS.
I need to be able to test the results of these strings based upon how the
individual stock closed on a specific
day. I have the above partial REXX script that does a similar process but
based on individual stocks in a cell
(not in combined strings) and when it finds said condition it changes the
background color to indicate
result.
I need to be able write a new REXX script that produces a VBA script to
break each these combined cell strings in the column into individual
elements,
test for true/false and write results to another 2 columns counting the
number of correct stocks in one,
then writing those that were wrong to another. Perhaps also turning the cell
background color green if all elements are correct.
For example,if column J2 contains the string 'ABC_POS+DEF_NEG+GHI_POS' and
the stock DEF actually closed
DEF_POS in my REXX script, then I need to write 2 columns that says
1,DEF_POS.
If 2 are wrong, then I need the columns to say 2, DEF_POS+GHI_NEG. I Already
have a column stating
the number of elements contained in the string, and I can use that to
calculate accuracy.
In the REXX script above, the actual result of that script gives me a macro
with as many lines in the
Case "'||ctl.1.i||'"' as are present in the ARRAY I populate. Each line in
the resulting VBA script might therefore say:
Case "ABC_POS"
Case "DEF_POS"
Case "GHI_NEG"
Case "ABC_POS"
... and so on depending on the day in question.
The worksheet may contain some 20 columns and say 5000 rows (different stock
combination strings).
Can anyone please give me some idea if this is possible with a VBA script
and how to do so ?
Many thanks in advance.
--
FORUM RULES (925+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security
measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
----------------------------------------------------------------------------
--------------------------
To post to this group, send email to excel-macros@googlegroups.com
--
FORUM RULES (925+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
------------------------------------------------------------------------------------------------------
To post to this group, send email to excel-macros@googlegroups.com
