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

 

[ms_metis]

It's getting data from another program (a VB program). I have to use VBA because the worksheet is working with an outside program to make a webpage based on the information entered in the first program and the code in the excel sheet.


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$4" Then
Range("B25:B31").ClearContents
Do Until MY_FIRST <> MY_SECOND And MY_SECOND <> MY_THIRD And MY_THIRD <> MY_FOURTH And _
MY_FOURTH <> MY_FIFTH And MY_FIFTH <> MY_SIXTH
MY_FIRST = Int((7 * Rnd) + 1)
MY_SECOND = Int((7 * Rnd) + 1)
MY_THIRD = Int((7 * Rnd) + 1)
MY_FOURTH = Int((7 * Rnd) + 1)
MY_FIFTH = Int((7 * Rnd) + 1)
MY_SIXTH = Int((7 * Rnd) + 1)
Loop
Select Case Range("B4").Value
Case 1
Range("B25:B30").Value = 0
Case 7
Range("B25").Value = 1
Range("B26").Value = 2
Range("B27").Value = 3
Range("B28").Value = 4
Range("B29").Value = 5
Range("B30").Value = 6
Case 2 To 6
n = Range("B4").Value
Dim r, num(5), used(6)
For x = 1 To 5: used(x) = 0: Next
For x = 1 To 5
1 r = Int(Rnd * 6) + 1
If used(r) = 1 Then GoTo 1
num(x) = r: used(r) = 1
Cells(24 + x, 2) = num(x)
Next
End Select
End If
End Sub

 

[Ronald Moore]

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

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 -- one of your rare mistakes? I don't see CountIf in that code, and in any case, > 1 should have been > 0 .

ms_metis -- if that code had been executed you would have received a run time error. Evidently you did nothing to trigger the worksheet calculate event so you may need to reconsider your thinking here.

My suggestion for your original question:

Cells(19, 2).Value = Sgn(Application.CountIf(Range("B25:B30"), 1))

 

[ms_metis]

Got it to work. Thanks...but I guess I should have explained all I needed done.

See, I need this to be done:

