Snasui Excel Forum

Code: Select all

Private Sub CommandButton1_Click()
    Dim r As Long
    Dim i As Integer
    Dim iCount As Integer
    For i = 1 To 14
        r = Range("a" & Rows.Count).End(xlUp).Row + 1
        If Controls("Textbox" & iCount + 2) <> "" Or _
            Controls("TextBox" & iCount + 3) <> "" Or _
            Controls("TextBox" & iCount + 4) <> "" Or _
            Controls("TextBox" & iCount + 5) <> "" Then
            Range("a" & r) = TextBox1.Text
            Range("b" & r) = Controls("TextBox" & iCount + 2).Text
            Range("c" & r) = Controls("TextBox" & iCount + 3).Text
            Range("d" & r) = Controls("TextBox" & iCount + 4).Text
            Range("e" & r) = Controls("TextBox" & iCount + 5).Text
        End If
        iCount = iCount + 4
    Next i
 End Sub

Code: Select all

...
With Sheets("data3")
    For i = 1 To 14
        r = .Range("a" & Rows.Count).End(xlUp).Row + 1
        If Controls("Textbox" & iCount + 2) <> "" Or _
            Controls("TextBox" & iCount + 3) <> "" Or _
            Controls("TextBox" & iCount + 4) <> "" Or _
            Controls("TextBox" & iCount + 5) <> "" Then
            .Range("a" & r) = TextBox1.Text
            .Range("b" & r) = Controls("TextBox" & iCount + 2).Text
            .Range("c" & r) = Controls("TextBox" & iCount + 3).Text
            .Range("d" & r) = Controls("TextBox" & iCount + 4).Text
            .Range("e" & r) = Controls("TextBox" & iCount + 5).Text
        End If
        iCount = iCount + 4
    Next i
End With
...

Code: Select all

Private Sub CommandButton1_Click()
   Dim r As Long
    Dim i As Integer
    Dim iCount As Integer
        With Sheets("data3")
    For i = 1 To 14
          r = Range("a" & Rows.Count).End(xlUp).Row + 1
        If Controls("Textbox" & iCount + 2) <> "" Or _
            Controls("TextBox" & iCount + 3) <> "" Or _
            Controls("TextBox" & iCount + 4) <> "" Or _
            Controls("TextBox" & iCount + 5) <> "" Then
            Range("a" & r) = TextBox1.Text
            Range("b" & r) = Controls("TextBox" & iCount + 2).Text
            Range("c" & r) = Controls("TextBox" & iCount + 3).Text
            Range("d" & r) = Controls("TextBox" & iCount + 4).Text
            Range("e" & r) = Controls("TextBox" & iCount + 5).Text
        End If
        iCount = iCount + 4
    Next i
    End With
End Sub

Code: Select all

Sheets("Sheet1").Range("A1").Value = 10
Sheets("Sheet1").Range("B10").Value = 5
Sheets("Sheet1").Range("B3").Value = 2

Code: Select all

With Sheets("Sheet1")
   .Range("A1").Value = 10
   .Range("B10").Value = 5
   .Range("B3").Value = 2
End With