excel vba set range

Dear experts I'm trying to set a range but i get an error 91 object variable or with block not set, on the last line of my function. can someone tell me why this is
I thank you!

function setrange()
dim rng1 as range


With Workbooks(WorkbookName).Sheets("DividedPD's")
x = .Cells(Rows.Count, "A").End(xlUp).Row

rng1 = .Range("A2:B" & x)
end with
end function
LVL 1
MarkVrenkenAsked:
Who is Participating?
 
SteveConnect With a Mentor Commented:
and finally will need Set statement:

Set rng1 = .Range("A2:B" & x)

Open in new window

0
 
SteveCommented:
if you have Option Explicit set.. you may need a line:

Dim x as Long

Open in new window

0
 
SteveCommented:
Also I would define the worksheet object better:
not using:
With Workbooks(WorkbookName).Sheets("DividedPD's")

Open in new window

but try:
Dim wb as workbook: set wb = thisworkbook   'or other workbook as you need
Dim ws as worksheet: set ws = wb.Sheets("DividedPD's")

with ws

Open in new window

0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Connect With a Mentor Microsoft MVP ExcelCommented:
Hello,

when you declare a variable as a range, you need to use the "set" keyword to populate it.

Dim MyRange as Range
Set MyRange = ws.Range("A1")

In your code you also use the . before assigning the value to the variable. That would normally require a WITH statement

With (something)
    .attribute = value
End With

Why don't you post your complete code?

cheers, teylyn
0
 
MarkVrenkenAuthor Commented:
thanks i'm not very clean in coding indeed so it is good that you remind me, but it doesn't help my problem unfortunately. any other ideas?
0
 
MarkVrenkenAuthor Commented:
O i responded to soon. i will try the options
0
 
EricLynnWrightCommented:
See his line.  Looks like you have a space in sheets.



With Workbooks(WorkbookName).Sh eets("Divi dedPD's")
0
 
MarkVrenkenAuthor Commented:
Here's the full code, the set command did the trick thank you! I also have an error when trying to set the rowsource. Can you please also help me with that?

cheers,

Mark

Private Sub UserForm_Initialize()

Dim rng1 As Range
Dim rng2 As Range
Dim x As Long
dim y as long

Dim wb As Workbook: Set wb = ThisWorkbook 
Dim ws As Worksheet: Set ws = wb.Sheets("DividedPD's")

With ws

x = .Cells(Rows.Count, "A").End(xlUp).Row

Set rng1 = .Range("A2:B" & x)

ListBox1.RowSource = rng1

Me.butAdd.Caption = "Add"

y = .Cells(Rows.Count, "J").End(xlUp).Row
 
set rng2 = .Range("J2:K" & y)
 
ListBox2.RowSource = rng2

Me.butAddSP.Caption = "Add"

End With
End Sub

Open in new window


@telelyn. Personally for me it's okay if he posts it in 3 messages;) i'm glad you all help. This site is already a great experience for me since almost all my questions are answered and it saves me a lot of head ache').

Cheers
0
 
Rory ArchibaldConnect With a Mentor Commented:
Rowsource expects an address string so use

listbox1.rowsource = "'" & ws.name & "'!" & rng1.address

Open in new window


or just use:

listbox1.list = rng1.value

Open in new window

0
 
MarkVrenkenAuthor Commented:
Thank you all! really appreciate it. Hope everyone is happy with the dividing of points;)

Cheers:)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.