?
Solved

excel vba set range

Posted on 2012-09-17
11
Medium Priority
?
684 Views
Last Modified: 2012-09-17
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
0
Comment
Question by:MarkVrenken
10 Comments
 
LVL 24

Expert Comment

by:Steve
ID: 38405014
if you have Option Explicit set.. you may need a line:

Dim x as Long

Open in new window

0
 
LVL 24

Expert Comment

by:Steve
ID: 38405025
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
 
LVL 24

Accepted Solution

by:
Steve earned 668 total points
ID: 38405028
and finally will need Set statement:

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

Open in new window

0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 50

Assisted Solution

by:Ingeborg Hawighorst (Microsoft MVP / EE MVE)
Ingeborg Hawighorst (Microsoft MVP / EE MVE) earned 664 total points
ID: 38405030
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
 
LVL 1

Author Comment

by:MarkVrenken
ID: 38405031
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
 
LVL 1

Author Comment

by:MarkVrenken
ID: 38405039
O i responded to soon. i will try the options
0
 
LVL 3

Expert Comment

by:EricLynnWright
ID: 38405047
See his line.  Looks like you have a space in sheets.



With Workbooks(WorkbookName).Sh eets("Divi dedPD's")
0
 
LVL 1

Author Comment

by:MarkVrenken
ID: 38405058
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
 
LVL 85

Assisted Solution

by:Rory Archibald
Rory Archibald earned 668 total points
ID: 38405075
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
 
LVL 1

Author Closing Comment

by:MarkVrenken
ID: 38405088
Thank you all! really appreciate it. Hope everyone is happy with the dividing of points;)

Cheers:)
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question