runtime error 91 Object variable or with block variable not set

[bsquad]

okay so I am still not really picking up on the syntax of VBA coming from having heavy SQL experience.

I am pretty sure I am having an issue with the declarations but can't figure it out.

- I totally stole this code from another post and was adjusting it based on my parameters.

I am attempting to have it check on one sheet (Sheet1) if A1, A2.... is occupied, if it is, then I want to generate random numbers in Range A3 up to L65000 on the my current sheet (RAND_Input) depending on how many rows are occupied on Sheet1

The error I am getting is 'Object variable or With block variable not set'

Sub RAND_Generator_v4()
Dim r As Range, c As Range, a As Range, d As Range
Set d = Worksheets("Calc-Census - Current State").UsedRange
Application.ScreenUpdating = False
For Each c In ActiveSheet.UsedRange
If d.Text <> "" Then
If r Is Nothing Then
Set r = c
Else
Set r = Union(r, c)
End If
End If
Next c
For Each a In r.Areas
With a
.Formula = "=rand()"
.Value = .Value
End With
Next a
End Sub




ORIGINAL CODE

Its base on if the cell color is red, which I did try using with conditional formatting but it didn't work for me.

Sub randthing()
Dim r As Range, c As Range, a As Range
Application.ScreenUpdating = False
For Each c In ActiveSheet.UsedRange
If c.Interior.ColorIndex = 3 Then
If r Is Nothing Then
Set r = c
Else
Set r = Union(r, c)
End If
End If
Next c
For Each a In r.Areas
With a
.Formula = "=rand()"
.Value = .Value
End With
Next a
End Sub

 

[Norie]

What are you actually trying to do in the first loop?

Why in the first loop to you have this?

     If d.Text <> "" Then

 

[Sektor]

d.Text <> "" makes sense until UsedRange returns one cell, but this is hard to say what you try to accomplish.

 

[bsquad]

My apologies I had renamed the sheets,

I am attempting to have it check on one sheet (Calc-Census - Current State) if A1, A2.... is occupied, if it is, then I want to generate random numbers in Range A3 up to L65000 on the my current sheet (RAND_Input) depending on how many rows are occupied on Calc-Census - Current State

 

[Norie]

Do both sheets have the same used range?

 

[bsquad]

yes so if in sheet (Calc-Census - Current State) it goes up to row '20,000'. I want it to generate RAND() A3:L20002 on the RAND_Input

 

[dmt32]

Hi,

try changing this line:

If d.Text <> ""

to this:

If c.Text <> ""

should remove the error.

Dave

 

[Norie]

I don't see any connection between the 2 sheets in the posted code.

If you want to put a random value on in every cell on a sheet where the corresponding cell on another sheet has a value try this.

Sub RAND_Generator_v4()
Dim r As Range, c As Range, a As Range, d As Range

    Set d = Worksheets("Sheet2").UsedRange
    Application.ScreenUpdating = False
    For Each c In d.Cells
        If c.Value <> "" Then
            If r Is Nothing Then
                Set r = c
            Else
                Set r = Union(r, c)
            End If
        End If
    Next c
    
    For Each a In r.Areas
        With Sheets("Sheet1").Range(a.Address)
            .Formula = "=rand()"
            .Value = .Value
        End With
    Next a
    
End Sub

 

[bsquad]

Norie,

you are the smartest person on the planet.


Thank you