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
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,057
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,057
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,057
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,057
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,118,451
Messages
5,572,201
Members
412,447
Latest member
immy
Top