$$Excel-Macros$$ Re: Excel Add-In or XLSTART file not working
Anyone ? This is a really annoying issue that I am just unable to fix. -- FORUM RULES (934+ 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
Re: $$Excel-Macros$$ Re: Excel Add-In or XLSTART file not working
Thanks Noorain, appreciate the response. The problem however isn't the opening of files so much as it is the execution of the macros contained therein. I tried the articles recommendations and it didn't help. The code I pasted works fine when manually pasted into excel vba editor, however if I create an add-in or use an xlstart file the code fails with a generic 400 error. If I debug the macro in the add-in or the xlstart file it seems to failing on the range select statement RANGE(A2).select and I have no idea why. Why it works when manually pasted and not when included in the add-in/xlstart file ? That is my problem. When using the xlstart file, it opens automatically every time I open a csv file no problem at all, it's just I get the 400 error every time I execute one of the macros contained therein. Any other suggestions ? Regards, Fin. -- FORUM RULES (934+ 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
$$Excel-Macros$$ Excel Add-In or XLSTART file not working
I am very new to Excel VBA Code so please forgive my coding clumsiness. I have the following Code that I use to format various reports. I want to be able to install it automatically for CSV files I open, format then save as xls files. I have tried creating both an xla add-in file and when that didn't work, an XLSTART auto file, both with the below code inserted and saved. The problem I have is that the add-in file does not show the marcos, or if using the XLSTART file they both fail on the Range(A2).Select statement but if I disable both and manually copy and paste the code into the 'vIEW CODE sheet option of an opened CSV file it works fine. I can view the code when using the add-in but it still fails to execute on any range select statement. The add-in is available to select via the add-ins menu, but after doing so the macros still fail to be available via ALT-F8 and fail when run through the VBA Editor. Paste it in manallu however and it runs fine. Any ideas what I am doing wrong ? Many thanks for any assistance. === Sub AutoFit() Dim Del_Char As Integer Range(A2).Select ActiveWindow.FreezePanes = True Rows(1:1).Select Cells.EntireColumn.AutoFit With Selection.Interior .ColorIndex = 16 .Pattern = xlSolid End With Selection.Font.ColorIndex = 2 Selection.HorizontalAlignment = xlLeft Range(A2).Select Del_Char = Len(Selection.End(xlDown).Select) If Del_Char = 4 Then Selection.ClearContents End If Range(A2).Select End Sub Sub Color_OffSet() Dim str_Date As Date Selection.CurrentRegion.Select iRows = Selection.Rows.Count iColumns = Selection.Columns.Count For iC = 2 To 2 For iR = 2 To iRows Select Case Selection.Item(iR, iC).Value Case YELLOW Selection.EntireRow(iR).Interior.ColorIndex = 6 Case ORANGE Selection.EntireRow(iR).Interior.ColorIndex = 45 Case BLUE Selection.EntireRow(iR).Interior.ColorIndex = 33 End Select Next iR Next iC Range(A2).Select End Sub Sub AlignShrinkToFit() Selection.CurrentRegion.Select Selection.HorizontalAlignment = xlLeft Columns(A:AP).Select Selection.HorizontalAlignment = xlRight Columns(L).Select Selection.HorizontalAlignment = xlLeft Selection.CurrentRegion.Select iRows = Selection.Rows.Count iCols = Selection.Columns.Count Columns(AR:BE).Select Selection.EntireRow.ShrinkToFit = True Range(A2).Select End Sub === -- FORUM RULES (934+ 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
$$Excel-Macros$$ Re: Excel Add-In or XLSTART file not working
Oops, Excel 2002 on Office XP Pro OS = XP Pro SP3. -- FORUM RULES (934+ 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
$$Excel-Macros$$ Re: Test Cell String for Element match and report Accuracy
So, no takers huh ? .. Anyone .. ? -- 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
$$Excel-Macros$$ Re: Test Cell String for Element match and report Accuracy
Haseeb, thank you so much for your efforts, that is amazing I must say. I'm frankly staggered how you achieved that with formulas. Now I must admit I'm lost as to how you did so, but I kind of need to know if I am to repeat the task with the next days report. The end of day data that the original rexx script contained was taken from my stock database at the close of business. If I am to test the day3 strings with say today's data, I need to refresh it and then test that against the new active strings. The report will produce new strings to be tested each day against new close of day data. So, how do I add the latest close of day data to whatever you did, and then apply that to todays report ? I'm not sure I am explaining this correctly. Basically there will be a fresh CSV file with a varying number of rows per day (report with day3 strings) and obviously fresh close of day stock data (with a fixed number of stock symbols) to test the strings. I need a way to be able to test one against the other. I'm a bit lost here I am afraid. And as an aside, to previous post by Don, I'm sorry but this was my first attempt at Excel / VBA coding so please forgive my crude and inefficient code. I frankly had no idea what I was doing when I started and was simply pleased enough to get to work at all. I'm not sure how you define a 'project' but if that is the case, then I guess I am sorry for that too. Thanks again Haseeb, you have no idea how much I appreciate your help. -- 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
$$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
$$Excel-Macros$$ Rexx xlobj cell value check to color background.
Hey folks,
I am new to Excel programming and using oorexx to read a column of
cells in a worksheet checking for certain values and setting the
background color when the value matches. The code I have written works
fine, but being that it calls from rexx to an xlobj for each cell it
is not perhaps the most efficient way of doing it.
I was wondering if some kind soul could please advise if I am missing
a much more efficient method.
First I read from my database a series of text values into an array
with which to check each cell against. If the value matches one of the
46 values in the array then set the color, else next row, column.
-- ctl.1.i is the array values containing 46 string values
xlobj~ActiveSheet~UsedRange~select
iColumns = xlobj~Selection~Columns~Count
iRows = xlobj~Selection~Rows~Count
Do iC = 1 To iColumns
-- here I check the column name so that I do not have to do this for
EVERY column in the sheet.
-- check meets approx 10 cols of 40
If Lastpos('_COND',xlobj~Selection~Item(1, ic)~Value,) 0 Then
Do
Do iR = 1 To iRows -- could be well over 100k rows
body = xlobj~Selection~Item(iR, iC)~Value
do i = 1 to ctl.0
If body = ctl.1.i then xlobj~Selection~Item(iR,
iC)~Interior~ColorIndex = 6
End -- Do ctl.0
End -- Do iRows
The rest of the code is of course a lot bigger but the above is the
area I was hoping to optimize.
Anyone with any bright ideas ?
Cheers, Fin.
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
