Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Check Empty Rows in Sheet2 when sheet1 buton clicked
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Check Empty Rows in Sheet2 when sheet1 buton clicked
Hi Bernard
Try something like this With Workbooks("Book1").Worksheets("Matter Arrising").Cells(2, 1) If Workbooks("Book1").Worksheets("ValidationLists").C ells(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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Check Empty Rows in Sheet2 when sheet1 buton clicked
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to have Sheet1 list Sheet2 rows matching text? | Excel Worksheet Functions | |||
copy data from sheet2 to sheet1 when sheet2 has variable # of rows | Excel Discussion (Misc queries) | |||
+/- in sheet2 based on conditions and dynamic rows in sheet1 | Excel Discussion (Misc queries) | |||
[=Sheet1!A1] - if i use Column A1 ('Sheet1') / A2 ('Sheet2') | Excel Worksheet Functions | |||
Display Rows From Sheet1 In Sheet2 (Import) | Excel Worksheet Functions |