EXCEL TOOLS
Excel Add-ins ที่พัฒนาโดยคุณสันติพงศ์ ณสุย (MVP Excel 2010-2020) ด้วยภาษา C# เพื่อแก้ไขปัญหาไฟล์ใหญ่ คำนวณนาน ทำงานช้า จัดการข้อมูลต่าง ๆ ที่ทำงานประจำวันได้อย่างสะดวกรวดเร็ว สนใจคลิกไปดูได้ที่นี่ครับ => Excel Tools
Excel Add-ins ที่พัฒนาโดยคุณสันติพงศ์ ณสุย (MVP Excel 2010-2020) ด้วยภาษา C# เพื่อแก้ไขปัญหาไฟล์ใหญ่ คำนวณนาน ทำงานช้า จัดการข้อมูลต่าง ๆ ที่ทำงานประจำวันได้อย่างสะดวกรวดเร็ว สนใจคลิกไปดูได้ที่นี่ครับ => Excel Tools
[code]
และปิดด้วย [/code]
ตัวอย่างเช่น [code]dim r as range[/code]
เพื่อให้แตกต่างจากข้อความทั่วไป สะดวกในการอ่านและทดสอบ (คลิกเพื่อดูตัวอย่าง)Code: Select all
Private Sub CommandButton6_Click()
'On Error Resume Next
Dim id As String
Dim rowselect As String
If TextBox7.Text = "" Then
MsgBox ("Please select data")
Else
id = TextBox7.Text
rowselect = WorksheetFunction.CountIf(Worksheets("¡ÒÃàºÔ¡").Range("A2:A10000"), 0)
rowselect = WorksheetFunction.Match(id, Sheet9.Range("A2:A10000"), 0)
Rows(rowselect).Select
Rows(rowselect).EntireRow.Delete
If MsgBox("Are you sure want to delete to data?", vbYesNo) = vbYes Then
Unload Me
End If
End If
Unload Me
Requisition.Show
End Sub
rowselect = WorksheetFunction.CountIf(Worksheets("¡ÒÃàºÔ¡").Range("A2:A10000"), 0)
เปลี่ยนเป็นrowselect = WorksheetFunction.CountIf(Worksheets("¡ÒÃàºÔ¡").Range("A2:A10000"), id)
Code: Select all
If rowselect = 0 then
exit sub
else
' rowselect = worksheetfunction.match(...)
end if
'Other code
Code: Select all
Private Sub CommandButton6_Click()
'On Error Resume Next
If TextBox7.Text = "" Then
MsgBox ("Please select data")
Exit Sub
Else
Dim id As String
Dim rowselect As String
id = TextBox7.Text
rowselect = WorksheetFunction.CountIf(Worksheets("การเบิก").Range("A1:A10000"), id)
If rowselect = 0 Then
Exit Sub
Else
rowselect = WorksheetFunction.Match(id, Sheet9.Range("A1:A10000"), id)
Rows(rowselect).Select
Rows(rowselect).EntireRow.Delete
End If
If MsgBox("Are you sure want to delete to data?", vbYesNo) = vbYes Then
Else
UserForm1.Show
Unload Me
End If
End If
Unload Me
UserForm1.Show
End Sub
Code: Select all
rowselect = WorksheetFunction.Match(id, Sheet9.Range("A1:A10000"), id)
Code: Select all
If MsgBox("Are you sure want to delete to data?", vbYesNo) = vbYes Then
Else
UserForm1.Show
Unload Me
End If
rowselect = WorksheetFunction.Match(id, Sheet9.Range("A1:A10000"), 0)
Code: Select all
Private Sub CommandButton6_Click()
'On Error Resume Next
If TextBox7.Text = "" Then
MsgBox ("Please select data")
Exit Sub
Else
Dim id As String
Dim rowselect As String
If id = TextBox7.Text Then
Exit Sub
Else
rowselect = WorksheetFunction.CountIf(Worksheets("การเบิก").Range("A1:A10000"), id)
End If
If rowselect = 0 Then
Exit Sub
Else
rowselect = WorksheetFunction.Match(id, Sheet9.Range("A1:A10000"), 0)
Rows(rowselect).Select
Rows(rowselect).EntireRow.Delete
End If
If MsgBox("Are you sure want to delete to data?", vbYesNo) = vbYes Then
Unload Me
End If
End If
Unload Me
Requisition.Show
End Sub
Code: Select all
'Other code
Dim id As String
Dim rowselect As Long
If TextBox7.Text = "" Then
MsgBox ("Please select data")
Exit Sub
Else
id = Me.TextBox7.Text
If WorksheetFunction.CountI(Sheet9.Range("A1:A10000"), id) > 0 Then
rowselect = WorksheetFunction.Match(CLng(id), Sheet9.Range("A1:A10000"), 0)
Else
Exit Sub
End If
Rows(rowselect).EntireRow.Delete
'Other code
End If
'Other code
Code: Select all
Private Sub CommandButton6_Click()
'On Error Resume Next
Dim id As String
Dim rowselect As Long
If TextBox7.Text = "" Then
MsgBox ("Please select data")
Exit Sub
Else
id = Me.TextBox7.Text
If WorksheetFunction.CountIf(Sheet9.Range("A1:A10000"), id) > 0 Then
rowselect = WorksheetFunction.Match(CLng(id), Sheet9.Range("A1:A10000"), 0)
Else
Exit Sub
End If
Rows(rowselect).EntireRow.Delete
rowselect = WorksheetFunction.CountIf(Worksheets("¡ÒÃàºÔ¡").Range("A1:A10000"), id)
If rowselect = 0 Then
Exit Sub
Else
rowselect = WorksheetFunction.Match(id, Sheet9.Range("A1:A10000"), 0)
Rows(rowselect).Select
Rows(rowselect).EntireRow.Delete
End If
If MsgBox("Are you sure want to delete to data?", vbYesNo) = vbYes Then
Unload Me
UserForm1.Show
End If
End If
End Sub
Application.Calculation = xlCalculationManual
'Other code ...
Application.Calculation = xlCalculationAutomatic
Code: Select all
Private Sub btsearch_Click()
'On Error Resume Next
If ComboBox1 = "" Or ComboBox2 = "" Then
MsgBox "Please fill the complete data"
Exit Sub
End If
Dim found As Boolean
Dim txt As String
Dim r As Range
Dim nRow As String
Sheet4.Activate
For Each r In Sheet4.Columns(1).SpecialCells(xlCellTypeConstants)
'If Right(r.Value, 3) = Right(ComboBox1.Text, 3) And
If ComboBox2.Text = Application.Text(r.Offset(0, 1).Value, ComboBox2.Text) Then
nRow = r.Row
found = True
Exit For
End If
Next r
If found Then
txt = Cells(nRow, 6)
TextBox1.Text = txt
Exit Sub
Else
MsgBox "Data not found !"
End If
'Sheet1.Activate
End Sub
Code: Select all
'Other code
For Each r In Sheet4.Columns(2).SpecialCells(xlCellTypeConstants)
'If Right(r.Value, 3) = Right(ComboBox1.Text, 3) And
' If ComboBox2.Text = Application.Text(r.Offset(0, 1).Value, ComboBox2.Text) Then
If (r.Value = ComboBox2.Text And r.Offset(0, -1).Value = ComboBox1.Text) Or _
r.Value = ComboBox2.Text And r.Offset(0, -1).End(xlUp).Value = ComboBox1.Text Then
' r.Value = comb
nRow = r.Row
found = True
Exit For
End If
Next r
'Other code