Insert cell .Address as string into Range() ?

Question

I have a piece of code that should loop through each cell in a Range. Under certain conditions those cell Addresses (e.g., "E8") should be added to an array, and each cell in that range should be changed to the "Good" style.

However when I run this, I get the error:

Run-time error '1004':

Method 'Style' of object 'Range' failed

Here is my code:

Dim cell As Range

Dim cellArray() As Variant

For Each cell In Range("A8:H8")

   If (IsEmpty(cell.Value)) And (cell.Style <> "Bad") Then

      ReDim cellArray(cell + 1)

      cellArray(cell) = cell.Address(False, False)

      Range(cell.Address(False, False)).Style = "Good"

   End If

Next cell

If I try to print Range(cell.Address(False, False)) to a MsgBox, it comes back blank. I also tried assigning Range(cell.Address(False, False)) to a variable "r" which I dimensioned as both a String and a Range, but both times there were other errors.

Any help appreciated.

Answer 1

As soon as I responded I tried unprotecting the sheet with

Sheets("Worksheet1").Unprotect

That was the issue!

Answer 2

Hi Andreas,

Unfortunately I can't share the file. Thanks for your continued help though. 

All of those variables are only used in that specific loop so I can't imagine what other external factors would be causing that line to error out.

Answer 3

I was just using "Good" as a test (since I haven't really found a lot of documentation on specific styles other than Good or Bad) to see if I could even get the cells to change color at all, which I still unfortunately can't since

cell.Style = "Good" 

doesn't work and throws the error I mentioned.

Thanks for the code on the replacement test. Now I just have to figure out why the error is thrown when I try to set the style.

Answer 4

Run-time error '1004':

Method 'Style' of object 'Range' failed

This error is just a symptom of an other circumstance, we need to see your file.

Please upload your file (maybe with anonymous data) on an online file hoster like www.dropbox.com and post the download link here.

A macro to anonymize data in selected cells can be downloaded here:

https://www.dropbox.com/s/rkfxuh85j5wyj9y/modAnonymize.bas?dl=1

Andreas.

Answer 5

I am a little confused as to why you are setting the style to "Good" if it does not meet the spec of containing a value but the following code is how to handle whether any data was assigned to the array.

Uses a Boolean variable and set it to True if the code enters the If statement and adds a value to the array.

Sub Test()

    Dim cell As Range

    Dim cellArray() As Variant

    Dim bInValid As Boolean    'Additional variable

    Dim j As Long

    j = 1

    ReDim cellArray(1 To j)

    For Each cell In Range("A8:H8")

        If (IsEmpty(cell.Value)) And (cell.Style <> "Bad") Then

            ReDim Preserve cellArray(1 To j)

            cellArray(j) = cell.Address(False, False)

            bInValid = True    'Set to True if code enters the If routine

            j = j + 1

            cell.Style = "Good"

        End If

    Next cell

    If bInValid Then   'Replacement If test

        MsgBox "All cells must have a value to continue. Please enter a value for " & Join(cellArray, ", ")

    Else

        MsgBox "Else"       'Used only for testing. Insert your other code in lieu

    End If

End Sub