What's wrong with it?

[marlonsaveri]

I have a form here that, when it's opened, we selected a property in a combobox and we click "search. "
When such property is found, it is highlighted in another color in spreadsheet and the form shows the maximum, minimum and average.

However...

1. I can't add items to combobox (there are some blanks).

2. Moreover, in the original workbook, it can not understand that

"numcol = Cells (myfinded.Row, Columns.count). End (xlToLeft). Column"

is used to take the last column of the table, though, I don't have idea why, it's ok on the sample file I did to this forum.

3. Is not calculating the maximum, minimum and average interval correctly, although the function is apparently right.

Can, please, somebody teach me how to fix these things?

[westconn1]

Moreover, in the original workbook, it can not understand that

my guess would be myfinded is nothing

[marlonsaveri]

but
MsgBox myfinded.Row returns 9 and
MsgBox Columns.Count returns the number of columns. It's ok.

When I try "msgbox "hello world" inside this code, it's ok

If Not myfinded Is Nothing Then
        numcol = Cells(myfinded.Row, Columns.Count).End(xlToLeft).Column
        msgbox "hello world"
    End If

[westconn1]

as i can not open your workbook, i could only take a guess, sorry it is wrong

what do you get with msgbox numcol?
what type is numcol defined as?
what error do you get?

[marlonsaveri]

Thanks again,

Each time is a error, for example, 1004, that couldn't find "average" in worksheetfunction class.

Msgbox numcol returns 10, ok in this sample, but not in all sheets.
numcol is long.


That's the code:

    Option Explicit
    Dim numlin As Long
    Dim numcol As Long


Private Sub CommandButton1_Click() 'close button
    prop_especific.Hide
'   No colors
    With Cells.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
End Sub

Private Sub CommandButton2_Click()

    On Error GoTo StudyError
    
'   declare
    Dim myfinded As Range
    Dim col As Integer
    Dim lin As Integer
    Dim MySearchedWord As String
    Dim myrange As Range

'   No colors
    With Cells.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    
'   Hide labels
    LabelMax.Caption = ""
    LabelMin.Caption = ""
    LabelMed.Caption = ""
    LabelMax.Visible = False
    LabelMin.Visible = False
    LabelMed.Visible = False

'   Find combobox value
    MySearchedWord = ComboBox1.Value
    Set myfinded = Cells.Find(what:=MySearchedWord, After:=Range("B3"), LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)
       
'   Paint cells
    Range(myfinded.Address).Interior.Color = RGB(212, 200, 200)
    For lin = myfinded.Row To myfinded.MergeArea(myfinded.MergeArea.count).Row  'for first to last merged cell
       For col = 3 To numcol 'for 3td to last col
         Cells(lin, col).Interior.Color = RGB(212, 200, 200)
         Next
    Next

    
'   Get max, min and average of myrange
    For lin = myfinded.Row To myfinded.MergeArea(myfinded.MergeArea.count).Row
        For col = 4 To numcol
            If Not myrange Is Nothing Then
                Set myrange = Union(myrange, Cells(lin, col))
             Else
                Set myrange = Cells(lin, col)
            End If
        Next col
    Next lin

    LabelMax.Visible = True
    LabelMin.Visible = True
    LabelMed.Visible = True
    
    LabelMax.Caption = "Max: " & WorksheetFunction.max(myrange)
    LabelMin.Caption = "Min: " & WorksheetFunction.min(myrange)
    LabelMed.Caption = "Av: " & WorksheetFunction.Average(myrange)
    Exit Sub
    
StudyError:

End Sub



Private Sub UserForm_Initialize()
    
    On Error GoTo StudyError
    
    Dim maxlin As Integer 'keep property numb
    Dim maxcol As Integer 'get number of data about each property
    Dim counties As String 'guarda a counties
    Dim i As Integer

