Range definition in VBA

Topic

New Reply

I’m trying to define a range of cells. Would the following code work?

Dim rng As Range
Set rng = ActiveWorkbook.Worksheets(“Sheet1”).Range(“A5:A20”)

Reply | Quote

Replies

Yes, that’s the way to set a Range variable in VBA. You can now do things with the variable rng, for example

rng.Interior.ColorIndex = 3

or loop through its cells:

Dim oCell As Range
For Each oCell In rng
…
Next oCell

Reply | Quote

Any idea why I get a “Run-time error ‘1004’: Application-defined or Object-defined error” in this line of the module:

Sub ShowDialog()

With frmProperties ‘ <<< Error shows up here
.cmbType.RowSource = ""
.lstName.RowSource = ""
.lstFinish.RowSource = ""
.lstProperties_BOL.RowSource = ""
.lstProperties_EOL.RowSource = ""
.lstName.MultiSelect = fmMultiSelectMulti
.lstFinish.MultiSelect = fmMultiSelectMulti
.lstProperties_BOL.MultiSelect = fmMultiSelectExtended
.lstProperties_EOL.MultiSelect = fmMultiSelectExtended
End With

' Assign material names to ComboBox
GetMaterialNames
' Show the UserForm
frmProperties.Show
End Sub

When I use the following code in the userform module?

Private Sub UserForm_Initialize()

' Worksheets("Properties Database").Activate
' Set rngAllMaterialTypes = ActiveWorkbook. _
Worksheets("Properties Database"). _
Range("A5", Range("A65536").End(xlUp)) ' <<<< WORKS when removing the two comment signs

Set rngAllMaterialTypes = ActiveWorkbook. _
Worksheets("Properties Database"). _
Range("A5", Range("A65536").End(xlUp)) ' <<<< DOESN'T WORK

Reply | Quote

Try

Dim wsh As Worksheet
Set wsh = Worksheets(“Properties Database”)
Set rngAllMaterialTypes = wsh.Range(wsh.Range(“A5”), wsh.Range(“A65536”).End(xlUp))

or

Dim wsh As Range Worksheet
Dim m As Long
Set wsh = Worksheets(“Properties Database”)
m = wsh.Range(“A65536”).End(xlUp).Row
Set rngAllMaterialTypes = wsh.Range(“A5:A” & m)

Reply | Quote

Thanks Hans. I now get a “Run-time error ’13’: Type mismatch” at this line:

With frmProperties

Do you know why?

Reply | Quote

In the second example I should have had

Dim wsh As Worksheet

instead of

Dim wsh As Range

Reply | Quote

In the second example I should have had

Dim wsh As Worksheet

instead of

Dim wsh As Range

That did it. Thanks.

Reply | Quote