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.

 

[agihcam]

xtinct -

I did a little modifications from Jindon's code based on your posted linked data. EXAMPLE1A & EXAMPLE1B.

try this codes and post back if it didn't get what you want.

Jindon -

let me know if i have missed something.

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("EXAMPLE1A.xls").Sheets("Sheet1")
Set ws2 = Workbooks("EXAMPLE1B.xls").Sheets("Sheet1")
For Each r In ws1.Range("a2", ws1.Range("a" & Rows.Count).End(xlUp))
    For Each r2 In ws2.Range("b2", ws2.Range("b" & Rows.Count).End(xlUp))
        Set c = ws2.Columns("a").Find(r.Value, , , xlPart)
        If Not c Is Nothing Then
            ff = c.Address
            Do
                 If Trim(UCase(c.Offset(, 1).Value)) Like "*HIGH" Then
                          myStatus = "Risk-High": Exit Do
                 ElseIf Trim(UCase(c.Offset(, 1).Value)) Like "*MED" Then
                          myStatus = "Risk-Med"
                 ElseIf Trim(UCase(c.Offset(, 1).Value)) Like "*LOW" Then
                          If myStatus <> "Risk-High" And myStatus <> "Risk-Med" Then
                              myStatus = "Risk-Low"
                          End If
                 End If
                 Set c = ws2.Columns("a").FindNext(c)
           Loop Until ff = c.Address
           r.Offset(, 1).Value = myStatus: myStatus = Empty
      Else
           r.Offset(, 1).Value = "No Risk Status Found!"
      End If
        Next
Next
Set ws1 = Nothing
Set ws2 = Nothing
End Sub

 

[xtinct]

hi, thanks for your help.. i tried your code but after the

If Not c Is Nothing Then

it skips the Do loop and went straight to

Else 
           r.Offset(, 1).Value = "No Risk Status Found!" 
      End If

 

[jindon]

try

Sub sample()
Dim ws1 As Worksheet, ws2 As Worksheet, r 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))
      Set c = ws1.Columns("a").Find(r.Value,,,xlPart)
      If Not c Is Nothing Then
           ff = c.Address : MsgBox c.Address
           Do
                 If Trim(UCase(c.Offset(,1).Value)) Like "*HIGH" Then              
                          myStatus = "Risk-High" : Exit Do
                 ElseIf Trim(UCase(c.Offset(,1).Value)) Like "*MED" Then
                          myStatus = "Risk-Med"
                 ElseIf Trim(UCase(c.Offset(,1).Value)) Like "*LOW" Then
                          If myStatus ="" Then myStatus = "Risk-Low"
                 End If
                 Set c = ws1.columns("a").FindNext(c)
           Loop Until ff = c.Address
           r.Offset(,6).Value = myStatus : myStatus = Empty
      Else
           MsgBox r.Value & " is not exist"
      End If
Next
Set ws1 = Nothing
Set ws2 = Nothing
End Sub

 

[xtinct]

hi thanks jindon.. the codes are working already.. thanks alot to you :D

 

[xtinct]

can you explain to me what does this line of code mean?

Set c = ws1.Columns("a").Find(r.Value,,,xlPart)

 

[jindon]

Code is looping though column("b") of ws2, finding each cell value in
Column("a") of ws1 with partial match.

Therefore, if Cells is found then c is set to Found Cell, else c = Nothing

Does this explain to you?

 

[xtinct]

ok.. so the 3 ,,, have got no special meanings to it?

 

[jindon]

ok.. so the 3 ,,, have got no special meanings to it?

Ah, you wanted to you the meaning of ,,,

refer vba help for Find method for details

briefly:
Find method has settings such as
What:= you must set
After:=
LookIn:=
LookAt:=
MatchCase:=
etc

and those with current setting value, we can omit by putting ","

That line says

Set c = ws1.Columns("a").Find(What:=r.Value,LookAt = xlPart)

 

[xtinct]

ic.. thanks for explaining

 

[xtinct]

hi, really sorry to bother you again.. my boss has got some more requirements for the program.. now i have to compare between items of 2 columns which are "Risk Status" columns and the "EOL Status" column.

"Risk Status" column will have Risk-High, Risk-Med, Risk-Low & Released
"EOL Status" column will have EOL

the order of priority will be :
1. EOL
2. Risk-High
3. Risk-Med
4. Risk-Low
5. Released

if both columns are empty, then the Example2 "Status" shall be empty.

btw, is it possible to have a msgbox or generate a report for those "Product Name" in Example2 that don't have any match in "Product Full Code" in Example1?

Thanks alot.