'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'


Here is my code
Sub RAND_Generator()
Dim r As Range
Dim c As Range
Dim a As Range
Dim b As Range
Set b = Worksheets("Calc-Census - Current State").Range("A3:A65000")
Application.ScreenUpdating = False
For Each c In Worksheets("RAND_Input").Range("A3:L65000").Cells
If b.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]

Where do you get the error?

PS Can you add code tags when posting code?

[kasan]

In your first For replace

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

[bsquad]

Thank you for the replies,

kasan, I did change and I am still unfortunately getting that error.


Norie,

I am not sure I completely follow after reading your link about code tags, and I have never been asked this. If this is just a feature/setting I turn on I would gladly do it.