Object Variable or With block variable not set

Raza · #1 01-06-2015, 11:12 PM

Hi!
Can anybody help me?

It returns that object variable or with block variable not set!

Code:

Private Sub CommandButton21_Click()
Dim marks As Range

Dim result As String

marks = Range(Cells(1, 1), Cells(4, 1))
If Range(Cells(1, 1), Cells(4, 1)) >= 50 Then
result = "pass"
Else
result = "fail"
End If

Range("A6") = result

End Sub

if anyone make that correct for me, I'll be thankful!!!!

gmayor · #2 01-07-2015, 03:48 AM

You are ascribing a single value to a range that comprises multiple locations.
Also you have assigned a variable name to the range and then didn't use it? However this will not affect the result.

If any of the values in the range is a below 50 then the following will indicate 'fail'.

Code:

Private Sub CommandButton21_Click()
Dim marks As Range
Dim bFail As Boolean
Dim n As Integer
    Set marks = Range(Cells(1, 1), Cells(4, 1))
    For n = 1 To marks.Rows.Count
        If marks.Cells(n, 1) < 50 Then
            bFail = True
            Exit For
        End If
    Next n
    If bFail Then
        Range("A6") = "Fail"
    Else
        Range("A6") = "Pass"
    End If
End Sub

Raza · #3 01-07-2015, 05:39 AM

Hi GMayor!
Thank You so much!
Perfectly working!!!!

Raza · #4 01-07-2015, 11:10 PM

Hi GMayor!
I applied your code, but that wasn't working with other colums.
I changed the column number "1" to "3 or 4" but failed.

gmayor · #5 01-07-2015, 11:23 PM

The code works for the range you originally posted. What are you now trying to do? Can you post your worksheet so we can see the problem?

Raza · #6 01-07-2015, 11:54 PM

Actually I'm studying and practicing VBA not working on any project or S/T else.
I considered column 1 as range, what is the solution to change the range from column one (1) to another column.

gmayor · #7 01-08-2015, 12:37 AM

You have set the range with

Code:

Range(Cells(1, 1), Cells(4, 1))

the first number in the bracket is the row, the second the column, so your example sets the range as A1 to A4

Raza · #8 01-14-2015, 11:04 PM

Code:

Private Sub CommandButton21_Click()
Dim marks As Range
Dim bFail As Boolean
Dim n As Integer
    Set marks = Range(Cells(1, 2), Cells(4, 2))
    For n = 1 To marks.Rows.Count
        If marks.Cells(n, 2) < 50 Then
            bFail = True
            Exit For
        End If
    Next n
    If bFail Then
        Range("B6") = "Fail"
    Else
        Range("B6") = "Pass"
    End If
End Sub

Hi GMayor!!
I Tried this one for Range B1 to B4 but Failed!!

gmayor · #9 01-15-2015, 12:19 AM

That's because you are calling column 2 of a range that only has 1 column!

The line

Code:

If marks.Cells(n, 2) < 50 Then

should read

Code:

If marks.Cells(n, 1) < 50 Then

Thread Tools
Show Printable Version Email this Page
Display Modes
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Run Time Error '91': Object variable or With block variable not set using Catalogue Mailmerge	Berryblue	Mail Merge	1	11-13-2014 05:36 PM
Run-time error 91 object variable or with block variable not set	JUST ME	Word VBA	4	03-25-2014 06:56 AM
object variable or with block variable not set	MJP143	Excel	1	02-11-2013 05:07 AM
Run-time error '91': Object variable or With block variable not set	tinfanide	Excel Programming	2	06-10-2012 10:17 AM
XML parsing & Object variable not set (Error 91)	tinfanide	Excel Programming	0	12-29-2011 08:43 AM