# New to excel VBA : Need guidance

#### xtinct

##### New Member

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?

#### xtinct

##### New Member
hi, sorry.. but i dont get what u mean..

### Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

#### jindon

##### MrExcel MVP
Did you test the code anyway?

#### jindon

##### MrExcel MVP
try this one
Code:
``````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

##### Well-known Member
xtinct -

Here is the code I have used to output example2.xls that I have posted.
Code:
``````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
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)
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

##### New Member
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

Replies
2
Views
75
Replies
0
Views
137
Replies
1
Views
75
Replies
13
Views
465
Replies
0
Views
1K

1,171,565
Messages
5,876,215
Members
433,185
Latest member
ptr009

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back