Excel VBA If cell.value = .. then Range(selection).offset().value issue

Question

I am getting an error on the code below:

For Each cell In Range("H2", Selection.End(xlDown).End(xlDown).End(xlUp)).SpecialCells(xlCellTypeVisible)
If cell.Value = Range(Sheets("Mapping").Cells(4, x)).Value Then
Range(Selection).Offset(0, -7).Value = "=IF(LEFT(RC9,8)=Mapping!R8C1,""YES"",""NO"")"
Else
End If

This is part of a larger code where I am trying to have each visible cell in Column H compared to a cell on the mapping sheet and if correct then the if statement will be added into column A.

I am having a similar issue when attempting to use cell.value in another if statement where I include an AND statement, see below. Same type of FOR statement for Column I in this case. Comparing each value in column I to two different cells on the mapping sheet and if correct the changing column A's value to YES. Both lines break on the 3rd line and the second code breaks on the second line as well.

For Each cell In Range("I2", Selection.End(xlDown).End(xlDown).End(xlUp)).SpecialCells(xlCellTypeVisible)
If cell.Value = Range(Sheets("Mapping").Cells(4, x)).Value And cell.Value = Range(Sheets("Mapping").Cells(9, 1)).Value Then
Range(Selection).Offset(0, -7).Value = "YES"
Else
'do nothing

Any help would be greatly appreciated. Thanks.

Add error message and code that does the selection beforehand. You should be debugging your selection range and cell value to make sure you have what you think you have — dbmitch, Aug 18 '16 at 18:02
I don't know if it matters or not, but when I need to enter a formula in to a range, I don't use `.Value` but rather `.Formula`. so `Range(Selection).Offset(0, -7).Formula = "=IF(LEFT(RC9,8)=Mapping!R8C1,""YES"",""NO"")"` but I would hope this isn't the only thing needed to fix it — PartyHatPanda, Aug 18 '16 at 18:04
`Selection` being a `Range` object, `Range(Selection)` simply doesn't feel right. That said you should [avoid Select and Activate](http://stackoverflow.com/a/10717999/1188513) in your code (i.e. working with `Selection` shouldn't be a go-to way to do things), and work off object references instead. Note that an unqualified `Range` implicitly refers to the active worksheet, and unqualified `Sheets` implicitly refers to the active workbook (and you want to use the `Worksheets` collection instead, which only contains `Worksheet` objects - which isn't necessarily true for the `Sheets` collection). — Mathieu Guindon, Aug 18 '16 at 18:15
Which cell is "selected" when you are running your code?? If it is, for instance, cell D4 then you are looping across all cells in D2:H297 (row "297" made up as an example of what the .End(xlDown).End(xlDown).End(xlUp) from D4 might return). If cell E22 has the same value `Sheets("Mapping").Cells(4, x).Value` then your 3rd line will attempt to access a column 7 columns to the left of column E. Obviously that is going to fail. — YowE3K, Aug 18 '16 at 20:20
As for the `If cell.Value = Range(Sheets("Mapping").Cells(4, x)).Value And cell.Value = Range(Sheets("Mapping").Cells(9, 1)).Value Then` line, if `Sheets("Mapping").Cells(4, x).Value` is not equal to `Sheets("Mapping").Cells(9, 1).Value` you can optimise your code by not even searching through the range of cells, because no cell will match both of those values if those values don't match each other. And if they do match each other, there is no point comparing cell.value to **both** of them, you may as well just test against one of them. — YowE3K, Aug 18 '16 at 20:24
@YowE3K correct me if I'm wrong but the cell that is "selected" should be based off of the FOR statement. For each cell in the range of H2:H297 as per your example. If that is not the case then what can I replace Range(selected).offset... with to correct the current issue. — bick77, Aug 18 '16 at 20:56
The `Selection` in `Selection.End(xlDown).End(xlDown).End(xlUp)` refers to the currently selected cell, not to the cells that are returned by the `Range` that that code is generating. So, if the currently selected cell was "D4", then your `Range("H2", Selection.End(xlDown).End(xlDown).End(xlUp)).SpecialCells(xlCellTypeVisible)` is equivalent to `Range("H2", [D4].End(xlDown).End(xlDown).End(xlUp)).SpecialCells(xlCellTypeVisible)` which might be the equivalent of `Range("H2", "D297")` which would be equivalent to `Range("D2:H297")`. — YowE3K, Aug 18 '16 at 21:20

score 1 · Accepted Answer · answered Aug 18 '16 at 21:15

I think you are trying to do this:

For Each cell In Range("H2:H" & Range("H" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeVisible)
    If cell.Value = Sheets("Mapping").Cells(4, x).Value Then
        cell.Offset(0, -7).FormulaR1C1 = "=IF(LEFT(RC9,8)=Mapping!R8C1,""YES"",""NO"")"
    End If
Next

I'm not sure what the variable x contains - I am just assuming that your code is meant to be checking the value in row 4, column x, of sheet "Mapping". And if you are expecting the 4 to be cell.Row, or something, then you will need to make the necessary changes.

Your other block of code is probably meant to be something like:

If Sheets("Mapping").Cells(4, x).Value <> Sheets("Mapping").Cells(9, 1).Value Then
    'No processing required if Mapping![x]4 isn't the same as Mapping!A9
Else
    For Each cell In Range("I2:I" & Range("I" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeVisible)
        If cell.Value = Sheets("Mapping").Cells(4, x).Value Then
            cell.Offset(0, -7).Value = "YES"
        End If
    Next
End If

Excel VBA If cell.value = .. then Range(selection).offset().value issue

1 Answers1