Code to place value in another cell

[chris46521]

I would like to create code that will check to see if there is a certain
value in a particular cell, and if there is place a "1" in another cell
in that row. I think I know how to do the If statement, but I don't
know how to write a value in another cell in VBA. Thanks for any help
or suggestions.

 

[Tom Ogilvy]

since you offer no particulars, here is some contrived code

the 1 is placed in column F for a value in column A of 3 for the 1st through
10th row


for each cell in Range("A1:A10")
if cell.Value = 3 then
cell.offset(0,5).Value = 1
else
' clear column F if not?
'cell.offset(0,5).clearcontents
end if
next

 

[chris46521]

Thanks for your reply. I would like to write code that checks to see i
there is an "O" in column N for any row and if there is to place a "1
in column AS for that row. I tried modifying the code you posted to fi
my worksheet, but when I enter an "O" in the N column, no "1" is plac
in cell AS (which I is range 45) and it seems that it goes through a
endless loop as the hour glass comes up and I have to break th
program. Do you know what might be going on? Thanks!

since you offer no particulars, here is some contrived code

the 1 is placed in column F for a value in column A of 3 for the 1s
through
10th row


for each cell in Range("A1:A10")
if cell.Value = 3 then
cell.offset(0,5).Value = 1
else
' clear column F if not?
'cell.offset(0,5).clearcontents
end if
next

 

[Tom Ogilvy]

Do you want to run this one time manually - or do you want to use the change
event to update column AS whenever an entry is made in N. It sounds like the
latter.

for the change event

Private Sub Worksheet_Change(ByVal Target As Range)
if Target.count > 1 then exit sub
if Target.column <> 14 then exit sub
if lcase(Target.Value) = "o" then
On Error Resume Next
Application.enableEvents = False
cells(Target.row,"AS").value = 1
Application.EnableEvents = True
On Error goto 0
end if
End sub

Right click on the sheet tab and select view code. Put in code like the
above.

As to your current problem, I can't see your code, so I don't know what you
are doing. If you are looping over all the cells in column N, then yes, this
can take a very long time.

 

[chris46521]

Thanks a lot Tom! Your code worked perfectly!

Do you want to run this one time manually - or do you want to use th
change
event to update column AS whenever an entry is made in N. It sound
like the
latter.

for the change event

Private Sub Worksheet_Change(ByVal Target As Range)
if Target.count > 1 then exit sub
if Target.column <> 14 then exit sub
if lcase(Target.Value) = "o" then
On Error Resume Next
Application.enableEvents = False
cells(Target.row,"AS").value = 1
Application.EnableEvents = True
On Error goto 0
end if
End sub

Right click on the sheet tab and select view code. Put in code lik
the
above.

As to your current problem, I can't see your code, so I don't know wha
you
are doing. If you are looping over all the cells in column N, the
yes, this
can take a very long time.