Changing Value using vba
 

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

Changing Value using vba
 

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
---------------
Jacob Skaria


"Greg B" wrote:

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

Changing Value using vba
 

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Greg B" wrote in message
...
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

Changing Value using vba
 

This is how I would do it...

Sub IncrementYesses()
Dim C As Range
Dim FirstAddress As String
With Worksheets("STOCKLIST").Columns("F")
Set C = .Find("Yes", LookAt:=xlWhole, MatchCase:=False)
If Not C Is Nothing Then
FirstAddress = C.Address
Do
C.Offset(, -1).Value = C.Offset(, -1).Value + 1
Set C = .FindNext(C)
Loop While Not C Is Nothing And C.Address < FirstAddress
End If
End With
End Sub

--
Rick (MVP - Excel)


"Greg B" wrote in message
...
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

Changing Value using vba
 

Thank you Jacob it works perfectly
Greg B

"Jacob Skaria" wrote in message
...
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
---------------
Jacob Skaria


"Greg B" wrote:

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