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

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