Putting a value in cell based on another cell
I'm trying to set a Y or N in cell Ax (based on the value in Counter)
depending on the value in cell Hx. I wrote this using the VB help, but it errors out on "Set curCell" Sub AddDeleteModify() For Counter = 2 To 200 Set curCell = Worksheets("Sheet1").Cells("H" & Counter) Set myCell = Worksheets("Sheet1").Cells("A" & Counter) If curCell Is Nothing Then Exit Sub If curCell = "New" Then myCell = "Y" If curCell < "New" Then myCell = "N" Next Counter End Sub Eventually, I want to add code that will start the counter over again, check Hx and put a Y or N in Bx is depeding on whether Hx is "Delete" Then restart the counter 1 last time and check Hx for "Modify" or "Re-Instate ID" and put a Y in Cx if either of those values are present, and an N if not. Any help would be appreciated. Also, is there a way to simply use the current worksheet, as I'm not sure that the name of the worksheet is always going to be Sheet1? Thank you very much for taking the time to read this. Best regards, Dee |
Putting a value in cell based on another cell
You can do this in a single loop. Try the below macro which works on the
activesheet Sub AddDeleteModify() For counter = 2 To 200 Range("A" & counter & ":C" & counter) = "N" Select Case Range("H" & counter) Case "New" Range("A" & counter) = "Y" Case "Delete" Range("B" & counter) = "Y" Case "Modify", "Re-Instate ID" Range("C" & counter) = "Y" End Select Next counter End Sub If this post helps click Yes --------------- Jacob Skaria "Dee Sperling" wrote: I'm trying to set a Y or N in cell Ax (based on the value in Counter) depending on the value in cell Hx. I wrote this using the VB help, but it errors out on "Set curCell" Sub AddDeleteModify() For Counter = 2 To 200 Set curCell = Worksheets("Sheet1").Cells("H" & Counter) Set myCell = Worksheets("Sheet1").Cells("A" & Counter) If curCell Is Nothing Then Exit Sub If curCell = "New" Then myCell = "Y" If curCell < "New" Then myCell = "N" Next Counter End Sub Eventually, I want to add code that will start the counter over again, check Hx and put a Y or N in Bx is depeding on whether Hx is "Delete" Then restart the counter 1 last time and check Hx for "Modify" or "Re-Instate ID" and put a Y in Cx if either of those values are present, and an N if not. Any help would be appreciated. Also, is there a way to simply use the current worksheet, as I'm not sure that the name of the worksheet is always going to be Sheet1? Thank you very much for taking the time to read this. Best regards, Dee |
Putting a value in cell based on another cell
Hi,
Try this Sub AddDeleteModify() For Counter = 2 To 200 If ActiveSheet.Range("H" & Counter) = "New" Then ActiveSheet.Range("A" & Counter).Value = "Y" Else ActiveSheet.Range("A" & Counter).Value = "N" End If Next End Sub Mike "Dee Sperling" wrote: I'm trying to set a Y or N in cell Ax (based on the value in Counter) depending on the value in cell Hx. I wrote this using the VB help, but it errors out on "Set curCell" Sub AddDeleteModify() For Counter = 2 To 200 Set curCell = Worksheets("Sheet1").Cells("H" & Counter) Set myCell = Worksheets("Sheet1").Cells("A" & Counter) If curCell Is Nothing Then Exit Sub If curCell = "New" Then myCell = "Y" If curCell < "New" Then myCell = "N" Next Counter End Sub Eventually, I want to add code that will start the counter over again, check Hx and put a Y or N in Bx is depeding on whether Hx is "Delete" Then restart the counter 1 last time and check Hx for "Modify" or "Re-Instate ID" and put a Y in Cx if either of those values are present, and an N if not. Any help would be appreciated. Also, is there a way to simply use the current worksheet, as I'm not sure that the name of the worksheet is always going to be Sheet1? Thank you very much for taking the time to read this. Best regards, Dee |
Putting a value in cell based on another cell
Works great! Thanks so much!
"Mike H" wrote: Hi, Try this Sub AddDeleteModify() For Counter = 2 To 200 If ActiveSheet.Range("H" & Counter) = "New" Then ActiveSheet.Range("A" & Counter).Value = "Y" Else ActiveSheet.Range("A" & Counter).Value = "N" End If Next End Sub Mike "Dee Sperling" wrote: I'm trying to set a Y or N in cell Ax (based on the value in Counter) depending on the value in cell Hx. I wrote this using the VB help, but it errors out on "Set curCell" Sub AddDeleteModify() For Counter = 2 To 200 Set curCell = Worksheets("Sheet1").Cells("H" & Counter) Set myCell = Worksheets("Sheet1").Cells("A" & Counter) If curCell Is Nothing Then Exit Sub If curCell = "New" Then myCell = "Y" If curCell < "New" Then myCell = "N" Next Counter End Sub Eventually, I want to add code that will start the counter over again, check Hx and put a Y or N in Bx is depeding on whether Hx is "Delete" Then restart the counter 1 last time and check Hx for "Modify" or "Re-Instate ID" and put a Y in Cx if either of those values are present, and an N if not. Any help would be appreciated. Also, is there a way to simply use the current worksheet, as I'm not sure that the name of the worksheet is always going to be Sheet1? Thank you very much for taking the time to read this. Best regards, Dee |
Putting a value in cell based on another cell
Works great! Thanks so much!
"Jacob Skaria" wrote: You can do this in a single loop. Try the below macro which works on the activesheet Sub AddDeleteModify() For counter = 2 To 200 Range("A" & counter & ":C" & counter) = "N" Select Case Range("H" & counter) Case "New" Range("A" & counter) = "Y" Case "Delete" Range("B" & counter) = "Y" Case "Modify", "Re-Instate ID" Range("C" & counter) = "Y" End Select Next counter End Sub If this post helps click Yes --------------- Jacob Skaria "Dee Sperling" wrote: I'm trying to set a Y or N in cell Ax (based on the value in Counter) depending on the value in cell Hx. I wrote this using the VB help, but it errors out on "Set curCell" Sub AddDeleteModify() For Counter = 2 To 200 Set curCell = Worksheets("Sheet1").Cells("H" & Counter) Set myCell = Worksheets("Sheet1").Cells("A" & Counter) If curCell Is Nothing Then Exit Sub If curCell = "New" Then myCell = "Y" If curCell < "New" Then myCell = "N" Next Counter End Sub Eventually, I want to add code that will start the counter over again, check Hx and put a Y or N in Bx is depeding on whether Hx is "Delete" Then restart the counter 1 last time and check Hx for "Modify" or "Re-Instate ID" and put a Y in Cx if either of those values are present, and an N if not. Any help would be appreciated. Also, is there a way to simply use the current worksheet, as I'm not sure that the name of the worksheet is always going to be Sheet1? Thank you very much for taking the time to read this. Best regards, Dee |
All times are GMT +1. The time now is 03:00 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com