Posts by Neiluk66

    Re: Reference Part Workbook Name In Formula From Date In Cell

    Dave, i got it to work as you said, what are the options if its not practicle to have the workbook open, is there an alternative method?

    thanks in advance ,Neil
    this is my formula now.


    i want to return a value from a cell in another workbook where part of the the workbook name is determined by a date set on the current open sheet.

    so on my open sheet i have a cell where the user can change the date and i want the below formula to then use the date to look at the relevant file.

    any ideas would be appreciated. know how to do this in VBA but no clue with formulas.

    what i am trying to do is concatenate part of a file name and a cell value which contans a date




    I need to identify duplicates in a list and have the foilowing formula:


    This works ok but i have a further condition which i dont know how to factor into the formula. I think i could write some vba to determine the dupes but i was hoping to avoid this as im sure it will take me an hour or so.

    Duplicates are identified at the moment as being identical numbers in column "amount", i now need to specify duplicates as being identical numbers in this range where there is at least one row with no pay date filled in in col "paydate"

    i hope the attached will make things clear.

    thanks in advance for your thoughts.



    is there an equivalent of

    range("2").PasteSpecial xlPasteValues

    when using

    .Copy Destination:=

    i dont really want to select the paste range but need just the value to be copied




    Im a bit confused as to how to proceed with my userform and module variables.

    I have a Userform and the code does sum calculations on a worksheet then opens another WB and pastes the results.

    I have variables for the WB and WS names involved as well as the Userform control values.

    I understand that i should declare public variables at the beginning of a mudule if i want them to be available to all sub procedures but what about the userform variables? if i declare these as public in the form will they be available to the module subs? I seem to remember Public variables have to be at the beginning of mudules.

    Any thoughts appreciated.


    Re: Unique List

    Hi all,

    Thanks for your time so far.

    Norie, i tried your code and it didnt work because you had missed the criteria off the first advanced filter. It wasnt really what i was looking for and i think i need to explain it better.

    What i want to achieve is the same result as having a pivot table but in list format. So a pivot would show a total sum of Kostenstalle "DDUF4"
    , where the portlio is "W_001_36432" and so on. I need the data in a list format though.

    I think that one can produce a pivot then reverse the process so you get a list but i seem to remember this being a bit complicated and im not that keen on coding pivots if i can avoid it.

    Am i making more sense?

    My sheet 2 shows exactly the result i want, i have included amounts for clarity.

    thanks again to all who responded.


    I have two colums of data Col A and Col B.

    I want to run an advanced filter on Col A to extract unique values.

    I then want to Autofilter Col A by each value in the previously extracted unique list in turn and extract a unique list from Col B.

    So far i have extrcated the unique list and applied autofilter.

    My questions are:

    How do i build a For Each Next loop which goes through each of the extracted unique values and filters on Col A extracting unique values in Col B?

    Is there an altogether simpler way of doing this or am i on the right track?

    thanks in advance, i attach my slimmed down WB for clarity.


    code so far is

    SUMIF argument


    I want to determine the arguments for a SUMIF from my data and parse them (right word?) to the SUMIF function then output the sumif to a messagebox.

    Previously i would have run an advanced filter and extracted the unique entries from the list to get the values for the SUMIF but not sure if i can do this in VBA without putting values on the ws.

    here is my code where

    StrCriteria = "FL_6MGBP"

    was used as a test to see if the SUMIF worked. i actually want to determine the SUMIF for each unique entry in column A "Kontahent" attached is a wb with an example, hope this is clear.

    and would be grateful for ideas.



    Re: Sumproduct


    your right, SUMIF works fine.

    I ripped some code from a previous macro where i had mulitple criteria and wanted the formula on the sheet, didnt stop to think how the scenario was different.

    Thanks for that.



    I want to use sumproduct in my code and display the result in a msgbox but i dont want to enter the formula on the ws.

    i have made an example on the attached ws of the result im looking for in the form of a ws based formula..

    error message with code is
    "Unable to get the sumproduct property of the worksheet class"

    hope someone can give me some pointers as to where im going wrong.

    PS been absent from this sight and VBA for some time and surprised i have not been demoted from"senior member!"


    Re: Worksheet_SelectionChange


    I had just decided to actually put some effort into it myself by opening up John Walkenbach's "power programming" and found a chapter on adding code programatically, when you come up with this brilliantly simple solution that means i dont have to read it!

    Another time perhaps
    Thanks for your time.


    Re: Worksheet_SelectionChange

    Hi Thomach,

    I definateley dont want Worksheet_Change event as the user will just be selecting cells and not changing values (i hope!).

    im just working with one sheet at a time. I mentioned specifying a worksheet to monitor the changes because i was thinking you might be able to put code equivalent to "sheet1"Worksheet_SelectionChange ... in a module or userform code to avoid adding the sub to each worksheet that is created.

    how difficult is it to add a Worksheet_SelectionChange sub to a new sheet programatically?

    thanks for the input



    I want to use the Worksheet_SelectionChange event to run a macro. - a simple sum of values on the sheet that will be displayed in a userform.

    Problem is the worksheet that the calculation will be run on, and that the user will be selecting cells from, will be created by the user.

    I beleive its possible to create a macro programatically and add it to the worksheet, although i have never done this, but is there an alternative?

    Can you specify a worksheet that you want to monitor the selection change and thereby trigger the macro?

    this is my code which is currently run by a button on a form

    cheers for thoughts

    Re: Subtract month VBA

    Thats great Norie,

    I adapted to give the month in long format.

    been out of the game for too long, forgetting the basics!

    MyMonth = Format(DateAdd("m", -1, Date), "mmmm")




    I want to return the previous month in long text format and then use it as part of a file name.

    when i run the below the subtraction takes one day away from the current date and the msgbox displays "January"

    I want to return "March"

    Can someone help please?


    Re: Recorded code default to existing module


    I think your right.

    I usually delete the modules as soon as i am finished with the recorded code which ironically is why i keep having to do this. If only i had left the first one alone once it had been created it!