Posts by 5foot9

    This is for a power query. In my main table I have a column of numeric values called Field. In a small lookup table (5 rows) I have 3 columns, the first column is the group name, the second is the minimum value, the third the maximum value. I would like to return a new column called GroupName in my main table where the Field value is between the min and max value in the lookup table and returns the corresponding group name.

    Ok, it works if I hard code which items to display so I've been trying to come up with a way forward that involves using a cell reference as the array for the filtered items on the slicer. I'm struggling to get the code to run using a string variable as the array. I found something here that may be a way forward -…array-as-an-array.935150/

    I don't understand what's happening here so would appreciate any pointers from someone. I've attached an updated file which I hope helps.


    Am I right in thinking that because of the use of a data model all selected items must be visible in the pivot table before code runs to avoid an error? Would this just mean clearing all filters beforehand?

    In the meantime I'll keep looking and trying to understand.

    I've found some code that looks more like what I want but there seems to be at least one error so far.

    'At least one item must remain visible in the Slicer at all times, so make the first

    'item visible, and at the end of the routine, check if it actually *should* be visible

    .SlicerItems(1).Selected = True

    Returns the the run time error 1004, application-defined or object error

    I had to define pt and I guess dim as PivotTable is correct.

    The line vSelection = Array("Class2", "Class3", "Class4") is something I would like to pick up from cells on the sheet and was wondering if that would be possible

    I've looked around for hours to try and find something that I could try to adapt to my needs but not really found anything close yet, I'll keep trying though. Most of what Ive seen so far is the opposite, getting slicer values into a cell

    I have 6 groups of 6 slicers where I want each slicer to have a value which is automatically selected from it's own cell (comma separated values) or a range of cells when a group is called/selected. The slicer values can then be changed if desired or the accompanying graphs/charts simply viewed. Each time a group is called/selected it will use their own cell or range values to select the slicer values so no changes are saved.

    The idea behind this is that the cell values hold the optimum settings for the slicers which potentially change over time therefore the ability to change the slicers and experiment with different settings is what is key (I'll be able to add in code to automate cell values later) All data is linked to the power pivot data model The workbook I'm using is large but the slicers only have between 2 and 20 possible values in each. When I reset them at the moment it takes about 20 seconds, it is what it is. I'm hoping I don't need to create a new workbook to attach here as that could prove troublesome and time consuming but if that's what needs to be done then so be it. Ideally I would like someone to point me to somewhere out there on the wild web where I could adapt some code for myself, I'm very slow and clunky at it but I kind of get somewhere near eventually. Thanks in advance.

    Thanks Rory it works great!
    A couple of things, would it work the same if both were comboboxes? Refering to your note - BkmrExch is a textbox but is dependant on another combobox and will change if the user changes combobox entry which is what I want so would it be best to leave it as a change event? I've not used an exit event before so I'm unclear when it's best to use it. Thanks again, really appreciate your help.

    I have a userform textbox that gives only 2 values, bookmaker or exchange. I want to use whatever value is shown in the text box to look through a small table of 2 columns and 5 rows to find all matching conditions in column 1 and add the values from column 2 to a combobox. I think I'm relatively close but I think I'm going wrong when it comes to the with statements and I'm also having getting errors with my variable definitions. I seem to be chasing my tail at the moment and I was probably closer with previous incarnations, so I guess I have a serious case of brain fry now!
    The combobox is called BetType and will populate with either 2 or 3 values depending on what is shown in the textbox called BkmrExch.
    Here is the code I have now

    rabsofty's help has solved this problem for me but now I need some advice on how to solve loading a combobox from a multiple sheets and some information as to why there are multiple 'criteria' and 'extract' named ranges in the name manager. I guess I need to start a new thread or would I continue here as it's the same project?

    Thank you so much for your help. I knew it was just a tweak here and there but couldn't get my head around it at all!!
    As for the duplicates, when you look on the dropdown for the names they should match the contents of column AF which they do but somehow duplicate them in the dropdown.
    Somehow that seems cured now with the reshuffle of code which is great. If you want to know what I meant take a look again at the original file. I'll have a look at your code and see where I went wrong and try to see if that's affected the duplication. Thanks again

    I hope I can get some help or guidance from someone here. I've uploaded a simplified sheet which I hope is easy to understand. I import the data and trim and format it using other code, the layout is not important to me as it is because the sheet will be hidden. I have noticed I'll need to unhide when using the userform though.

    I am trying to create a userform using comboboxes to allow me to enter horse racing data to another sheet. I want to select a Track which in turn will populate the race times combobox for the selected track which in turn will populate the Names (horse names) combobox. I've managed to create the userform and it works to a point but somehow the Names combox gets populated twice resulting in a dropdown of duplicates. One other issue is the time format is wrong in the dropdown but shows the correct format in the combox once selected. Also is it possible for the Track combobox to be empty when userform is loaded until I make a selection and is there a way to disable any manual entries?

    Any other advice or different methods on using multiple dependent comboboxes would be welcome


    Managed to find a solution,

    Set InternalWB = Workbooks.Open(xpath & LatestFile, Local:=True) 'Opens file above.

    The Local = true sets the global Time format to the local time format which Excel would otherwise ignore in certain cases....brilliant!!! Taken me a day and a half to find this out. I think I'm happy, but I may be a little sad that it's taken this long and it was such a simple solution....Life

    I'm getting a problem with excel switching UK date fomat to US date format. The code I have is opening the most recent downloaded file in a folder
    and automatically switching the UK date format to a US date format.

    I'm using an excel 2016 xlsm to open an excel 2007-2013 xls file

    The file in question has a UK date format date already and there is no problem if I open this file manually
    so logic tells me there is something that the code is or isn't doing to prevent
    the format switch from occurring or maybe its something to do with the two different types of excel.

    I've had (along with many others I suspect) the annoying never ending UK/US date format problem
    with excel before and managed to work round it but this code is something I have copied from
    somewhere else and I'm unfamiliar with most of it.

    I've tried using the text to columns trick but it seems to behave differently in code than when used manually which is not something I want to do
    as automation of all the processes is what is needed.

    The code switches the date formats as soon as the file is opened so I'm struggling to see where I can put any code in place to stop this happenning
    If its just a code thing then with my very limited coding ability I am guessing the Dir$ or the wildcard are the culprits.

    This code works fine until I encounter a UK day of 12 or under then it switches them around to US format

    The code switches the date formats as soon as the file is opened so I'm struggling to see how I can put any code in place to stop this happening

    Re: Match two values with two values on another sheet and return value from third col

    I think I've found the problem even though I don't understand why the problem occurs. The ranges for each sheet must start in the same column on each sheet to avoid the match error. The difference in range sizes makes no difference just as long as they start in the same column. Does anyone know why this is??

    This code now works fine

    Many thanks to skywriter and jolivanes for your help, hope you have a great weekend!

    Re: Match two values with two values on another sheet and return value from third col

    Probably won't work on 2003, this is 2016 version using powerpivot. Slots are what I call the numerical reference for a system, they have a nickname too. Betsender is irrelevant for this code, I'm just describing the purpose of my requisition really. There's a slicer called slots that will show the selected slots qualifiers for that day when clicked. No C&P needed

    from previous post

    Basically skywriters code does exactly what I want. On the 'Today' sheet select a slot from the slicer then decide which ones will be a bet or not, a 1 or 0 in the yellow bet column. Click the red 'Bet' button to run the code. Select the next slot and repeat til all slots are done. This will populate the 'Bet' column on the 'Qualifiers' sheet. This is done by matching the slot number in E2 and the text in the qualifier column in 'Today' with the slot number and qualifier text in the columns in 'Qualifiers' sheet.
    I can then click the 'Betsender' button on 'Control' which sends today's bets (all the rows that contain a 1 in the bet column) to a csv file that can be uploaded into a bot.