Sub Find_First()
Dim FindString As String
Dim Rng As Range
Dim FistAddress As String
Dim LastAddress As String
Dim Result As Range
Dim ws As Worksheet
Dim firstAdd As String
' Xóa dong trong trong sheet NET
Sheets("NET").Select
Range("A4").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.EntireRow.Delete
'Tim gia tri dau tien
FindString = InputBox("Can Tim Kiem Cai Gi:", "Tra Cuu")
If Trim(FindString) <> "" Then
With Sheets("Sheet1").Range("C:D")
Set Rng = .Find(What:=FindString, _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
firstAdd = Rng.Address
If Not Rng Is Nothing Then
Application.Goto Rng, True
Cells(Rng.Row, 13).Value = 1
FistAddress = Rng.Row
Else
MsgBox "Nothing found"
End If
End With
'Tim gia tri cuoi cung
With Sheets("Sheet1").Range("C:D")
Do
Set Rng = .Find(What:=FindString, _
After:=.Cells(1), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False)
If Not Rng Is Nothing Then
'Rng = Cells(LastAddress, 3)
'Set Rng = .FindNext(Rng)
Application.Goto Rng, True
Cells(Rng.Row, 13).Value = 2
LastAddress = Rng.Row
'MsgBox Cells(Rng.Row, 11)
'MsgBox Cells(Rng.Row, 3)
Set Rng = Cells(LastAddress, 3)
Set Rng = .FindNext(Rng)
FindString = Cells(LastAddress, 3)
Else
MsgBox "Nothing found"
End If
Loop While firstAdd <> Rng.Address And Cells(Rng.Row, 11) > 600
'Copy sang Sheet NET
Set Result = Range(Cells(FistAddress, 1), Cells(LastAddress, 12))
Result.Select
Selection.Copy Destination:=Sheets("NET").Range("A4")
Sheet23.Activate
End With
End If
End SubSub sosanh()
Dim i As Integer
Dim a, b As Integer
Sheets("1").Select
a = Application.WorksheetFunction.RandBetween(1, 3)
b = Application.WorksheetFunction.RandBetween(1, 3)
Do
Calculate
Loop until (a = b)
MsgBox "gia tri a va b la: " & a &" "& b
End Sub
Viết cái gì không hiểu luôn. Ngắt ý ở chỗ nào trong câu trên?
a = Application.WorksheetFunction.RandBetween(1, 3)
b = Application.WorksheetFunction.RandBetween(1, 3)
Do
Calculate
Loop until (a = b)Vầy thử xem:
Sub Test()
Dim a As Long, b As Long
Randomize
Do
a = Int(Rnd() * 3) + 1
b = Int(Rnd() * 3) + 1
Loop Until (a = b)
MsgBox "gia tri a va b la: " & a & " " & b
End Sub
Mới học vba nên bị ngộ nhận. a và b trong trường hợp này chỉ được tính duy nhất một lần, Chạy cái này thì xác xuất treo máy là rất cao.
1/ Cho code vào thẻ chèn code. Tìm đọc ở mục 4 màu đỏ ở link
- Bạn chèn đoạn code sau vào cái nút Submit Form.
Private Sub btnSubmit_Click()
Dim cbName As Variant
Dim rFind As Range
cbName = UserForm1.cmbName.Value
If cbName = "" Then
MsgBox "Ban chu nhap Ten vao", vbCritical, "Chu Y"
ElseIf cbName <> "" Then
Set rFind = Sheets("Sheet2").Range("D2:D1000").Find(cbName, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=True)
If Not rFind Is Nothing Then
txtAddress = rFind.Offset(0, 1).Value
txtPhone = rFind.Offset(0, 2).Value
txtZipcode = rFind.Offset(0, 3).Value
Else
txtAddress = ""
txtPhone = ""
txtZipcode = ""
MsgBox "Not Found"
End If
End If
End Sub
1000"). Nếu không tìm thấy Name trong Sheet2.Range("D21000") thì user có thể Add mới thông tin cần nhập vào qua nút Submit Form.1000) hay không.
Sheets("Sheet1").Range("A1:Z71")
