Code to place value in another cell
 

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.


--
chris46521
------------------------------------------------------------------------
chris46521's Profile: http://www.excelforum.com/member.php...o&userid=35909
View this thread: http://www.excelforum.com/showthread...hreadid=565243

Code to place value in another cell
 

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

--
Regards,
Tom Ogilvy


"chris46521" wrote:


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.


--
chris46521
------------------------------------------------------------------------
chris46521's Profile: http://www.excelforum.com/member.php...o&userid=35909
View this thread: http://www.excelforum.com/showthread...hreadid=565243

Code to place value in another cell
 

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!

Tom Ogilvy Wrote:
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

--
Regards,
Tom Ogilvy


"chris46521" wrote:


I would like to create code that will check to see if there is
certain
value in a particular cell, and if there is place a "1" in anothe
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 an
help
or suggestions.


--
chris46521

------------------------------------------------------------------------
chris46521's Profile
http://www.excelforum.com/member.php...o&userid=35909
View this thread
http://www.excelforum.com/showthread...hreadid=565243



--
chris4652
-----------------------------------------------------------------------
chris46521's Profile: http://www.excelforum.com/member.php...fo&userid=3590
View this thread: http://www.excelforum.com/showthread.php?threadid=56524

Code to place value in another cell
 

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.

--
Regards,
Tom Ogilvy



"chris46521" wrote:


Thanks for your reply. I would like to write code that checks to see if
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 fit
my worksheet, but when I enter an "O" in the N column, no "1" is place
in cell AS (which I is range 45) and it seems that it goes through an
endless loop as the hour glass comes up and I have to break the
program. Do you know what might be going on? Thanks!

Tom Ogilvy Wrote:
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

--
Regards,
Tom Ogilvy


"chris46521" wrote:


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.


--
chris46521

------------------------------------------------------------------------
chris46521's Profile:
http://www.excelforum.com/member.php...o&userid=35909
View this thread:
http://www.excelforum.com/showthread...hreadid=565243




--
chris46521
------------------------------------------------------------------------
chris46521's Profile: http://www.excelforum.com/member.php...o&userid=35909
View this thread: http://www.excelforum.com/showthread...hreadid=565243

Code to place value in another cell
 

Thanks a lot Tom! Your code worked perfectly!

Tom Ogilvy Wrote:
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.

--
Regards,
Tom Ogilvy



"chris46521" wrote:


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

Tom Ogilvy Wrote:
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 th
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

--
Regards,
Tom Ogilvy


"chris46521" wrote:


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" i
another
cell
in that row. I think I know how to do the If statement, but
don't
know how to write a value in another cell in VBA. Thanks for any
help
or suggestions.


--
chris46521


------------------------------------------------------------------------
chris46521's Profile:
http://www.excelforum.com/member.php...o&userid=35909
View this thread:
http://www.excelforum.com/showthread...hreadid=565243




--
chris46521

------------------------------------------------------------------------
chris46521's Profile
http://www.excelforum.com/member.php...o&userid=35909
View this thread
http://www.excelforum.com/showthread...hreadid=565243



--
chris4652
-----------------------------------------------------------------------
chris46521's Profile: http://www.excelforum.com/member.php...fo&userid=3590
View this thread: http://www.excelforum.com/showthread.php?threadid=56524