When the number 1 is randomly generated (in cells B25 to B29), I need 1 placed in cell B19 (O in B19 if 1 isn't generated)

When the number 2 is randomly generated (in cells B25 to B29), I need 1 placed in cell B20 (O in B20 if 2 isn't generated)

When the number 3 is randomly generated (in cells B25 to B29), I need 1 placed in cell B21 (O in B21 if 3 isn't generated)

and so on until when the number 6 is randomly generated (in cells B25 to B29), I need 1 placed in cell B24 (O in B24 if 6 isn't generated).

--B

 

[Norie]

Norie -- one of your rare mistakes? I don't see CountIf in that code, and in any case, > 1 should have been > 0 .

Just typos.

 

[ms_metis]

Can I use that command:
Cells(19, 2).Value = Sgn(Application.CountIf(Range("B25:B30"), 1))

multiple times? That is, can I say:
Cells(19, 2).Value = Sgn(Application.CountIf(Range("B25:B30"), 1))
Cells(20, 2).Value = Sgn(Application.CountIf(Range("B25:B30"), 2))
Cells(21, 2).Value = Sgn(Application.CountIf(Range("B25:B30"), 3))

I tried it and it wouldn't work. It seemed to be looping and not stopping.

--B

 

[Ronald Moore]

Got it to work. Thanks...but I guess I should have explained all I needed done.

See, I need this to be done:

When the number 1 is randomly generated (in cells B25 to B29), I need 1 placed in cell B19 (O in B19 if 1 isn't generated)

When the number 2 is randomly generated (in cells B25 to B29), I need 1 placed in cell B20 (O in B20 if 2 isn't generated)

When the number 3 is randomly generated (in cells B25 to B29), I need 1 placed in cell B21 (O in B21 if 3 isn't generated)

and so on until when the number 6 is randomly generated (in cells B25 to B29), I need 1 placed in cell B24 (O in B24 if 6 isn't generated).

--B

Can I use that command:
Cells(19, 2).Value = Sgn(Application.CountIf(Range("B25:B30"), 1))

multiple times? That is, can I say:
Cells(19, 2).Value = Sgn(Application.CountIf(Range("B25:B30"), 1))
Cells(20, 2).Value = Sgn(Application.CountIf(Range("B25:B30"), 2))
Cells(21, 2).Value = Sgn(Application.CountIf(Range("B25:B30"), 3))

I tried it and it wouldn't work. It seemed to be looping and not stopping.

--B

The statements in the second quote should work as you desire in the first quote, provided that you mean the number 0 instead of the letter O. I don't think those statements are your problem.

I'm not sure what you mean by: "it wouldn't work. It seemed to be looping and not stopping."? I'm having trouble following the code you've posted so far, so I'm not sure I'll be able to help.

The most likely explanation, based on a cursory review, is that the worksheet change code you posted earlier is writing to the worksheet itself, and therefore producing further worksheet change events. Thus the code will be activated again and again, indefinitely until you run out of memory or resources. To prevent this, you need to disable application events at procedure entry and reenable just before exit. Insert this statement as the first statement of your worksheet change code:

Application.EnableEvents = False

and this statement as the last statement:

Application.EnableEvents = True

As I said earlier, I'm having trouble understanding the code posted so far. Let me ask if your end goal is to produce a random permutation of the numbers 1 through 6 in B25:B30. (That is, the numbers 1 through 6 in some random sequence.) Or, in the last part of the worksheet change code, it looks like you're trying to come up with a random permutation of the numbers 1 through 5. If you wish, I can provide you with some fairly simple code to produce a random permutation of the integers 1 to n, for a given integer n.

 

[ms_metis]

In this worksheet:

1)
I need to randomly generate a set of numbers between 1 to 6 based on a number in cell B4. B4 will equal 1 to 7. Also, numbers can only be used once.


If B4 = 1, 1 random number will be generated & placed in cell B23.
If B4 = 2, 2 random numbers will be generated & placed in cell B23 & B24.
If B4 = 3, 3 random numbers will be generated & placed in cell B23-B25.
If B4 = 4 4 random numbers will be generated & placed in cell B23-B26.
If B4 = 5 , 5 random numbers will be generated & placed in cell B23-B27.
If B4 = 6 , numbers 1-6 will be placed in cell B23-B25.
If B4 = 7, zeros will be placed in cells B23-B28.


2)
If the number 1 is generated in cells B23 to B28, I need a 1 placed in B17. If 1 isn't generated in cells B23 to B28, I need a zero placed in B17.

If 2 is generated in cells B23 to B28, I need a 1 placed in B18. If 2 isn't generated in cells B23 to B28, I need a zero placed in B18.

If 3 is generated in cells B23 to B28, I need a 1 placed in B19. If 3 isn't generated in cells B23 to B28, I need a zero placed in B19.

If 4 is generated in cells B23 to B28, I need a 1 placed in B20. If 4 isn't generated in cells B23 to B28, I need a zero placed in B20.

If 5 is generated in cells B23 to B28, I need a 1 placed in B21. If 5 isn't generated in cells B23 to B28, I need a zero placed in B21.

If 6 is generated in cells B23 to B28, I need a 1 placed in B22. If 6 isn't generated in cells B23 to B28, I need a zero placed in B22.

 

[Ronald Moore]

I see some discrepancies in your latest response and the responses and code you posted earlier. In the code, and in your earlier posts, the range to hold your randomly selected numbers was B25:B30, and the 0,1 indicators for whether a number appears among the randomly selected numbers are in B19:B24. Also, in the code, an input value of 1 in cell B4 results in all zeroes being returned, whereas an input of 7 does this per your latest post. For the sake of discussion, I'll assume the latest post is what you want.

Now some comments about your code. First, it would be a great help to others in the future if you could take a few minutes to learn how to use the code tags around your code. You can use the code button above the input window to generate the tags. I didn't spend too much time trying to understand your code for that reason. After further review, the code was generating further worksheet change events but these did not continue indefinitely. That wasn't your problem, but still as a matter of good practice you should disable application events if your worksheet change event code changes the sheet itself.

You still didn't really clarify what you meant by "it seemed to be looping and not stopping." However, after further study, I may have a guess. If you request 1 to 5 random numbers to be generated, your code will always produce 5 results. regardless of the input value in B4. Look at your code in the case 2 to 6 branch. It always loops from x = 1 to x=5 and always writes 5 results to the worksheet. You don't even use the value of n (in which you stored the input value in cell B4).

In that branch of the select, you generate a random integer r, store that number in an element of array num, and then store that element of the num array in the worksheet. Why do you need the array num, or even a single variable? You can eliminate the middleman and just store the result r directly in the worksheet.

In that same branch of the code, you have an array named "used" which you use to prevent reselection of a previously selected number. Now, if you think about it a bit, you should realize that these "used" flags are exactly what you are trying to compute in your initial post on this thread. I would suggest using the desired range in the worksheet itself to house the "used" flags, and you won't need to worry about COUNTIF at all.

Finally, I have no idea what you're trying to accomplish in your code with MY_FIRST, MY_SECOND, etc and it doesn't appear necessary to accomplish what you want in your last post. All of this leads to my suggested code below. Note the use of range object variables. This isn't strictly necessary, but it requires changing only two or three statements instead of many if you have to move your ranges around for any reason.

Private Sub Worksheet_Change(ByVal Target As Range)

  If Target.Address <> "$B$4" Then Exit Sub
  Dim InputValue As Integer, i As Integer
  Dim rUsedFlags As Range, rRandomNumbers As Range
  Application.EnableEvents = False
  Range("B23:B29").ClearContents  'not too sure about this
  Set rUsedFlags = Range("B17:B22")
  Set rRandomNumbers = Range("B23:B28")
  InputValue = Range("B4").Value
  'initialize used flags to 0, unless input value = 6
  rUsedFlags.Value = -(InputValue = 6)

  Select Case InputValue
    Case 1 To 5
      Dim r As Integer
      For i = 1 To InputValue
        Do
          r = Int(Rnd * 6) + 1  'random integer, 1 to 6
        Loop Until rUsedFlags(r) = 0
        rRandomNumbers(i).Value = r
        rUsedFlags(r) = 1
      Next
    Case 6
      For i = 1 To 6
        rRandomNumbers(i).Value = i
      Next
    Case 7
      rRandomNumbers.Value = 0
  End Select
  
  Application.EnableEvents = True
End Sub