Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Changing the range for averages with out changing the formula. | Excel Worksheet Functions | |||
Changing case and also changing entered data | Excel Programming | |||
Changing background colour when changing data in a cell | Excel Discussion (Misc queries) | |||
Autofill 1 column with changing data and changing range | Excel Programming | |||
Changing footers on all worksheets without changing print set up | Excel Discussion (Misc queries) |