How to Check Empty Rows in Sheet2 when sheet1 buton clicked

[bernardng]

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

 

[Mike Fogleman]

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