Application-defined or object-defined error

[Philosophaie]

Error:

Run-time error '1004':
Application-defined or object-defined error

Compares cells in column Z with the Random number generated to see if it has
already been generated. I think it has something to do with "m".

RandomNumber = Int(1000 * Rnd + 1)
m = .Cells(1, 27)
For k = 1 To m
If RandomNumber = .Cells(k, 26) Then
RandomNumber = Int(1000 * Rnd + 1)
End If
Next k
.Cells(m, 26) = RandomNumber
m = m + 1
.Cells(1, 27) = m

 

[Gary''s Student]

Sub servient()
With Sheets("Sheet1")
RandomNumber = Int(1000 * Rnd + 1)
m = .Cells(1, 27).Value
For k = 1 To m
If RandomNumber = .Cells(k, 26).Value Then
RandomNumber = Int(1000 * Rnd + 1)
End If
Next k
.Cells(m, 26).Value = RandomNumber
m = m + 1
.Cells(1, 27).Value = m
End With
End Sub

 

[ker_01]

Can you post a larger chunk of your code?

..Cells: Anytime you see a keyword prefaced by a ".", I'd expect to see a
'With X' statement above it somewhere, and an 'end with' below somewhere. If
you are missing this prefix, that would cause problems.

Sheet1.cells(1,27) refers to cell "AA1" in Column/Row notation. So, assuming
you have a valid value in AA1; you are checking each value in Col Z for that
many rows, and if you find a match, you generate a new random number.

I'm not sure if this will really have the result you intend; you reset the
random number and continue checking the rest of column Z, so it is possible
that the new random number will match one of the rows you have already
processed- your code doesn't go back and check previous values for matches
when you create a new random number.

There are a couple of ways to address this, but the most simple option would
be to set k=0 when you create the new random number, so your loop will start
searching from the top again.

Alternatively, you could use application.match against all of column Z and
determine if there are any matches, rather than cycling through each value
one at a time.

(aircode)

FoundMatchingValue = 1
Do Until IsError(FoundMatchingValue)
RandomNumber = Int(1000 * Rnd + 1)
FoundMatchingValue = Application.Match(RandomNumber,
Sheet1.Range("Z1:Z100"), False)
Loop

HTH,
Keith