VBA My if statement wont work

Question

I have only recently started picking up VBA in excel and I have been going through various tutorials online but I have encountered a bit of a problem when getting to if statements. Based on the knowledge i have gained online and from my VB knowledge i came up with the following code.

Private Sub CommandButton1_Click()
   Dim mynum As Integer, checker As String
   mynum = Range("A1:A10").value

   If mynum > 0 Then
        checker = "check"
   Else
        checker = "missing"
   End If

   Range("B1:B10").value = checker
End Sub

The idea is that if there is a number over 0 in column a, the adjacent cell in column b is checked, if however its 0 or lower the adjacent cell says missing (if that makes any sense) theres no real point to this as I am learning the basics at the moment :)

Now my problem...basically when i attempt to debug this, I get the Type Mismatch error and i cant really see why this is so. I know that it works when i set the range as one cell rather than multiple cells so my best guess is that it has something to do with that. I have looked into it but again im getting results that only back up that this should work. I must have misread it somewhere but help would be appreciated. :)

Siddharth Rout · Accepted Answer · 2012-08-31 08:23:35Z

7

You cannot Assign/Check values of range like this for what you are trying to do. One way would be to loop through your range. For Example

Private Sub CommandButton1_Click()
    Dim Rng As Range, aCell As Range

    Set Rng = Range("A1:A10")

    For Each aCell In Rng
        If aCell.Value > 0 Then
            aCell.Offset(, 1).Value = "check"
        Else
            aCell.Offset(, 1).Value = "missing"
        End If
    Next
End Sub

EDIT

BTW, you don't need VBA for this. If you are open for a Non VBA solution then simply put this formula in Cell B1 and copy it down

=IF(A1>1,"Check","Missing")

edited Aug 31, 2012 at 8:23

answered Aug 31, 2012 at 8:16

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Zoe Over a year ago

Hi Siddharth thank you for the fast response, I should have thought about looping it thank you for the example as well it makes more sense now :)

Collectives™ on Stack Overflow

VBA My if statement wont work

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related