CODEWITHSUNDEEP
excelvba
methuselah
methuselah

Reputation: 13206

Inserting form values into Excel spreadsheet using VBA

I'm trying to insert form values into my Excel spreadsheet using but my current code is inserting values into the same row.

  1. As my table starts from row 3, I want to start from there and continue by automatically shifting to the next row each time. The rows are already set and I don't want to insert new rows but overwrite the current 'empty' rows.
  2. When 202 rows (maximum no. of rows available) have been entered then I want the spreadsheet to return an error message dialog.

How can I go about achieving this?

Current Code

    Private Sub btnSubmit_Click()

    Dim ws As Worksheet
    Set ws = Worksheets("main")

    ' Copy the data to the database
    ws.Rows("4:4").Insert Shift:=xlDown
    ws.Range("A3").Value = cbo_deptCode.Value
    MsgBox ("Booking request has been successfully made")

End Sub

Upvotes: 1

Views: 9987

Answers (2)

brettdj
brettdj

Reputation: 55682

something like this

Private Sub btnSubmit_Click()
    Dim ws As Worksheet
    Dim rng1 As Range
    Set ws = Worksheets("main")
    Set rng1 = ws.Cells(Rows.Count, "a").End(xlUp)
    If rng1.Row > 202 Then
    MsgBox "202 Rows exceeded"  
    Else
    rng1.Offset(1, 0) = cbo_deptCode.Value
    End If
End Sub

Upvotes: 1

salih0vicX
salih0vicX

Reputation: 1373

Please try this and let us know if you have any questions or concerns:

Sub Button1_Click()
    Dim ws As Worksheet
    Dim i As Long
    Set ws = Worksheets("main")

    ' Copy the data to the database
    i = Cells(Rows.Count, 1).End(xlUp).Row + 1 'Get last empty cell in column A
    If i > 202 Then
        MsgBox "Row 203"
        Exit Sub
    End If

    Range("A" & i).Value = cbo_deptCode.Value
    MsgBox ("Booking request has been successfully made")
End Sub

Upvotes: 1

Related Questions