Posts by vhookup

Hi, Sorry Roy.

Hi All,

It sounds simple and I'm sure it is, but I've gone back and forth last few days but cannot solve it. In a nutshell, I need to find Top 10 values from 2 columns, HOWEVER, I cannot have more than one max value in this list from the same row of the source data, it must be a strict one answer per row from the 10 highest value rows. I hope that makes sense?!

My current Formula below returns values fine but I get a repeat where I don't need it please see the image link below:

=IF(ROWS($J30:J$30)<=$H$28,LARGE($K$3:$L$20,ROWS(J30:$J$30)),"")

In this Example Table if the values in the "Count" column (K:K) are greater than a value in the "Max" column (L:L) then it needs use the Values in Count Column (K:K), but do not repeat the value adjacent in Column L:L further down the Top 10 (147 at 20:30:00 is not wanted in this example list but the two highlighted 138's are fine). The cells pictured to the right which are headed Time and Top10 are I29:J39 in the example sheet attached Countsheet.xlsx

Previously I have been running a formula for a while top get top 'x' values from a single column. This formula (in a different file) located in AD6 copied down as far as needed (10rows in this case) uses the number input in AD3 to determine a Top 'x' - The code below worked well for this situation along with a formula in the adjacent cells to extract the Day, Date and Time this max value occurred.

=IF(ROWS($AD$6:$AD6)<=$AD$3,LARGE($W$2:$W$112,ROWS($AD$6:$AD6)),"")

Thank you in advance for the help.

Quote from royUK

Try this, I think it does what you are aiming for. I've also tidied up the rest of the code.

Thank you for this i'll get more time tomorrow to really delve into this and set it up within the other workbooks, hopefully there shouldn't be any issues with that though!

Thanks again

Quote from royUK

For a start you declare a variable ws but don't give it a value. It should be

Note all variables should be declared at the top of the code.

Code

Dim ws As Worksheet
If Me.ComboBox1.ListIndex < 0 Then
MsgBox "you must select a name", vbCritical, "Input required"
Me.ComboBox1.SetFocus
Exit Sub
End If
Set ws = Worksheets(Me.ComboBox1.Value)

I think this is what you are trying to do, but the code will need expanding to cover each set of controls

Code

Private Sub CommandButton3_Click()
Dim ws As Worksheet

If Me.ComboBox1.ListIndex < 0 Then
MsgBox "you must select a name", vbCritical, "Input required"
Me.ComboBox1.SetFocus
Exit Sub
End If
Set ws = Worksheets(Me.ComboBox1.Value)
ws.Range("D16").CurrentRegion.Cells(Me.ComboBox2.ListIndex + 2, Me.ComboBox3.ListIndex + 2) = Me.TextBox1.Value


End Sub

Try it out, it only works with the first set of comboboxes and textbox. Let me know if this on the right track

Display More

Display More

Hi Roy, I've tried this with multiple lines and to multiple sheets, it works - very much on the right track. I've just seen the new message now though. Just trying to understand what does what at the moment.

Thank you Very much for your help!

Hi I have included a sample below.

Many thanks

Hi Stephen, not currently.

But even only testing it with the one set of boxes is not working. Now you've mentioned that, that has raised more questions ??‍♂️

Hi,

I'll try to be brief and to the point.

I have a user form and several target sheets.

User form has 3 different ComboBox and 1 type of textbox. Combobox 1 is Sheet (exclude this from this question, this will be used to select different sheets to send the data to - but I'll be able to sort this code out), Combobox2 is Day and Combobox3 is month, Textbox1 will be a number.

The sheets are essentially yearly calendars Columns (D:O) are months, Column C contains days of the month (1-31 in C17:C47).

I would like the number in textbox to be added to the intersection of the Month and Day i.e. Jan 1 selected via the comboboxes and 10 input in Textbox, this would then appear in the intended sheet in Cell D17.

My mind is boggled, researched for weeks, I've adapted code from a previous project which did a similar thing but was looking in 2 columns from labels on a userform rather than 1 column and 1 row using comboboxes and it was also using lastrow, which i don't think is the solution, but I've found nothing that works or any other suggestions online.

If i'm barking up the wrong tree could you please point me in the right direction? (it might be worth noting that there are 10 of each combobox and textbox on the user form -apart from the sheet combo- to allow for multiple entries upon loading the form) Below is what I have so far - various bits have been removed where the

Private Sub CommandButton3_Click()

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.DisplayStatusBar = False
    
    Dim LastRow As Long
    Dim ComboDay As String
    Dim ComboMonth As String
    Dim i As Integer

    Dim ws As Worksheet
      
           Sheets("Sheet1").Select
            LastRow = ws.Range("C" & Rows.Count).End(xlUp).Row       'LastRow for the loop
    
    
'Identifying date and time
ComboDay = UserForm1.ComboBox2.Text            'date retrieved from Combobox
ComboMonth = UserForm1.ComboBox3.Text           'Month selected in the ComboBox

For i = 1 To LastRow
    If (ws.Range("C" & i).Text = ComboDay) And (ws.Range("D" & i).Text = ComboMonth) Then
        ws.Range("ComboDay", "ComboMonth" & i).Value = TextBox1.Text 'and so on for 10 textboxes
        ws.Range("ComboDay", "ComboMonth" & i).Value = TextBox2.Text
        ws.Range("ComboDay", "ComboMonth" & i).Value = TextBox3.Text
    End If
Next i
           
Application.DisplayStatusBar = True
Application.DisplayAlerts = True
Application.ScreenUpdating = True


End Sub