CODEWITHSUNDEEP
vbaexcel
qwww
qwww

Reputation: 1363

How to increment active cell in excel vba 4 steps?

I have a code which will enter data into a sheet. So once the data is entered, the form is reset and new data items can be entered. But this new data items should be gone to sheet in such a way that each time there is change in position. As in figure below, when i enter data using the form, it will get filled from B1 to D4. Now I require the cell to be selected for next entry is F1. ie, next set of data has to be filled from F1 to H4.

See Image

I tried ActiveCell.Offset(0, 4).Select but it is taking me to cell T2 then next time to U2 and all

Here is my code : 

Private Sub CommandButton1_Click()
Dim p As Long
Dim j As Integer
Dim erow As Long
Dim b As Integer
'erow = Sheet3.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
a = number
For p = 2 To number + 1

    Cells(p, 2) = Controls("txtBox" & p - 1).Text
    Cells(p, 3) = Controls("txtBox" & p + number - 1).Text
   Cells(p, 4) = Controls("txtBox" & p + number + number - 1).Text

Next p


'Cells(3, i) = TextBox1.Value
'Cells(4, i) = TextBox2.Value
'Cells(5, i) = TextBox3.Value
'Cells(3, i + 1) = TextBox4.Value
'Cells(3, i + 1) = TextBox4.Value

Call resetForm
ActiveCell.Offset(0, 4).Select

End Sub

Text boxes are dynamically created

My form looks like this :

enter image description here

What has to be modified?

Upvotes: 0

Views: 2006

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57683

I suggest something like the following:
It finds the last used column in row 2 and moves 2 right to get the first cell for the new data block. Note that for the first data block we need to move only one right.

This example adds some sample data to the next empty block:

Option Explicit

Private Sub CommandButton1_Click()
    Dim ws As Worksheet
    Set ws = Sheet3

    Dim LastCol As Long
    LastCol = ws.Cells(2, ws.Columns.Count).End(xlToLeft).Column 'get the last used column in row 2

    Dim FirstCellToWrite As Range
    If LastCol = 1 Then
        Set FirstCellToWrite = ws.Cells(2, LastCol + 1) 'first data block is different
    Else
        Set FirstCellToWrite = ws.Cells(2, LastCol + 2)
    End If

    'write example values
    FirstCellToWrite.Value = 1
    FirstCellToWrite.Offset(0, 1).Value = 2
    FirstCellToWrite.Offset(0, 2).Value = 3
    FirstCellToWrite.Offset(1, 0).Value = 4
    FirstCellToWrite.Offset(1, 1).Value = 5
    FirstCellToWrite.Offset(1, 2).Value = 6
    FirstCellToWrite.Offset(2, 0).Value = 7
    FirstCellToWrite.Offset(2, 1).Value = 8
    FirstCellToWrite.Offset(2, 2).Value = 9
End Sub

Upvotes: 1

Related Questions