Snasui Excel Forum

Code: Select all

Dim r As Range
Dim i As Integer

Code: Select all

Set r = Sheets("Form").Range("E1")
With Sheets("Database")
    i = Application.Match(r, .Range("C:C"), 0)
    If .Range("D" & i) Then
        Exit Sub
    End If

Code: Select all

Sub BeenArL()                           ' ปุ่มบันทึกรับชำระ ชีท Form
    Dim rSource As Range
    Dim rTarget As Range
    Dim rs As Range
    Dim rt As Range
    Dim i As Double
    Dim r As Range
    Set r = Sheets("Form").Range("E1")
    With Sheets("Database")
    i = Application.Match(r, .Range("C:C"), 0)
        Exit Sub
    End If
    End With
    With Sheets("Form")
        Set rSource = .Range("B3:B47")
    End With
    With Sheets("Database")
        Set rTarget = .Range("D2", .Range("D" & Rows.Count).End(xlUp))
    End With
    With ActiveSheet
     i = (.Range("L4") + .Range("L6"))
    If i <> .Range("J8") Then
        MsgBox "โปรดตรวจจำนวนเงินและบันทึกใหม่"
        Exit Sub
    End If
    End With
    Application.Calculation = xlCalculationManual
    For Each rs In rSource
        For Each rt In rTarget
            If rt = rs Then rt.Offset(0, 25) = "Y"
        Next rt
    Next rs
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = False
    Sheets("TemBilling").Range("A12:O12").Copy
    Sheets("AR").Range("A" & Rows.Count).End(xlUp) _
        .Offset(1, 0).PasteSpecial xlPasteValues
    Sheets("TemBilling").Range("P12:W12").Copy
    Sheets("Report").Range("A" & Rows.Count).End(xlUp) _
        .Offset(1, 0).PasteSpecial xlPasteValues
    Sheets("Form").Range("H1,J2,I4:L4,L6,G4").ClearContents
    With Sheets("Form")
            .Range("J6") = .Range("J6") + 1
        End With
        Application.ScreenUpdating = True
End Sub

Code: Select all

    With Sheets("Form")
        Set rSource = .Range("G1")
    End With
    With Sheets("Database")
        Set rTarget = .Range("F2", .Range("F" & Rows.Count).End(xlUp))
    End With
    With Sheets("Form")
        Set rSource = .Range("E1")
    End With
    With Sheets("Database")
        Set rTarget = .Range("C2", .Range("F" & Rows.Count).End(xlUp))
    End With
    With Sheets("Form")
     i = (.Range("A3") > .Range("A2"))
    End With
    With Sheets("Database")
        Set rTarget = .Range("E2", .Range("E" & Rows.Count).End(xlUp))
    End With

Code: Select all

if r = 1 and r.offset(0,1) = 2 and r.offset(0,2) = 3 then
next r

Code: Select all

dim rall as range, r as range
set rall = range("a1:a10")
for r in rall
if r = 1 and r.offset(0,1) = 2 and r.offset(0,2) = 3 then
next r

Code: Select all

With Worksheets("Form") .Range("E1")
    With Worksheets("Database")
    Set rall = .Range("C2:C13")

Code: Select all

With ActiveSheet
    i = (.Range("D1") - .Range("B1")) * 1 + 1
    If i < 0 Then
        MsgBox "Please check date."
        Exit Sub
    End If
    End With
    With Sheets("Form")
        Set rSource = .Range("E1")
    End With
    With Sheets("Database")
    Set rall = .Range("C2")
    End With
    With Sheets("Form")
        Set rSource = .Range("G1")
    End With
    With Sheets("Database")
    Set rall = .Range("F2")
    End With