I'm looping through some data in Excel's "temp" sheet to see if any cells are red (this indicates an error).

I run my data (2 columns of data, 8 rows long) in Sheet "temp" through a test before running the code below, and I mark the data that passes as green (i.e., RGB) (0, 200, 0). I then want to check another sheet called "main" to see if all 8 rows passed:

```  dim errorz(0) as variant
dim errorz_string as string

for i = 1 to 8

if sheets("temp").cells(i, 2).interior.color <> RGB(0, 200, 0) then

sheets("temp").cells(i, 3) = "Not verified"
sheets("temp").cells(i, 3).interior.color = RGB(200, 0, 0)

redim preserve errorz( UBound(errorz)-LBound(errorz) + 1 )
errorz( UBound(errorz)-LBound(errorz) ) = sheets("temp").cells(i, 1)

end if

next i

if  UBound(errorz) - LBound(errorz) = 0 then
sheets("main").cells(1,1) = "Yes all 8 in temp verified."
end if

if  UBound(errorz) - LBound(errorz) <> 0 then

for j = LBound(info.errorz) to UBound(info.errorz)
info.errorz(j) = "'" & info.errorz(j) & "'"
next j

errorz_string = Join(info.errorz, ",")
sheets("main").cells(1,1) = "No, missing " & errorz_string & " in temp"

end if

redim errorz(0)
errorz_string = ""
```

As I've never used an array before, I don't understand how to redefine it as empty with length 0, then increase its length in a loop to 1 to make it longer. Does redim errorz(0), for instance, provide an empty array of length 0?

Moreover, would "UBound(errorz)-LBound(errorz)" yield 0 or 1 or an error at the beginning when this array has length 0?

Feb 26, 2023 in Others 334 views

## 1 answer to this question.

In my opinion, using an array in this situation is not very beneficial. Instead, I would advise utilizing a collection. To do this, I rewrote your code:

```Sub program()

Dim errorz_string As String, i, j
Dim colErrorResults As New Collection, error_field

For i = 1 To 8

If Sheets("temp").Cells(i, 2).Interior.Color <> RGB(0, 200, 0) Then

Sheets("temp").Cells(i, 3) = "Not verified"
Sheets("temp").Cells(i, 3).Interior.Color = RGB(200, 0, 0)

End If

Next i

If colErrorResults.Count = 0 Then
Sheets("main").Cells(1, 1) = "Yes all 8 in temp verified."
End If

If colErrorResults.Count <> 0 Then

For Each error_field In colErrorResults

errorz_string = errorz_string & "'" & error_field & "', "

Next error_field

'remove final ',
errorz_string = Left(errorz_string, Len(errorz_string) - 2)

Sheets("main").Cells(1, 1) = "No, missing " & errorz_string & " in temp"

End If

End Sub
```

and another Version where I did some refactoring to use naming of sheets and the guard pattern:

```'if you have custom colors, you can use 'Debug.Print RGB(200, 0, 0)' to find out what the numeric value is.
Private Const GREEN = 51200
Private Const RED = 200

Sub program2()

Dim errorz_string As String, i
Dim colErrorResults As New Collection, error_field

For i = 1 To 8

'select the sheet in the project overview, press F4 and give it a name so you don't have to reference it by its display name. That way the display name can be renamed and your code still works
With wsTempSheet

'Fewer nestings are easier to read. Although "goto" is frowned upon, it allows you to use the guard pattern
If .Cells(i, 2).Interior.Color = GREEN Then GoTo nextLine

.Cells(i, 3) = "Not verified"
.Cells(i, 3).Interior.Color = RED

End With

nextLine:
Next i

If colErrorResults.Count = 0 Then
wsMain.Cells(1, 1) = "Yes all 8 in temp verified."
'here you can exit the sub so you can remove one if nesting
Exit Sub
End If

For Each error_field In colErrorResults

errorz_string = errorz_string & "'" & error_field & "', "

Next error_field

'remove final ',
errorz_string = Left(errorz_string, Len(errorz_string) - 2)

wsMain.Cells(1, 1) = "No, missing " & errorz_string & " in temp"

End Sub```
• 38,010 points

## How to search a file with a particular extension and a particulat string in its name?

You can easily find the file using ...READ MORE

+1 vote

## In community edition I am unable to create tenant ,Getting this error"the filed client IP Address must be a string or array type with maximum length 64"

length is too high READ MORE

## How to convert a string to an integer in JavaScript?

How do I convert a string to ...READ MORE

## How to find a value in an excel column by vba code Cells.Find

Just use: Dim Cell As Range Columns("B:B").Select Set cell = ...READ MORE

## Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

## How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

## Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE