Increment row change by two
 

I'm trying to get the code to increment by two every time it drops dow
a row. But all I can get it to do is increment on the same row
Basically I want it to add a number to the value "Bulk" for each cel
on that row. Then drop down one row and do it again but increment th
number by two. The result would be like:

BULK112 BULK112 BULK112
BULK114 BULK114 BULK114
Thanks

Dim arr()
ReDim Preserve arr(0)

i = 112
For Each CELL In Range("K10:DB50")
i = i + 2
If CELL.Value = "BULK" Then
ReDim Preserve arr(UBound(arr) + 1)
arr(UBound(arr)) = i

CELL.Value = "BU" & CStr(i)
If i 136 Then i = 112
End If
Next CELL

i = 100
For Each CELL In Range("K10:DB20")
If CELL.Value = "Pack" Then
i = i + 1
found = 0
On Error Resume Next
found = WorksheetFunction.Match(i, arr, 0)
If found Then i = i + 1

CELL.Value = "Pack" & CStr(i)

If i 145 Then i = 100
End If
Next CELL
End Su

--
Message posted from http://www.ExcelForum.com

Increment row change by two
 

If you start the data in two rows. (K10:DB11) by dragging, you could use the
code below

Sub FillRangeAndIncrement()
Range("K10:DB11").AutoFill Destination:=Range("K10:DB50"),
Type:=xlFillDefault
End Sub


--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
HIS


"hotherps " wrote in message
...
I'm trying to get the code to increment by two every time it drops down
a row. But all I can get it to do is increment on the same row.
Basically I want it to add a number to the value "Bulk" for each cell
on that row. Then drop down one row and do it again but increment the
number by two. The result would be like:

BULK112 BULK112 BULK112
BULK114 BULK114 BULK114
Thanks

Dim arr()
ReDim Preserve arr(0)

i = 112
For Each CELL In Range("K10:DB50")
i = i + 2
If CELL.Value = "BULK" Then
ReDim Preserve arr(UBound(arr) + 1)
arr(UBound(arr)) = i

CELL.Value = "BU" & CStr(i)
If i 136 Then i = 112
End If
Next CELL

i = 100
For Each CELL In Range("K10:DB20")
If CELL.Value = "Pack" Then
i = i + 1
found = 0
On Error Resume Next
found = WorksheetFunction.Match(i, arr, 0)
If found Then i = i + 1

CELL.Value = "Pack" & CStr(i)

If i 145 Then i = 100
End If
Next CELL
End Sub


---
Message posted from http://www.ExcelForum.com/