Set cell value by using .Offset and .Find

**mmogharreban** · 05-10-2013, 05:38 PM

I am trying to set a cell value = to "X" by finding one cell value (= to value in cell D7 on sheet1) on another sheet (= to value in cell B7 on sheet1) and entering the "X" two columns to the left of the found value. I am using the code below. Can anyone advise what I am doing wrong here?

Thanks

Mike

   Dim rng2 As Range
        Set rng2 = Sheets(Sheet1.Range("B7").Value).Range("C:C").Find(Range("D8").Value, , xlValues, xlWhole)
        rng2.Offset(-2, 0).Value = "X"

**p24leclerc** · 05-10-2013, 09:11 PM

try the following macro:

Public Sub test()
Dim rng2 As Range
Sheets("Sheet2").Select
With Range("C2", Cells(Rows.Count, 3).End(xlUp))
  Set rng2 = .Find(Sheets("Sheet1").Range("D7"), , xlValues, xlWhole)
End With
If Not rng2 Is Nothing Then
  rng2.Offset(0, -2).Value = "X"
End If
End Sub

**mmogharreban** · 05-13-2013, 12:04 PM

Many thanks Pierre. Unfortunately I am still not getting the desired result. The Macro is running with no error message, just not making the change...
Here is what I am using

Dim rng2 As Range

Sheets(Sheet1.Range("B7").Value).Select
With Range("C2", Cells(Rows.Count, 3).End(xlUp))
  Set rng2 = .Find(Sheet1.Range("D7"), , xlValues, xlWhole)
End With
If Not rng2 Is Nothing Then
  rng2.Offset(0, -2).Value = "X"
End If