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

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,344
Office Version
  1. 365
Platform
  1. Windows
You can't compare a range of cells to one value.

What do you actually want to do?
 

ms_metis

New Member
Joined
Sep 21, 2005
Messages
30
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.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,344
Office Version
  1. 365
Platform
  1. Windows
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
 

ms_metis

New Member
Joined
Sep 21, 2005
Messages
30

ADVERTISEMENT

Couldn't get it to work.
 

ms_metis

New Member
Joined
Sep 21, 2005
Messages
30
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
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,344
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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?
 

ms_metis

New Member
Joined
Sep 21, 2005
Messages
30
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)
 

ms_metis

New Member
Joined
Sep 21, 2005
Messages
30
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)
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,344
Office Version
  1. 365
Platform
  1. Windows
But how are you generating the numbers?

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

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,999
Messages
5,834,809
Members
430,323
Latest member
Regash

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
Top