Loop with multiple Ranges

Question

Im trying to write a code which determines whether certain cells are empty or not and then returns a set string.

To go in detail; I was hoping for the code to look into cell B2, determine if it is empty, then go to C2 and determine if it is non-empty. If both were correct in cell B2 it would then input "Correct" and move on in the range. However, my code doesnt seem to work because it just inputs "Correct" in every cell in the loop range.

I have posted my code below; any help would be much appreciated.

Sub Fill_Rows()


Dim X As Range
Let Y = Range("C2")

For Each X In Range("B2:B5000")
    If X = "" And Y <> "" Then
        X = "Correct"
    End If
    Y = Y + 1
Next X


End Sub

There's no need to check Y in the loop, it only needs to be checked once. have you stepped through the code to see what's happening? Sometimes a cell might look empty but there could be something in it. — SJR, Aug 08 '18 at 13:57
@wildesbare - allow me one remark as you asked already six unaccepted questions: it's good use at *Stack Overflow* to identify a good or helpful answer by marking it as accepted, so possibly you could review them - see ["Someone answers"](https://stackoverflow.com/help/someone-answers); you find further hints at https://stackoverflow.com/tour :-) — T.M., Aug 14 '18 at 08:48
Thank you for the tip of accepting answers. I didnt know i could do that. — wildesbare, Aug 14 '18 at 14:10

score 1 · Answer 1 · answered Aug 08 '18 at 13:55

1

If you meant to check by each row like (B2 and C2) then (B3 and C3), then you could do it like this.

Sub Fill_Rows()
    Dim iRow As Long
    For iRow = 2 To 5000
        If Cells(iRow, "B").Value = vbNullString And Cells(iRow, "C").Value <> vbNullString Then
            Cells(iRow, "B").Value = "Correct"
        End If
    Next iRow
End Sub

answered Aug 08 '18 at 13:55

Pᴇʜ

56,719
10
49
73

Im sorry im still quite new to VBA.... do mind explaining what the vbNullString and how the "B" and "C" work in the cells function – wildesbare Aug 08 '18 at 14:07
`vbNullString` is the same as `""` (some people say `vbNullString` takes less memory and is faster: see [Is there any difference between vbNullString and “”?](https://stackoverflow.com/questions/32435320/is-there-any-difference-between-vbnullstring-and)). The `"B"` and `"C"` are just the column names: `cells()` is used like `Cells(row, column)`. – Pᴇʜ Aug 08 '18 at 14:12

score 0 · Accepted Answer · answered Aug 08 '18 at 16:00

Alternative

Added two solutions:

[1] an example code as close as possible to yours and
[2] an alternative using a datafield array to demonstrate a faster way for bigger data sets.

[1] Example Code close to yours

There is no need to use a second variable Y, all the more as apparently you left it undeclared, which always can cause issues (type mismatches, no range object etc.). So always use Option Explicit in the declaration head of your code module to force yourself to declare all variable types you are using.

Now you can simply use an offset of 1 column to the existing cell to check the neighbouring cell, too.

Option Explicit   ' declaration head of your code module (obliges to declare variables)

Sub Fill_RowsViaRangeLoop()
Dim X As Range, ws As Worksheet
Set ws = ThisWorkbook.Worksheets("MySheet")  ' << replace with your sheet name
For Each X In ws.Range("B2:B5000")
    If X = "" And X.Offset(0, 1) <> "" Then  ' column offset 1 checks next cell in C
         X = "Correct"
    End If
Next X
End Sub

[2] Example Code using a datafield array

Looping through a bigger range isn't very fast, you can speed up your procedure by assigning your range values to a variant datafield array v, loop through the received array items correcting found items in column 1 and write it back to sheet.

Option Explicit   ' declaration head of your code module (obliges to declare variables)

Sub Fill_RowsViaArray()
   Dim v As Variant, i As Long, ws As Worksheet
   Set ws = ThisWorkbook.Worksheets("MySheet")  ' << replace with your sheet name
 ' Assign values to a 2-dim array
   v = ws.Range("B2:C5000")             ' or better: v = ws.Range("B2:C5000").Value2
 ' Check criteria looping over all rows (=first array dimension)
   For i = 1 To UBound(v)               ' data field arrays are one-based, i.e. they start with 1
       If v(i, 1) = vbNullString And v(i, 2) <> vbNullString Then v(i, 1) = "Correct"
   Next i
 ' Write edited array back to original range (adapt the range size to the array boundaries in both dimensions)
   ws.Range("B2").Resize(UBound(v, 1), UBound(v, 2)) = v
 End Sub

Further Notes

It's good use to fully qualify your sheet or range references (see e.g. object variable ws)
Each array item is identified by a row and a column index.
As such a datafield array is one based (start indices are 1), the first item in row 1 and column 1 will be referred by v(1,1), in col 2 by v(1,2).
In order to count the number of row items you check the upper boundary of its first dimension) via UBound(v,1) or even shorter via Ubound(v)
In order to count the number of columns you check the upper boundary of its second dimension) via UBound(v,2) (here the argument 2 is necessary!)
A comparation using vbNullString can be preferred in coding to "" as it takes less memory (c.f. @PEH 's answer) .

Loop with multiple Ranges

2 Answers2

Linked