CODEWITHSUNDEEP
excelvba
Nitin
Nitin

Reputation: 25

Comparison and assign value via click button in VBA

I want to assign a value in column like serial number via click button. I already a make one code but its without button. I want to add button also.

Option Base 1
Function Check()
    Dim i As Integer
    Dim startCell As Range
    Dim firstNonEmptyCell As Range

    Set startCell = Range("G2")

    If VBA.IsEmpty(startCell.Value) Then
        MsgBox "No data in this column"
    Else
        Range("A2") = 1
        Range("A2").Select
        Selection.DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, _
        Step:=1, Stop:=400, Trend:=False
    End If
End Function

Upvotes: 1

Views: 2224

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149287

  1. Insert a Form button via Insert in the Developer Toolbar.

    enter image description here

  2. Paste the below code in a module and then right click on the button and click on assign Macro.

  3. Select Button1_Click in the Assign Macro dialog box and you are done.

Code

Option Explicit

Sub Button1_Click()
    Dim ws As Worksheet
    Dim LRow As Long, i As Long

    '~~> Change this to the relevant worsheet
    Set ws = ThisWorkbook.Sheets("Sheet1")

    With ws
        '~~> Find Last Row in Col G which has data
        LRow = .Range("G" & .Rows.Count).End(xlUp).Row

        If LRow = 1 Then
            MsgBox "No data in column G"
        Else
            For i = 2 To LRow
                .Range("A" & i).Value = i - 1
            Next i
        End If
    End With
End Sub

Upvotes: 1

Related Questions