Putting a value in cell based on another cell

[Dee Sperling]

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

 

[Jacob Skaria]

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

 

[Mike H]

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]

Works great! Thanks so much!

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]

Works great! Thanks so much!

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