Nothing

[miami2k]

I'm always having problems with sheets activation. In this case my range returns as nothing. The comand is activated in a combo placed in a different sheet of the same file.
In any of the set options the result for my i range is "Nothing" and the error if I move on top of the line is Error 1004 “Application-defined or Object-defined error”

Thank you

Private Sub CMBCliente_DropButt*******()


Dim i As Range
Dim Clienti As Worksheet
Dim lrow As Long
Set Clienti = Sheets("Clienti")

With Sheets("Clienti")
lrow = .Cells(Rows.Count, 2).End(xlUp).Row
Set i = ThisWorkbook.Sheets("Clienti").Range(Cells(1, 2), Cells(lrow, 2))
Set i = Clienti.Range(Cells(1, 2), Cells(lrow, 2))
Set i = Clienti.Range(Cells(1, 2), Cells(lrow, 2))

End With
Me.CMBCliente.ListFillRange = i.Address

End Sub

 

[ranman256]

maybe the range was not set...

Private Sub CMBCliente_DropButt*******()


Dim i As Range
Dim Clienti As Worksheet
Dim lrow As Long


Set Clienti = Sheets("Clienti")


With Clienti
  .activate
  Range("A1").select 
  lrow = ActiveSheet.UsedRange.Rows.Count
  Set i = .Range(Cells(1, 2), Cells(lrow, 2))


End With
Me.CMBCliente.ListFillRange = i.Address
End Sub

 

[shg]

In this,

Set i = ThisWorkbook.Sheets("Clienti").Range(Cells(1, 2), Cells(lrow, 2))

The two Cells references are to the active worksheet.

This might work:

Private Sub CMBCliente_DropButt*******()
  Dim wks           As Worksheet

  Set wks = Worksheets("Clienti")
  Me.CMBCliente.ListFillRange = wks.Range("B1", wks.Cells(wks.Rows.Count, "B").End(xlUp))
End Sub