Range, If, Then, Else, End If

ms_metis

New Member
Joined
Sep 21, 2005
Messages
30
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?
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 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)
 
Upvote 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)
 
Upvote 0
But how are you generating the numbers?

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

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top