VBA - Find all the White Cells and populate them with a Random Value.

[Tim77]

Hi all,

I am Junior on VBA and I can not make it with the below task. I have a huge worksheet with White and Grey Cells. My task is to find all the white Cells and Populate them with a Random value. Can you advise?

Thanks in advance,

 

[Fluff]

How about

Sub Tim77()
   Dim Cl As Range
   Const MinNum As Long = 1
   Const MaxNum As Long = 1000
   
   ActiveSheet.Unprotect "Password"
   Randomize
   For Each Cl In Range("A2:X100")
      If Cl.Interior.Color = vbWhite Then
         Cl.Value = Int((MaxNum - MinNum + 1) * Rnd() + MinNum)
      End If
   Next Cl
   ActiveSheet.Protect "Password"
End Sub

Change the password to suit

 

[CSmith]

@CSmith
You will also get a compile error on this If ws = Nothing Then

Agree should have been If ws Is Nothing Then _

 

[Tim77]

How about

Sub Tim77()
   Dim Cl As Range
   Const MinNum As Long = 1
   Const MaxNum As Long = 1000
  
   ActiveSheet.Unprotect "Password"
   Randomize
   For Each Cl In Range("A2:X100")
      If Cl.Interior.Color = vbWhite Then
         Cl.Value = Int((MaxNum - MinNum + 1) * Rnd() + MinNum)
      End If
   Next Cl
   ActiveSheet.Protect "Password"
End Sub

Change the password to suit

...some error with the comment "The password you supplied is not correct"

 

[CSmith]

You need to change to the password you used when protecting the worksheet.

 

[Tim77]

Only the Grey Cells are been protected with Password (that I do not know) and the White ones are the ones that I have to populate.

So I made a Loop

For Each Rng In ActiveSheet.Range("N9:CJ10000")
If Rng.Interior.ColorInder <> 15 Then
Rng.Value = Int ((MaxNum- MinNum +1)* Rnd +MinNum)
End If
Next Rng


The issue is that some random values are been populated and assigned and after some seconds a Run-time Error 1004 appears on the Rng.Value = Int line. Talking about protected sheet etc...

 

[CSmith]

This is what line 2 of your loop code should be:

If Rng.Interior.ColorIndex <> 15 Then

If Rng.Interior.ColorInder <> 15 Then

 

[Fluff]

after some seconds a Run-time Error 1004 appears on the Rng.Value = Int line. Talking about protected sheet etc..

That means you are trying to change a locked cell. Either remove the sheet protection before running, as I showed, or make sure that all white cells are unlocked.