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 - https://www.mrexcel.com/board/…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.

Slicer2.xlsm

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 hope the file helps and that I've explained myself clearly enough for you to understand what I'm trying to do! If you are able to solve the puzzle or part of it would you be kind enough to add some notes for me to understand how it works?

I'll make a mock up this evening when I get back as the master file is too large. Thanks for your continued help.

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

Option Explicit

Sub FilterSlicer()
Dim slr As Slicer
Dim sc As SlicerCache
Dim si As SlicerItem
Dim i As Long
Dim vItem As Variant
Dim vSelection As Variant
Dim pt As PivotTable

Set sc = ActiveWorkbook.SlicerCaches("Slicer_Class1")

vSelection = Array("Class2", "Class3", "Class4")

For Each pt In sc.PivotTables
    pt.ManualUpdate = True 'Stops PivotTable from refreshing after each PivotItem is changed
Next pt

With sc

    '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

    'Hide any other items that aren't already hidden.
    'Note that it is far quicker to check the status than to change it.
    ' So only hide each item if it isn't already hidden
    For i = 2 To .SlicerItems.Count
        If .SlicerItems(i).Selected Then .SlicerItems(i).Selected = False
    Next i

    'Make the PivotItems of interest visible
    On Error Resume Next 'In case one of the items isn't found
    For Each vItem In vSelection
        .SlicerItems(vItem).Selected = True
    Next vItem
    On Error GoTo 0

    'Hide the first PivotItem, unless it is one of the countries of interest
    On Error Resume Next
    If InStr(UCase(Join(vSelection, "|")), UCase(.SlicerItems(1).Name)) = 0 Then .SlicerItems(1).Selected = False
    If Err.Number <> 0 Then
        .ClearAllFilters
        MsgBox Title:="No Items Found", Prompt:="None of the desired items was found in the Slicer, so I have cleared the filter"
    End If
    On Error GoTo 0
End With


For Each pt In sc.PivotTables
    pt.ManualUpdate = False
Next pt

End Sub

I'll take a look Carim, thanks for your help.

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

Private Sub BkmrExch_Change()
Dim ws As Worksheet
Dim tblBetType As ListObject
Dim i As Variant
'Dim col As ListObject
Application.ScreenUpdating = False


Set ws = Sheets("TodaysBets")
Set tblBetType = ws.ListObjects("BkmrExchBetType")
'Set col = ws.ListObjects("BkmrExchBetType[BkmrExch]")


If BkmrExch.Value = "Exchange" Then


Application.EnableEvents = False
BetType.Clear
Application.EnableEvents = True




With BetType
For i = 1 To tblBetType.ListRows.Count


'For Each i In col


If cell.Value = BkmrExch.Value Then
BetType.AddItem cell.Offset(0, 1).Value
End If
Next i
End With




ElseIf BkmrExch.Value = "Bookmaker" Then


Application.EnableEvents = False
BetType.Clear
Application.EnableEvents = True




With BetType
For i = 1 To tblBetType.ListRows.Count


'For Each i In col


If cell.Value = BkmrExch.Value Then
BetType.AddItem cell.Offset(0, 1).Value
End If
Next i
End With


End If


Application.ScreenUpdating = True
End Sub

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

Hi,
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

[ATTACH]n1207520[/ATTACH]

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

Just to add Uk dates are DD/MM/YYYY in the xls file and switch to MM/DD/YYYY

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

Sub ProcessResults() 'Opens most recent download of results.xls file & copies relevant data to results sheet


Dim xpath       As String
Dim xfile       As String
Dim InternalWB  As Workbook
Dim LatestFile  As String
Dim LatestDate  As Date
Dim LMD         As Date
Dim wb          As Workbook




Application.ScreenUpdating = False


Set wb = Nothing
xpath = Sheets("Tables").Range("L14").Value 'path
If Right(xpath, 1) <> "\" Then xpath = xpath & "\"
xfile = Dir$(xpath & "results*" & ".xls", vbNormal) '* Wildcard is for date/hour/min parameters.


If Len(xfile) = 0 Then
    MsgBox "Can't find what your looking for!", vbExclamation
     Sheets("Today").Select


    Exit Sub
End If


Do While Len(xfile) > 0
    LMD = FileDateTime(xpath & xfile) 'process for finding all files above using FileDateTime function. loops thru until most recent date AND name match "like" above
    If LMD >= LatestDate Then
        LatestFile = xfile
        LatestDate = LMD
    End If
    xfile = Dir
Loop


On Error Resume Next
Set wb = Workbooks(LatestFile)
On Error GoTo 0


If wb Is Nothing Then
    Set InternalWB = Workbooks.Open(xpath & LatestFile) 'Opens file above.
    Else
    Set InternalWB = Workbooks(LatestFile)
    MsgBox LatestFile & " is already open."
End If 
Application.ScreenUpdating = True


End Sub

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

Thanks, I wish I knew why the start columns for each range must be in the same place, maybe it's another vb quirk.

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

Option Explicit
Sub PlaceBets()
Dim wsOne As Worksheet, wsTwo As Worksheet, numSlot As Long
Dim rngOne As Range, rngtwo As Range, r As Range, numBet As String
Set wsOne = Worksheets("Qualifiers"): Set wsTwo = Worksheets("Today")
Application.ScreenUpdating = False




With wsOne.Range("A1").CurrentRegion
    Set rngOne = .Offset(1).Resize(.Rows.Count - 1)
End With
With wsTwo.Range("A1:S7")
        numSlot = Range("E2")
    Set rngtwo = Range("A1:S7")
End With
For Each r In rngOne.Columns("H").Cells
    If r.Value = numSlot Then
    
    numBet = Application.WorksheetFunction.Index(rngtwo.Columns("S"), _
        Application.WorksheetFunction.Match(r.Offset(, -4), rngtwo.Columns("I"), 0))
        If numBet <> "" Then
   
            r.Offset(, 2).Value = CLng(numBet)
        Else
            r.Offset(, 2).Value = ""
        End If
    End If
Next r
Application.ScreenUpdating = False
End Sub

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.

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

forum.ozgrid.com/index.php?attachment/73458/ Woo Hooo managed to do it this time albeit reduced with charts and pivots deleted, hope this helps. My brain is frazzled, I need to get my sleep back in sync!