Excel Forum

Code: Select all

Sub Button2_Click()
    Dim r As Long
        If Sheets("ใบเสนอราคา").Range("d29").Value <> "" Then
            MsgBox "ช่องบันทึกรายการสินค้าเต็ม"
            Exit Sub
        End If
    With Sheets("ใบเสนอราคา")
        For r = 1 To 1
        .Range("d13").End(xlUp).Offset(1, 0).Value = Sheets("form").Range("O10")
        .Range("b13").End(xlUp).Offset(1, 0).Value = Sheets("form").Range("a7")
        .Range("g13").End(xlUp).Offset(1, 0).Value = Sheets("form").Range("f7")
        .Range("h13").End(xlUp).Offset(1, 0).Value = Sheets("form").Range("p10")
        .Range("i13").End(xlUp).Offset(1, 0).Value = Sheets("form").Range("h7")
        Next r
        
        For i = 1 To .Range("B13", .Range("B" & .Rows.Count) _
            .End(xlUp).Offset(0, 0)).Rows.Count
                .Range("a13").Cells(i).Value = i
        Next i
        End With
End Sub

Code: Select all

Dim r As Long, lstRow As Long
If Sheets("ใบเสนอราคา").Range("d28").Value <> "" Then
    MsgBox "ช่องบันทึกรายการสินค้าเต็ม"
    Exit Sub
End If
With Sheets("ใบเสนอราคา")
    lstRow = .Range("d" & .Rows.Count).End(xlUp).Offset(1, 0).Row
    .Range("d" & lstRow).Value = Sheets("form").Range("O10")
    .Range("b" & lstRow).Value = Sheets("form").Range("a7")
    .Range("g" & lstRow).Value = Sheets("form").Range("f7")
    .Range("h" & lstRow).Value = Sheets("form").Range("p10")
    .Range("i" & lstRow).Value = Sheets("form").Range("h7")

    For i = 1 To .Range("B13", .Range("B" & .Rows.Count) _
        .End(xlUp).Offset(0, 0)).Rows.Count
            .Range("a13").Cells(i).Value = i
    Next i
End With

Code: Select all

    lstRow = .Range("b" & .Rows.Count).End(xlUp).Offset(1, 0).Row