Changing Value using vba

[Greg B]

Here is a copy of the code i want to use and adapt but I am a little stuck.
Dim rng As Range, cell As Range, col As Long
Dim rw As Long
col = 6
rw = 1
With Worksheets("STOCKLIST")
Set rng = .Range(.Cells(2, col), .Cells(Rows.Count, col).End(xlUp))
End With
For Each cell In rng
If UCase(cell.Value) = "YES" Then
cell.EntireRow.Copy Destination:=Worksheets("interM") _
.Cells(rw, 1)
rw = rw + 1
End If

I would like to have the macro look for the matching code which is "YES"
situated anywhere in column F, when it finds it I would like it to add 1
value to the number in column e on the same row? I mean this

alpha a 0 YES
to
alpha a 1 YES
Sorry about the bad example.

Thank you
Greg B

 

[Jacob Skaria]

Sub ClearColumns()
Dim rng As Range, cell As Range, col As Long
Dim rw As Long
col = 6
rw = 1
With Worksheets("STOCKLIST")
Set rng = .Range(.Cells(2, col), .Cells(Rows.Count, col).End(xlUp))
End With
For Each cell In rng
If UCase(cell.Value) = "YES" Then
cell.EntireRow.Copy Destination:=Worksheets("interM") _
.Cells(rw, 1)
Worksheets("STOCKLIST").Range("E" & cell.Row) = _
Worksheets("STOCKLIST").Range("E" & cell.Row) + 1
rw = rw + 1
End If
Next
End Sub


If this post helps click Yes

 

[Don Guillett]

Maybe
sub addonetocoleifcolfisyes()
for each c in rng
if ucase(c)="YES" Then c.offset(,-1)= _
c.offset(,-1)+1
next

 

[Greg B]

Thank you Jacob it works perfectly
Greg B

Sub ClearColumns()
Dim rng As Range, cell As Range, col As Long
Dim rw As Long
col = 6
rw = 1
With Worksheets("STOCKLIST")
Set rng = .Range(.Cells(2, col), .Cells(Rows.Count, col).End(xlUp))
End With
For Each cell In rng
If UCase(cell.Value) = "YES" Then
cell.EntireRow.Copy Destination:=Worksheets("interM") _
.Cells(rw, 1)
Worksheets("STOCKLIST").Range("E" & cell.Row) = _
Worksheets("STOCKLIST").Range("E" & cell.Row) + 1
rw = rw + 1
End If
Next
End Sub


If this post helps click Yes