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]

when i run the code, the error occurs at

End Select

it says

Compile error:
End Select without Select Case

 

[jindon]

Oh, I've just missed out one End If (Previous Code edited)

 

[xtinct]

i ran the code but my Example2 still remained unchanged.. nothing is updated or changed.. what is wrong?

 

[jindon]

Do you want to copy the code again ( i've just added the Msg
Box)
and come back if you get the Msg or Not?

 

[xtinct]

ran the code but no msgbox appear and Example2 is still the same..

 

[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 UCase(c.Offset(,1).Value) Like "*HIGH" Then              
                          myStatus = "Risk-High" : Exit Do
                 ElseIf UCase(c.Offset(,1).Value) Like "*Med" Then
                          myStatus = "Risk-Med"
                 ElseIf 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 = 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]

ok now it seems ok already.. but Example2's PRD336 is empty.. should have been Risk-Med

 

[jindon]

OOps
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]

extra space? what do u mean?

 

[xtinct]

pushing up thread for today..