Increment a cell value by 2

[Bals]

i am new vb in excel and need some assistance with the following macro. any help greatly appreciated. i need to create a macro which will take value from sheet 1 cell A1 value ,(example: CD-600500 is available in sheet 1 cell A1) then increment the value by 1 in sheet 2 cell range A5:A50,also A5:A50 if the cell is blank try goto next row and increment the value by 1.

 

[AlanSidman]

Attach a sample workbook showing a before and after scenario. Create only 8-10 records and mock up the solution you are looking for.

 

[Bals]

Hello Alan,
Here with i attached some example which i am looking for. Consider cell A1 value is my input, increment the value by 1 in sheet2 Range(A1:A16) Leave blank cells while incrementing.

 

[Bals]

Hello Alan,

I tried below but i want take a input value from Sheet1 cell A1 instead of i=900900 in code.

>>> use code - tags <<<

Sub increment()
Dim ws As Worksheet
Dim rng As Range
Dim i As Long
Dim cell As Range

'set worksheet:
Set ws = Worksheets("Sheet2")
'activate worksheet:
ws.Activate
    Set cellrange = Range("G8:G40").Cells.SpecialCells(xlCellTypeConstants)
    'Enter i value as dwg no suffix:
            i = 900900
For Each cell In cellrange
    If cell <> "" Then
    'Enter Prefix value in CD:
cell.Value = "CD-" & i
            i = i + 1
                         End If
           Next cell
           End Sub

 

[AlanSidman]

fixing your code

Option Explicit

Option Explicit

Sub increment()
    Dim ws As Worksheet
    Dim rng As Range
    Dim i As Variant
    Dim cell As Range
    Dim cellrange As Range

    'set worksheet:
    Set ws = Worksheets("Sheet2")
    'activate worksheet:
    ws.Activate
    Set cellrange = Range("G8:G40")
    'Enter i value as dwg no suffix:
    i = Right(Sheets("Sheet1").Range("A1"), 6)
    For Each cell In cellrange
        If cell = "" Then
            'Enter Prefix value in CD:
            cell.Value = "CD-" & i
            i = i + 1
        End If
    Next cell
End Sub

 

[Bals]

Dear Alan,

Its amazing! Thank you so much.