'   Discover how many rows and col there's in the table
    Dim myfinded As Range
    Dim MySearchedWord As String
    
    'rows:
    numlin = Range("A" & Rows.count).End(xlUp).Row
    numlin = Range("A" & numlin).MergeArea.count + numlin
    
    'find word "counties", go to the end of the table to know the number of col
    MySearchedWord = "counties"
    Set myfinded = Cells.Find(what:=MySearchedWord, After:=Range("A1"), LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)
    If Not myfinded Is Nothing Then
        numcol = Cells(myfinded.Row, Columns.count).End(xlToLeft).Column
    End If
MsgBox numcol

'   Put things in combobox
    For i = 10 To numlin 'we don't count titles rows
            ComboBox1.AddItem Cells(i, 2).FormulaR1C1
    Next
    
'   Remove empty items
    For i = 1 To ComboBox1.ListCount - 1
        If Len(ComboBox1.List(i)) = 0 Then
           ComboBox1.RemoveItem i
        End If
    Next
    
    Exit Sub
    
StudyError:
    
End Sub

[westconn1]

For i = 1 To ComboBox1.ListCount - 1
If Len(ComboBox1.List(i)) = 0 Then
ComboBox1.RemoveItem i
End If
Next

you should work from the bottom up when removing items, each time an item is removed it changes the indexes of items below and causes the next item to be missed
this probably has nothing to do with your current problem, but something you need to be aware of


on doing some testing using parts of your code, if found that i was actually returning range objects on a different worksheet to what i was expecting, this gave me 1004 errors on average function and returned 0 for max and min, on fully qualifying ranges when setting range objects gave me correct results
you can test this with

msgbox myrange.parent.name

, the result surprised me as i was sure i was working with sheet1, but ..............

[marlonsaveri]

this is getting weirder and weirder.
The first time I run the form, using "for" as you said, it works. To be sure, I've put a "msgbox" in this part:

    If Not myfinded Is Nothing Then
        numcol = Cells(myfinded.Row, Columns.count).End(xlToLeft).Column
    End If

'   Put things in combobox
    For i = 10 To numlin 'we don't count titles rows
            ComboBox1.AddItem Cells(i, 2).FormulaR1C1
    Next
    
    MsgBox ComboBox1.ListCount & ". numcol = " & numcol
    
'   Remove empty items
    For i = ComboBox1.ListCount - 1 To 1
        If Len(ComboBox1.List(i)) = 0 Then
           ComboBox1.RemoveItem i
        End If
    Next

However, when I try to open the form a second time, it just opens, without showing the msgbox and numcol is not respected

[marlonsaveri]

If I do:

Private Sub UserForm_Initialize()
        MsgBox "why????"

the "msgbox" doesn't appear


About

msgbox myrange.parent.name

I use a portuguese version of Excel. So, it doesn't show: "sheet1", but "plan1"

==============

The number of items remains the same:

    lastcount = ComboBox1.ListCount - 1    
'   Remove empty items
    For i = lastcount To 1
        If Len(ComboBox1.List(i)) < 1 Then
           ComboBox1.RemoveItem i
        End If
    Next
    MsgBox ComboBox1.ListCount 'there are no difference between combobox1.listcount and lastcount yet

[westconn1]

For i = lastcount To 1

you need to tell it to go backwards

For i = lastcount To 1 step -1

If I do:
Code:

Private Sub UserForm_Initialize()
MsgBox "why????"

it does show when i test

The first time I run the form, using "for" as you said, it works. To be sure, I've put a "msgbox" in this part:

i do not know when it is supposed to run or why it only runs once, maybe post a sample workbook the demonstrates the problem

[nO_OnE]

If I do:

Private Sub UserForm_Initialize()
        MsgBox "why????"

the "msgbox" doesn't appear

I have had problems in excel with the Initialize events, sometimes its like the object has not completely loaded before it trys to run the code.

What I ended up doing, with Pete's help, was putting the code I wanted in the Initialize event in another sub, then in the initialization event, use the application.ontime to to run the sub, with the code, after a 1 second delay.