Help with find and replacing a text into another column

[daryldsouza]

Hello Guys, I am new to the forum and need your help in creating a macro which will check for a string in Colum1 and as per search criteria make an entry on colum2.

Lets say Colum A has the following entries.
//
CPU utilization exceeds threshold
Memory utilization exceeds threshold
Application errors
Hardware Error
//

I want to classify each entry to one Type of problem, which should display on the next column, there are many more entries and hence wish to automate this.
//
CPU utilization exceeds threshold >> CPU
Memory utilization exceeds threshold >> Memory
Application errors >> Application
Hardware Error >> Hardware
//

Thanks a lot in advance.

[arlu1201]

Is it always the first word that is taken as the classification?

[daryldsouza]

Hi Arlette,

Its not the first word everytime, apologies I should have mentioned this on my first. There are alsa statements like - 'Java process using more than 4% of CPU'. Can we get an output by defining a search in the text.

Lets say for all the errors I have 10 types already defined, the words already included in the text.

[arlu1201]

It will be good if you attach a sample file showing the list that you have with the words. And also the statements which need to be reviewed to identify the words.

To Attach a File:

1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

[daryldsouza]

Sorry for the delay, please find attached a sample file listing the issues and the type.

[ChesterTate]

This will only do it if we can define the issue by a single word. You'll note IssueType(1) corresponds to IssueText(1) and so on.

Sub checktype()

Dim IssueType(1 To 9) As String
IssueType(1) = "CPU"
IssueType(2) = "Batch"
IssueType(3) = "Batch Jobs"
IssueType(4) = "Disk"
IssueType(5) = "Memory"
IssueType(6) = "Oracle"
IssueType(7) = "Batch Jobs"
IssueType(8) = "Batch Jobs"
IssueType(9) = "Batch Jobs"

Dim IssueText(1 To 9) As String
IssueText(1) = "CPU"
IssueText(2) = "OSS"
IssueText(3) = "PMS"
IssueText(4) = "Mount"
IssueText(5) = "Memory"
IssueText(6) = "trend"
IssueText(7) = "CBCM"
IssueText(8) = "REPLIES"
IssueText(9) = "RECONNECTION"

LastRow = ActiveSheet.UsedRange.Rows.Count

For X = 1 To 9
For Y = 2 To LastRow
Data = Worksheets("Sheet1").Cells(Y, 1).Value
test = InStr(1, Data, IssueText(X))
If test > 0 Then
Worksheets("Sheet1").Cells(Y, 2).Value = IssueType(X)
End If
Next Y
Next X
End Sub

[jindon]

Try this

Sub test()
    Dim r As Range, temp As String
    With CreateObject("VBScript.RegExp")
        .IgnoreCase = True
        .Pattern = "^(CPU|Memory|trend.log|Mount)\b"
        For Each r In Range("a2", Range("a" & Rows.Count).End(xlUp))
            If r.Value <> "" Then
                If .test(r.Value) Then
                    temp = .Execute(r.Value)(0)
                    If UCase(temp) = "TREND.LOG" Then
                        temp = "Oracle"
                    ElseIf UCase(temp) = "MOUNT" Then
                        temp = "Disk"
                    End If
                Else
                    temp = "Batch jobs"
                End If
            End If
            r(, 2).Value = temp
            temp = Empty
        Next
    End With
End Sub

[daryldsouza]

Awesome you guys. Thanks so much !!

[daryldsouza]

Hi Jindon, Can you please help with what could be the problem in this code, the test only returns the same vaule and not 'Oracle'. If it checks 'Ora_Fault' it should return Oracle but returns Ora_Fault. Thanks !!!

With CreateObject("VBScript.RegExp")
        .IgnoreCase = False
        .Pattern = "^(Ora_Fault|Oracle)\b"
        For Each o In Range("a2", Range("a" & Rows.Count).End(xlUp))
            If o.Value <> "" Then
                If .test(o.Value) Then
                    temp0 = .Execute(o.Value)(0)
                    If UCase(temp0) = "Oracle" Then
                        temp0 = "Oracle"
                    ElseIf UCase(temp0) = "Ora_Fault" Then
                        temp0 = "Oracle"
                    End If
                Else
                   temp0 = Empty
        End If
                    o(, 5).Value = temp0
            End If
        Next
        End With

[jindon]

1) You must edit your post and wrap the code with the code tag.
It is a MUST rule here.
e.g
[code]
Your code
[/code]

2) try change

If .test(o.Value) Then
    temp0 = .Execute(o.Value)(0)
    If UCase(temp0) = "Oracle" Then
        temp0 = "Oracle"
    ElseIf UCase(temp0) = "Ora_Fault" Then
        temp0 = "Oracle"
    End If
Else
    temp0 = Empty
End If
o(, 5).Value = temp0

to

If .test(o.Value) Then
    temp0 = "Oracle"
Else
    temp0 = Empty
End if
o(, 5).Value = temp0