Posts by smuzoen

    Re: Does text from a list show up in the worksheet?


    You could try something like this using VBA. This will search the entire worksheet for the substring you place into the following code.


    Hope this helps

    Re: Sum cells based on criteria and variable column reference


    Am I right in assuming there are multiple COLUMNS and you have only shown 2 of them. You have an entry in the summation table of - PJNY BRL Cash.01Apr2013 0
    There are no columns with PJNY as the TradeID or BRL as the currency - if there are multiple columns then it is more likely you will need a macro to construct a summation table.
    Is this what you want - from the raw data you want a Summation Table that lists all the different Trade ID's with the corresponding Ccy, listing only unique PV sensitivity and a total for these combined criteria
    Table would be: Trade ID , Ccy , Unique Listing of PV sensitivity, Sum of PV Sensitivity.
    You only want the UNIQUE values of PV Sensitivity Listed and the SUM of the UNIQUE values of PV Sensitivity for each TradeID and Ccy.
    If that is what you want then I think a VBA solution may be the way to go.
    I need to get this straight in my head to get you the result you are after

    Re: Sumif with unknown column to find (match function)


    Yeh sorry about that. If I understand you correctly if G1 contains the criteria to select the column to sum for each of the values in Column A (e.g. Cash.01Apr2011) then the following formula should do what you want. Place this formula into D4 and copy down. If it is not what you want then in the workbook place the expected results into column D.
    =IF($G$1=$B$1,SUMIF($A$4:$A$7,A4,$B$4:$B$7),SUMIF($A$4:$A$7,A4,$C$4:$C$7))
    Hope this is what you want.

    Re: Sumif with unknown column to find (match function)


    Using match returns the relative position of a value in an array and the last condition in sumif is an array plus you used 1 as the match type which is "less than" so the formula really makes no sense. Upload a workbook and it would be easier to answer your question. I suspect you will need to use a hlookup but hard to say without a sample set of data.

    Re: VBA coding run time error 1004 application defined or object defined error


    What are you trying to do? The code as it stands is checking for the value in x and y and then putting data into cells based on this. Case is checking for certain values and if the criteria is met then a value is placed into various cells. X & Y wont change based on your code - it is using a variable i and this variable changes with the for next loop but currently x & y dont change as they are outside the loop. If you upload a sample workbook I will have a look.

    Re: Identify changes between two worksheets


    Hi there
    I have added some code for you so now you can use a form to select the various sheets. All you need to do is add the new Data sheet manually. If you then run the macro called "collectWorksheets" then a form will pop up. With this form you can delete the old data sheet you normally delete manually, then select the two sheets you want to run the report on - select the old data sheet to be used in the report, the new data sheet to be used in the report and then you can optionally give a name to the sheet where you want the report to go to. This Report Sheet will be created - if there are any sheet name conflicts then the VBA will deal with that. It is optional in the form to give the Report Sheet a name (one will be created if you don't name one). The sheet you normally delete manually can be deleted through the form and again this is optional. I have added the error handling you should require and I have tested it and I think you should be happy with it.
    WARNING: Try this on NON SENSITIVE DATA FIRST - make sure you are happy with the way it works before letting it loose on real data. To be sure back up your data before running the code.
    If there are problems let me know and I will recode if required.
    Enjoy
    PS: If you can give me a good idea of the worksheet you import into the workbook and how you select the sheet and its naming convention I could add the option to import the worksheet via a form and that way the whole process is automated.

    Re: 2003 Excel Question. Totaling Up inventory, Sure its that simple LOL


    Hi Jay
    This may get you started - you will just need to make up some rules in your VBA based on the possible outcomes. The code below is if you receive stock then the Stock Level goes up and the On order goes down. Have a look at the following and if you need more help just ask.


    Hope this helps

    Re: Separating one set of text & numbers from a string?


    Is it just the first term and the first numerical coefficient or do you need to match each term with each numerical data. That is does the entire string need to be processed or just the first term and corresponding value. Can you give an abbreviated example how it should appear in excel. Just upload a sample workbook. You will have a much better chance of an answer if we can see exactly what you want

    Re: Identify changes between two worksheets


    I could write you some code that will detect the new report and delete the oldest sheet that you can incorporate into the current code. That wont be hard. I can add some error handling as well. With the reports sheet - will the report sheet be generated as a new sheet or will you keep adding to the existing report sheet (i.e. when you run the code do you want the report sheet deleted and replaced with a fresh report sheet or is it to be a running collection of the previous reports)
    I will have something for you today. Just got to work so be later today

    Re: VBA Listbox hide column error


    Usually it is from referencing problems. As GlennUK said put up the code that calls this sub otherwise you could try

    Code
    If formname.ListBox2.Value = "Current Period" Then etc etc


    where formname is the name of the form that the listbox belongs to
    Other than that you need to upload your code

    Re: Identify changes between two worksheets


    Hi there
    I have written some VBA that should do what you want. It will generate the report in a sheet called Report Current. I have worked on a few assumptions. I assumed that only non matching order numbers are present in the newest sheet. If there are new order numbers in the oldest sheet then the VBA will need to be changed. Have a look at the workbook anyway and see if it does what you want it to do. You just have to run the macro called "compareReports". It will add a sheet called ReportCurrent and the data will be generated on this sheet.
    Get back to me if it needs any changes.
    PS: I have minimal error checking and this really should be added into the VBA.
    Hope this helps

    Re: Multidimensional array search for value meeting the condition


    Hi there
    To answer your first question - Yes- you can initialise an array by referencing a range

    Code
    Dim varSales As Variant
    varSales = Range("A1:F10")


    To answer the second part could you upload a sample workbook with some sample (non-sensitive) data and be explicit about what you want to achieve - what data to compare, where result is to go - make sure it is very clear what you want.

    Re: CSV Import to specific worksheet


    If you want to paste the data into the sheet at Column D Row 4 then just change the code at the end a little

    Code
    ' this will focus the last data copied (cell focused) within the defined range
     sh1.Select
     Range("D4").Select
     Selection.PasteSpecial xlValue
    Application.DisplayAlerts = False


    Just change A1 to D4.
    Hope this helps

    Re: Closing userform cause code error 400


    Before you start setting the values for the cells just specify the sheet. So once the form is unloaded and your control is back to the module just add something like

    Code
    Worksheets("Sheet1").Select
    'or if you have named the sheet
    Worksheets("Data").Select


    Hope that helps

    Re: CSV Import to specific worksheet


    If you just want to be able to select the sheet you want the data imported into you only need to modify the macro a little. This will import the data into Sheet2 but you just have to put in the sheet name you want the csv files to be imported into.
    As well unless there is a specific reason there is no need to add in an additional worksheet unless you have some specific need to do this. You could delete

    Code
    With ThisWorkbook
     .Worksheets.Add After:=.Worksheets(.Worksheets.Count)
    End With


    and it would still import all the csv files in c:\temp into sheet2.


    Hope this is what you were after

    Re: Data grabbing from a CSV file


    Hi GazF
    This should do what you want. It will allow you to select the file to import and then allow you to import subsequent files at the next available row. Just place this into a module and run ImportTextFile. It has hardcoded the separator as a comma however you can change this. If it needs to be changed let me know and I can tweak it for you.
    Note: I have not done extensive testing however with the supplied files I think it will do what you want.



    If you have a group of files that you want imported then you could incorporate a function to find all the data files in a directory and import them - this way you could group the files by month or the macro could be modified to select all the files from a particular month.
    Hope this helps

    Re: Compare number lists on different sheets and delete duplicates


    Hi there
    This should do what you want. Place this into a module in Oak Harbor and rename the workbooks as required. It will sort the sheet to delete from to make the process easier. That way you can delete a range rather than a row at a time.


    Let me know if you have any problems