when i am trying to use the range using cells

August 30, 2011 at 02:06:12
Specs: Windows XP
when i am trying to use cells with conditional formatting,
there is an error popping up like,
object variable or with block variable not set
pls tel me how to set the variable
here is the code i used
If (IsEmpty(Cells(i, "A") = False) & (Cells(i, "B").Value.Count > 1)) Then
'Set Cells=
For i = 2 To rowlast
If Cells(i, "B").Value = 9 Then
Cells(i, "B").Copy Destination:=Cells(i, "C")
End If
Next i
Else
If Cells(i, "B").Value = 6 Then
Cells(i, "B").Copy Destination:=Cells(i, "D")
End If

i am getting error in the line where i used IsEmpty function.

thanks in advance


See More: when i am trying to use the range using cells

Report •


#1
August 30, 2011 at 03:13:52
First things first you are missing an End If at the bottom.

Number 2 instead of IsEmpty i would prefer to use the following

If Cells(i,"A") = empty & (Cells(i, "B").Value.Count > 1)) Then
'Set Cells=
For i = 2 To rowlast
If Cells(i, "B").Value = 9 Then
Cells(i, "B").Copy Destination:=Cells(i, "C")
End If
Next i
Else
If Cells(i, "B").Value = 6 Then
Cells(i, "B").Copy Destination:=Cells(i, "D")
End If
end if '''Added end if


Report •

#2
August 30, 2011 at 11:04:18
Please click on the blue line at the end of this post before posting any more code.

That link will take you to the instructions on how to properly post code in this forum.

DerbyDad03
Office Forum Moderator

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#3
August 30, 2011 at 11:25:17
re: "when i am trying to use cells with conditional formatting"

Why do you mention conditional formatting?

I don't see anyting related to conditional formatting in your code.

re:

If (IsEmpty(Cells(i, "A") = False) & (Cells(i, "B").Value.Count > 1)) Then

There is so much that is confusing about that line.

1 - There must be more to your code, since I don't see i being set to anything before it is used in that line.

2 - What is the purpose of the &?

The & is used to concantenate strings and/or strings and variables in VBA.

e.g. This will place Goodbye for now. in A4

Sub asdas()
 myRw = 4
 myStr = "for"
  Range("A" & myRw) = "Goodbye " & myStr & " now."
End Sub

The way you are using it makes no sense to me.

3 - Cells(i, "B").Value.Count > 1

What is that supposed to do?

Cells(i, "B").Value is going to return the value in Cells(i, "B").

There is nothing for VBA to count.

You'll need to explain what you are trying to do before we can begin to offer suggestions on how to modify your code.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions


Ask Question