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

 

[Christian]

Hi Bernard

Try something like this

With Workbooks("Book1").Worksheets("Matter Arrising").Cells(2, 1)
If Workbooks("Book1").Worksheets("ValidationLists").Cells(2, 1) = ""
Then
ActiveCell.Offset(0, 4).Copy
Destination:=Workbooks("Book1").Worksheets("Matter
Arrising").Range("A2")
Else
MsgBox ("The paste cell is not empty")
End If
End With

This is checking the paste cell if it is empty and then copying the
value
You have to change the Workbook name.

Cheers
Christian

 

[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