value in offest cell to match

**short_n_curly** · 05-23-2010, 05:55 AM

hi does anybody know of any code that will check the value in range b17, match it to an cell in the array b3:p3 and then in the cell diectly under the matching cell place the value 1

any help would be much appreciated thanks

**pike** · 05-23-2010, 06:57 AM

Hi short_n_curly,
try...

Please Login or Register  to view this content.

**Alf** · 05-23-2010, 11:43 AM

Or like this perhaps

Option Explicit

Sub testval()
'
Dim cell As Range

For Each cell In Range("B3:P3")

If cell.Value = Range("B17").Value Then
cell.Offset(1, 0).Value = 1
End If
Next cell

End Sub

**foxguy** · 05-23-2010, 03:20 PM

Or don't use code. just put formulas into range b4:p4

Please Login or Register  to view this content.

and copy it into c4:p4
Then you don't have to run the macro again, when $B$17 changes, Excel will do it for you.

**short_n_curly** · 05-23-2010, 04:02 PM

thanks the vba codes are both great and do what i want, as for the formula, its not an option as i will be clearing the cells often hence the need for vba, thanks anyway