Range, If, Then, Else, End If

[ms_metis]

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

 

[Norie]

You can't compare a range of cells to one value.

What do you actually want to do?

 

[ms_metis]

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 you could use the CountIf worksheet function.

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

 

[ms_metis]

ADVERTISEMENT

Couldn't get it to work.

 

[ms_metis]

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]

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]

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]

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]

But how are you generating the numbers?

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