Hi all again,

I need help about this situation:
In alarm.xls, there are 2 sheets. First sheet called "Sheet1" and contains 500 lines of messages in the first column. Second sheet called "Sheet2" contain only a button in cell A1 called "Generate."

I want to copy all messages contains "Alarm" from first column of "Sheet1" into the first column of "Sheet2" by using this "Generate" button.

Please help me????


Regards,
Mona

Mona:

Try this...make sure you have declared Option Compare Text in order to make things not case sensitive. Assign "Generate" as the macro to your button.


Option Explicit
Option Compare Text
Sub Generate()
    Dim objSheet1 As Worksheet
    Dim objSheet2 As Worksheet

    Dim lngMaxRow As Long

    Dim i As Integer
    Dim j As Integer
    Dim intPos As Integer
'***
'* . set up worksheet objects
'***
    Set objSheet1 = ThisWorkbook.Worksheets("Sheet1")
    Set objSheet2 = ThisWorkbook.Worksheets("Sheet2")
'***
'* . clear all cells beginning in row 2, column 1 on
'* destination sheet (if there is data
'***
    With objSheet2
        lngMaxRow = .Rows.Count
        lngMaxRow = .Cells(lngMaxRow, 1).End(xlUp).Row
        If lngMaxRow >= 2 Then
            .Range(objSheet2.Cells(2, 1), objSheet2.Cells(lngMaxRow, 1)).Clear
        End If
    End With
'***
'* . determine maximum row in column 1 of source sheet
'* . check each cell to see if it contains "alarm" and copy it
'* . start copy in row 2
'***
    With objSheet1
        lngMaxRow = .Rows.Count
        lngMaxRow = .Cells(lngMaxRow, 1).End(xlUp).Row
        j = 1
        For i = 1 To lngMaxRow
            intPos = InStr(1, .Cells(i, 1).Value, "alarm")
            If intPos > 0 Then
                j = j + 1
                objSheet2.Cells(j, 1).Value = .Cells(i, 1).Value
            End If
        Next i
    End With
'***
'* . display count of matches
'***
    MsgBox (j - 1 & " Messages generated as 'alarm'...")

End Sub

Thanks LBAKER,

That coding really helps me... It runs smoothly. I will go though the coding right now to make sure i understand the process of the "Generate" button.

Thank you so much for your instant reply :D

Cheers!!

Regards,
Mona

Mona:

You are welcome. FYI, I use the .end(xlUp) method a lot. It basically simulates accessing the last row in the worksheet and striking the Ctrl "up arrow" key to find the last row on the worksheet that contains data. You might also want to check out the .end(xlDown) to get first row with data, .end(xlToLeft) to get last column with data, .end(xlToRight) to get first column with data.

Because the maximum number of rows/columns on a sheet are different between Excel versions, the .rows.count and columns.count will guarantee success in any version.

Lenora