Reference cell.offset from another sheet-CodePudding

I just started using VBA so Im stuck.

I am having it check if there is a unit number, check if there is a checkmark present, and if the value is less than 8. Then returning the value. This is what I have so far and it works great, but I need to change it to pull the value from another sheet. (Rather than a hidden (-8 offset) column on the active sheet)

'Make sure no blank unit numbers

    If cell.Offset(0, -7).Value <> "" Then
    
        If cell.Offset(0, -2).Value = ChrW(&H2713) And cell.Offset(0, -8).Value < 8 Then
            cell.Value = cell.Offset(0, -8).Value
        End If

There's more but this is the part I need to change. I need the "cell.offset(0, -8).value <8" to instead reference a cell on another sheet (say...Sheet5 A13 for example) and to to return that value if true in the active cell (offset 0,0).

BUT....this code repeats through a range of cells on the active sheet! It starts at I47 and goes through I119, checking for true in every row and returning the corresponding value.

So I am at a loss. Any help is appreciated!

CodePudding user response：

You can refer to a cell in any sheet using Worksheets("sheet name").cells(... syntax.

There are other ways like declaring a variable to hold a reference to that sheet and using it in several places, or addressing the other sheet by its number, etc, but start with this, and research other ways afterwards

CodePudding user response：

I think you want this ...

If cell.Offset(0, -7).Value <> "" Then
    If cell.Offset(0, -2).Value = ChrW(&H2713) And ThisWorkbook.Worksheets("Other Worksheet Name").Range(cell.Offset(0, -8).Address).Value < 8 Then
        cell.Value = cell.Offset(0, -8).Value
    End If
End If

Use the address of the current cell with offset on the other sheet.