Snasui Excel Forum

Code: Select all

Private Sub CommandButton1_Click()
    With Sheet2
        .Cells(2, 1) = "ลำดับที่"
        .Cells(2, 2) = "เลขที่สมาชิก"
        .Cells(2, 3) = "ชื่อ - สกุล"
        .Cells(2, 4) = "จำนวนเงิน"
        .Range("A2", "D2").HorizontalAlignment = xlCenter
        .Range("A2", "D2").Font.Bold = True
        r = 3
        l = 1
        For i = 2 To Sheet1.Range("A" & Rows.Count).End(xlUp).Row
            If IsNumeric(Sheet1.Cells(i, 3)) And Trim(Sheet1.Cells(i, 3)) _
                <> "Transaction Number" Then
                .Cells(r, 1) = l
                .Cells(r, 2) = Trim(Sheet1.Cells(i, 1))
                .Cells(r, 3) = Trim(Sheet1.Cells(i, 2))
                .Cells(r, 4) = Trim(Sheet1.Cells(i, 3))
                r = r + 1
                l = l + 1
            End If
        Next
    End With
End Sub

Code: Select all

Private Sub CommandButton6_Click()
If Range("K10") = "" Then Exit Sub
i = WorksheetFunction.CountA(Worksheets("form1").Columns("b:b")) + 3
Worksheets("form1").Cells(i, 2).Value = Range("K3").Value
Worksheets("form1").Cells(i, 3).Value = Range("K7").Value
Worksheets("form1").Cells(i, 4).Value = Range("K8").Value
Worksheets("form1").Cells(i, 5).Value = Range("K9").Value
Worksheets("form1").Cells(i, 6).Value = Range("K10").Value
    If Range("A4") = "" Then
        Worksheets("form1").Cells(i, 1).Value = 1
        Worksheets("form1").Cells(i, 7).Value = 1
    Else
        Worksheets("form1").Cells(i, 1).Value = Range("A3").End(xlDown) + 1
        Worksheets("form1").Cells(i, 7).Value = Range("G3").End(xlDown) + 1
    End If
End Sub

Code: Select all

Private Sub CommandButton4_Click()
If Range("K1") = "" Then Exit Sub
If Worksheets("data").Columns("a:d").Find(Range("K1"), LookIn:=xlValues) Is Nothing Then
MsgBox "äÁèÁÕàÅ¢•Õè¹Õé!"
Else
i = Worksheets("data").Columns("a:d").Find(Range("K1"), LookIn:=xlValues).Row
Worksheets("data").Range("A" & i).Resize(1, 7).Copy
Worksheets("form1").Range("K3").PasteSpecial xlPasteValues, Transpose:=True
Application.CutCopyMode = False
End If
End Sub

Code: Select all

Private Sub CommandButton4_Click()
    Dim i As Integer
    If Sheets("data").Range("I2") = "" Then Call Module1.Macro1
    i = Sheets("data").Range("I2")
    If Range("K1") = "" Then Exit Sub
    Worksheets("data").Range("A" & i).Resize(1, 7).Copy
    Worksheets("form1").Range("K3").PasteSpecial xlPasteValues, Transpose:=True
    Application.CutCopyMode = False
    Sheets("data").Range("I2").Delete shift:=xlUp
End Sub

Code: Select all

Sub Macro1()
    Dim rAll As Range, r As Range
    Dim i As Integer
    Sheets("data").Range("I:I").ClearContents
    With Sheets("data")
     Set rAll = .Range("A2", .Range("D" & Rows.Count).End(xlUp))
    End With
    For Each r In rAll
        If InStr(r, Sheets("form1").Range("K1")) > 0 Then
            If Sheets("data").Range("I" & Rows.Count).End(xlUp) <> r.Row Then
                Sheets("data").Range("I" & Rows.Count).End(xlUp).Offset(1, 0) = r.Row
            End If
        End If
    Next r
End Sub