I have written a GetRange function that populates a range based on a certain criteria as below. Now when I try to use the range created to display on to a listbox on a form, nothing gets displayed even though printing the GetRange below to a sheet works perfectly fine.
End Sub
VBA Code:
Public Function GetRange() As Range
Dim i As Long
Dim lastRowMvmtConsol As Long
Dim mvmtConsolSheet As String
mvmtConsolSheet = "Cash_Stock_Mvmt_Consol"
lastRowMvmtConsol = ActiveWorkbook.Sheets(mvmtConsolSheet).Range("A1").End(xlDown).Row
Set GetRange = ActiveWorkbook.Sheets(mvmtConsolSheet).Range("A1:R1")
'Set GetRange = ActiveWorkbook.Sheets(mvmtConsolSheet).Range("A1:R1")
For i = 2 To lastRowMvmtConsol
If ActiveWorkbook.Sheets(mvmtConsolSheet).Cells(i, 12).Value = "Transfers-In" Then
Set GetRange = Application.Union(GetRange, ActiveWorkbook.Sheets(mvmtConsolSheet).Range("A" & i & ":R" & i))
End If
Next i
'GetRange.Copy ActiveWorkbook.Sheets("Sheet1").Range("A1")
End Function
Private Sub Addtolistbox()
Dim rg As Range
Dim mvmtConsolSheet As String
mvmtConsolSheet = "Cash_Stock_Mvmt_Consol"
Set rg = GetRange()
'Set rg = ActiveWorkbook.Sheets(mvmtConsolSheet).Range(rg)
With lstDatabase
.RowSource = rg.Address '(external:=True)
.ColumnCount = rg.Columns.Count
.ColumnWidths = "50,50,60,60,60,60,70,80,90,90,90,90,90,90,90,90,90,90"
.ColumnHeads = True
'.ListIndex = 0
End With
End Sub
Last edited by a moderator: