Try this:
Private Sub CommandButton1_Click()
Dim value, acCell As String
Dim FoundRange As Range
Dim chkEmpty As Integer
'Dim aCell As String
value = Sheets("ValidationLists").Range("A1").value
On Error Resume Next
Set FoundRange = Range("A18:F37").Find(What:=value, _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, _
SearchDirection:=xlNext).Activate
acCell = ActiveCell.Address
Range(ActiveCell, ActiveCell.Offset(0, 4)).Copy
'Sheets("Matter Arrising").Activate : Problem1
'Sheets("Matter Arrising").Range("A2").Select
'aCell = Sheets("Matter Arrising").Range("A2:H2")
'MsgBox "Range are " & aCell
chkEmpty = WorksheetFunction.CountA(Sheets("Matter
Arrising").Range("A2:H2"))
MsgBox "1.) There are " & chkEmpty & " of Records"
If chkEmpty = 0 Then
MsgBox ("No Record")
Else
MsgBox (chkEmpty & " Records Found")
End If
End Sub
Mike F
"bernardng" wrote in
message ...
Code:
--------------------
Private Sub CommandButton1_Click()
Dim value, acCell As String
Dim FoundRange As Range
Dim chkEmpty As Integer
Dim aCell As String
value = Sheets("ValidationLists").Range("A1").value
On Error Resume Next
Set FoundRange = Range("A18:F37").Find(What:=value, _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, _
SearchDirection:=xlNext).Activate
acCell = ActiveCell.Address
Range(ActiveCell, ActiveCell.Offset(0, 4)).Copy
'Sheets("Matter Arrising").activate : Problem1
Sheets("Matter Arrising").Range("A2").selct
aCell = Sheets("Matter Arrising").Range(ActiveCell, ActiveCell.Offset(0,
7)).Address
MsgBox "Range are " & aCell
chkEmpty = Sheets("Matter Arrising").Application.CountA(Sheets("Matter
Arrising").Range(aCell))
MsgBox "1.) There are " & chkEmpty & " of Records"
If chkEmpty = 0 Then
MsgBox "No Record"
Else
MsgBox "Records Found"
End If
End Sub
--------------------
In My Sheet 1 I have one Button1 to click, then copy after the find
value and paste to sheet2 or Sheets("Matter Arrising"). Before paste to
Sheet2, I have used check empty rows to check whether the rows is empty
or not.
Problem1 :If Sheets("Matter Arrising") is not selected, my checking
emtpy rows is not work. I need user only click the button on sheet1
without going see any sheet2.
Is there any way to check empty rows in Sheet 2 when button1 is clicked
in sheet1?
Rgds
Bernard Ng
--
bernardng
------------------------------------------------------------------------
bernardng's Profile:
http://www.excelforum.com/member.php...o&userid=34117
View this thread: http://www.excelforum.com/showthread...hreadid=544212