# Range, If, Then, Else, End If

#### ms_metis

##### New Member
I'm having problems getting this code to work. Any suggestions? What am I doing wrong?

If Range("B25:B30") = 1 Then
Cells(19, 2) = 1
Else
Cells(19, 2) = 0
End If

Thanks

### Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
You can't compare a range of cells to one value.

What do you actually want to do?

If the number 1 is present in cells B25, B26, B27, B28, B29, or B30, I would like a 1 placed in Cell B19. If a 1 isn't present, I would like a 0 placed in cell B19.

Well you could use the CountIf worksheet function.
Code:
``````If Application.WorksheetFunction(Range("B25:B30"), 1)  > 1 Then
Cells(19, 2) = 1
Else
Cells(19, 2) = 0
End If``````

Couldn't get it to work.

There are other things going on in the worksheet. For example, I'm doing a randomizing feature.

This is the code I used:
Private Sub Worksheet_Calculate()

If Application.WorksheetFunction(Range("B25:B30"), 1) > 1 Then
Cells(19, 2) = 1
Else
Cells(19, 2) = 0
End If

End Sub

Nothing happened when 1 was present in cell B25

The code you posted is a worksheet calculate event.

It will get triggered when the worksheet is, well, calculated.

What else is going on?

How are you populating the cells?

1) 6 numbers are being randomly chosen based on a cell. For example of cell B4 is 6, 5 random numbers are generated (from 1-6) and placed in cells B25 to B29.

2) When certain random numbers are chosen, things happen. For example, if 2 is one of the random numbers chosen, I want Cell B20 to = 1 (if it isn't chosen, I want it to equal 0)

1) 6 numbers are being randomly chosen based on a cell. For example of cell B4 is 6, 5 random numbers are generated (from 1-6) and placed in cells B25 to B29.

2) When certain random numbers are chosen, things happen. For example, if 2 is one of the random numbers chosen, I want Cell B20 to = 1 (if it isn't chosen, I want it to equal 0)

But how are you generating the numbers?

Why are you using VBA code? Couldn't you just use worksheet functions?

Replies
1
Views
141
Replies
16
Views
842
Replies
4
Views
273
Replies
13
Views
365
Replies
1
Views
80

1,217,433
Messages
6,136,597
Members
450,021
Latest member
Jlopez0320

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back