VBA: Copying cell value in variable range

Question

In the following code, I'm having the hardest time identifying a specific cell in the variable range "rngCell". In the "If" statement, I would like to copy a specific cell in that column or row that the rngCell (the active cell is at) instead of the value of rngCell. I've tried using offset but have been failing. Example: If rngCell is at e42, I may need a value from e2 or a42.

Thank you.

Dim rngCell As Range
Dim lngLstRow As Long
Dim ws As Worksheet, resultsWS As Worksheet

lngLstRow = ws.UsedRange.Rows.Count
Worksheets("FileShares").Select

j = 4
p = 1
q = 4
g = 6

Dim k&                      
For k = 9 To 50
With ws
    For Each rngCell In .Range(.Cells(8, k), .Cells(lngLstRow, k))

        For i = LBound(maxKeywords) To UBound(maxKeywords)
            If rngCell.Value = maxKeywords(i) And rngCell.Interior.ColorIndex = 3 Then
                resultsWS.Cells(g, 2).Offset(j + p, 0) = rngCell.Value

            g = g + 1

            j = q + p - 5 'Used to start at row 8 and every row after
            End If
        Next i
    Next rngCell

End With
Next k

The code you're showing us will blow up with run-time error 91, since ws is never assigned. There's also no need whatsoever to .Select that FileShares sheet. — Mathieu Guindon
I'm also quite flabbergasted by Dim k&, given you're not using type hints anywhere. Why not give it an explicit, named type like everything else? — Mathieu Guindon
@Mat'sMug I didn't post the whole code. Just posted what was relevant. I'll keep in mind in the future not to use type hints, since they're strongly discouraged. Thanks for the suggestion. — Vince

Tim Williams Tim Williams · Accepted Answer · 2017-04-26T19:57:27

If rngCell is E42 then:

rngCell.EntireRow.Cells(1)    '>>A42
rngCell.EntireColumn.Cells(2) '>>E2

or

ws.Cells(rngCell.Row, 1)      '>>A42
ws.Cells(2, rngCell.Column)   '>>E2

VBA: Copying cell value in variable range

1 Answers