unqualified cells() issue but for a single cell not a range when looping through a range

Question

I have some code which runs on a button press on a sheet to correct some date issues from data pulled from another sheet:

For c = 17 To ColumnCount
    test_date = WB.Sheets("Raw SC data").Cells(1, c).Value
    If Day(test_date) < 13 Then
        WB.Sheets("Raw SC data").Cells(1, c).Value = DateSerial(Year(test_date), Day(test_date), Month(test_date))
    Else
        WB.Sheets("Raw SC data").Cells(1, c).Value = CDate(test_date)
        'WB.Sheets("Raw SC data").Cells(1, c).Value = CDate(Cells(1, c).Value)
    End If
Next c

Calculate

The code ran fine when I initiated it on the sheet which contains the cells to be corrected however it didn't appear to run when I initiated from another sheet. Looking at the posts below I worked out that the issue was probably related to the cells() expression not being qualified.

Setting the Range object with Cells on a different Sheet

Why does Range work, but not Cells?

Excel VBA, getting range from an inactive sheet

I solved this by inserting a worksheets().select to bring the sheet into focus however I know that selecting things is a poor way to code:

WB.Sheets("Raw SC data").Select
For c = 17 To ColumnCount
    test_date = WB.Sheets("Raw SC data").Cells(1, c).Value
    If Day(test_date) < 13 Then
        WB.Sheets("Raw SC data").Cells(1, c).Value = DateSerial(Year(test_date), Day(test_date), Month(test_date))
    Else
        WB.Sheets("Raw SC data").Cells(1, c).Value = CDate(test_date)
        'WB.Sheets("Raw SC data").Cells(1, c).Value = CDate(Cells(1, c).Value)
    End If
Next c

Calculate

Call rigth_size_rows
WB.Sheets("SC - Plan Comparison").Select

All the referenced posts are for a range however each of my iterations is a cell so I thought that I'd correctly qualified it but obviously not. To help me be a better coder how should I have addressed this issue?

EDIT: This is more of the code, the previous element draws data from another sheet into an array:

Dim names_arr As Variant
Dim WB As Workbook
Set WB = ThisWorkbook

name_rows = WB.Sheets("MSP Raw").Range("a2").CurrentRegion.Rows.Count

For r = 2 To name_rows
    If r = 2 Then
        name_string = WB.Sheets("MSP Raw").Range("a" & r)
    Else
        name_string = WB.Sheets("MSP Raw").Range("a" & r) & "," & name_string
    End If
Next r

project_name = WB.Sheets("SC - Plan Comparison").Range("c4")

' store the row and column size
Dim RowCount, ColumnCount, data_cols As Long
RowCount = UBound(arr, 1)
ColumnCount = UBound(arr, 2)
Debug.Print RowCount, ColumnCount

' create the output array
Dim OutputArray As Variant
ReDim OutputArray(1 To RowCount, 1 To ColumnCount)
Dim i, j, CurrentRow As Long
Dim name, project As String

CurrentRow = 0

'read through the data and filter
For i = 1 To RowCount
    name = arr(i, 5)
    project = arr(i, 12)
    'check if the names match or if the project matches
    If i = 1 Or (name <> "" And (InStr(name_string, name) > 0 Or project = project_name)) Then
        CurrentRow = CurrentRow + 1
        For j = 1 To ColumnCount
            OutputArray(CurrentRow, j) = arr(i, j)
        Next j
    End If
    ' copy the row to the output array
Next i

' print the output array to the raw data sheet
WB.Sheets("Raw SC data").Range("a1").CurrentRegion.Clear
WB.Sheets("Raw SC data").Range("a1").Resize(CurrentRow, UBound(OutputArray, 2)).Value2 = OutputArray ' ISSUE - this is causing any date with a sub 12 day swapping the day to month, even though it is stored correctly in the array.

'data_cols = WB.Sheets("Raw SC data").Range("a1").CurrentRegion.Columns.Count

'reformat as everything has come over as text
With WB.Sheets("Raw SC data").Range("G2:G" & CurrentRow)    'Range("Q2:U" & CurrentRow
    .NumberFormat = "0"
    .Value = .Value
End With

Calculate
WB.Sheets("Raw SC data").Select
For c = 17 To ColumnCount
    test_date = WB.Sheets("Raw SC data").Cells(1, c).Value
    If Day(test_date) < 13 Then
        WB.Sheets("Raw SC data").Cells(1, c).Value = DateSerial(Year(test_date), Day(test_date), Month(test_date))
    Else
        WB.Sheets("Raw SC data").Cells(1, c).Value = CDate(test_date)
        'WB.Sheets("Raw SC data").Cells(1, c).Value = CDate(Cells(1, c).Value)
    End If
Next c

Calculate

If you step through the code with the debugger, were does the code not do what you expect? Where does `ColumnCount` come from? Does it contain the expected value? — leosch, Mar 16 '23 at 19:46
You have correctly qualified the cell 3 times. A problem might be the `WB` variable. Also, you could use a worksheet variable so you wouldn't have to write `WB.Sheets("Raw SC data")` 3 times e.g. with `Dim ws As Worksheet: Set ws = WB.Sheets("Raw SC data")` and then replace the remaining occurrences of `WB.Sheets("Raw SC data")` with `ws` making the code shorter, but more importantly, more readable. Even better would be to set the cell right below the `For...` line e.g. with `Dim cell As Range: Set cell = WB.Sheets("Raw SC data").Cells(1, c)` and follow up with the necessary substitutions. — VBasic2008, Mar 16 '23 at 20:13
Any event handlers on the sheet? Do any of the worksheet formulas call user-defined functions in VBA? — Tim Williams, Mar 16 '23 at 20:49
@VBasic2008, thank you for the suggestion to clean up my code with the WS variable. — Miles, Mar 20 '23 at 16:59

unqualified cells() issue but for a single cell not a range when looping through a range

0 Answers0