New to excel VBA : Need guidance

[xtinct]

Example1

Example2

after filtering for example, all of "PA" eg. "PA1-AA" "PA2-AA" "PA3-AA" using VBA, how do i compare their risk status and copy the highest risk status in Example1 to the respective Example2 Status colum?

Thanks in advance.

 

[xtinct]

hi, sorry.. but i dont get what u mean..

 

[jindon]

Did you test the code anyway?

 

[jindon]

try this one

Sub test()
Dim a, b, c(), myStatus, Status As String
Dim i As Long, ii As Long
myStatus = Array("Released","Risk-Low","Risk-Med","Risk-High","EOL")
a = Workbooks("example1.xls").Range("a1").CurrentRegion.Resize(,4).Value
b = Workbooks("example2.xls").Range("a1").CurrentRegion.Resize(,3).Value
ReDim c(1 To UBound(b,1),1 To 1)
For i = 1 To UBound(b,1)
   For ii = 1 To UBound(a,1)
      If InStr(1,a(ii,1),b(i,3),1) = 1 Then
         If a(ii,2) = "" Then
            Status1 = 0
         Else
            Status1 = Application.Match(a(ii,2),myStatus,0) + 1
         End If
         If a(ii,3) = "" Then
            Status2 = 0
         Else
            Status2 = Application.Match(a(ii,3),myStatus,0) + 1
         End If
         c(i,1) = Application.Max(a(i,1),Status1,Statsu2)
         Exit For
      End If
   Next
Next
For i = 1 To UBound(c,1)
   If c(i,1) = 0 Then
      c(i,1) = Empty
   Else
      c(i,1) = myStatus(c(i,1)-1)
   End If
Next
Workbooks("example2").Range("h1").Resize(UBound(c,1)).Value = c
End Sub

 

[agihcam]

xtinct -

Here is the code I have used to output example2.xls that I have posted.

Sub sample2()
Dim ws1 As Worksheet, ws2 As Worksheet, r As Range, r2 As Range, c As Range, ff As String, myStatus As String
Set ws1 = Workbooks("EXAMPLE1.xls").Sheets("Sheet1")
Set ws2 = Workbooks("EXAMPLE2.xls").Sheets("Sheet1")
For Each r In ws2.Range("b2", ws2.Range("b" & Rows.Count).End(xlUp))
        With ws1.Columns("a")
        Set c = .Find(r.Value & "*", , , xlPart)
        If Not c Is Nothing Then
            ff = c.Address
            Do
            If Trim(UCase(c.Offset(, 2).Value)) = "EOL" Then
                          myStatus = "EOL": Exit Do
                 ElseIf Trim(UCase(c.Offset(, 1).Value)) Like "*HIGH" And c.Offset(, 2).Value = "" Then
                          myStatus = "Risk-High"
                 ElseIf Trim(UCase(c.Offset(, 1).Value)) Like "*MED" And c.Offset(, 2).Value = "" Then
                          myStatus = "Risk-Med"
                 ElseIf Trim(UCase(c.Offset(, 1).Value)) Like "*LOW" And c.Offset(, 2).Value = "" Then
                          If myStatus <> "Risk-High" And myStatus <> "Risk-Med" And myStatus <> "EOL" Then
                              myStatus = "Risk-Low"
                          End If
                 End If
                 Set c = .FindNext(c)
           Loop Until ff = c.Address
           r.Offset(, 6).Value = myStatus: myStatus = Empty
      Else
           r.Offset(, 6).Value = "No Risk Status Found!"
      End If
        End With
Next
Set ws1 = Nothing
Set ws2 = Nothing
End Sub

 

[xtinct]

hi agihcam,

sorry was very busy for the past few days.. thanks alot for your help.. your code works exactly the way i want..

thanks to jindon also for all the help you have rendered to me.. :D