Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
putting one name under another on a cell | Excel Discussion (Misc queries) | |||
start a macro or procedure based on user putting an x in a cell | Excel Discussion (Misc queries) | |||
SAP BW Report - Putting the Unit of Measure or Currency in another Cell based on Format Cell | Excel Programming | |||
Putting Formula in Cell | Excel Programming | |||
Putting text in a cell | Excel Programming |