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]

so far i have only figure out how to copy one by one..

Dim ProdID As String
Workbooks.Open Filename:= _
"C:\Documents and Settings\xtinct\Desktop\Example2.xls"
Windows("Example2.xls").Activate
ProdID = Range("A2").Value
Windows("Example1.xls").Activate
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="=*" & ProdID & "*", _
Operator:=xlAnd

Application.WindowState = xlMinimized
Windows("Example1.xls").Activate

status = Range("B2").Value
Windows("Example2.xls").Activate
Range("B2").Value = status
End Sub

 

[jindon]

Hi

Confusing,

In your example2, Product name is only 2 characters.
Do it mean all product are grouped?
What do you expect in the status column?

 

[xtinct]

Hi, what i meant was "PA1-AA" "PA2-AA" "PA3-AA" are all in "PA" category..

so when i filter Example1 for "PA" is should get "PA1-AA" "PA2-AA" "PA3-AA".

then i need to compare their Risk status and get the highest risk status and then copy it into Example2 "PA" status Colum...

 

[jindon]

ADVERTISEMENT

Hi, what i meant was "PA1-AA" "PA2-AA" "PA3-AA" are all in "PA" category..

so when i filter Example1 for "PA" is should get "PA1-AA" "PA2-AA" "PA3-AA".

then i need to compare their Risk status and get the highest risk status and then copy it into Example2 "PA" status Colum...

So what is your actual desired results?

 

[xtinct]

my actual desire result is to Automatically filter out all Product Name of Example2 from Example1 and to compare each of their Risk status and find the highest risk and Copy into Status colums of respective Product Name..

For Example

When filtering for PC, i would get

#########Risk Status
PC4-CC####Risk High
PC5-CC
PC6-CC####Risk Low
PC7-CC####Risk High
###############
So, i need to compare their Risk Status and get the highest risk. In this example, the highest risk is Risk High.
Therefor i need to copy it to Example2 Status colum

#########Status
PA
PB
PC#######Risk High


Hope this will make it clearer to you..

PS: Sorry that i couldn't use the HTMLMaker as i am in office and there are limitations...

 

[jindon]

ADVERTISEMENT

I don't think I understand...
try this anyway

Sub test()
Dim a, result(), i As Long, z As String, n As Long
a = Range("a1").CurrentRegion.Resize(,2).Value
ReDim result(1 To UBound(a,1), 1 To 2)
With CreateObject("Scripting.Dictionary")
     .CompareMode = vbTextCompare
     For i = 2 To UBound(a,1)
          If Not IsEmpty(a(i,1)) Then
               z = Left(a(i,1),2)
               If Not .exists(z) Then
                   n = n + 1
                   result(n,1) = z
                   result(n,2) = a(i,2)
               Else
                   x = .item(z)
                   If InStr(1,result(x,2),"High",vbTextCompare) = 0 Then
                         If InStr(1,a(i,2),"High",vbTextCompare) > 0 Then
                              result(x,2) = a(i,2)
                         ElseIf InStr(1, a(i,2), "Med",vbTextCompare) > 0 Then
                              result(x,2) = a(i,2)
                         End If
                   End If
                End If
          End If
     Next
End With
Range("e1").Resize(n,2) = result
End Sub

 

[xtinct]

hi, thanks for the help.. but i find the code confusing.. might be because of my lack of knowledge of the programming knowledge..

what i meant was Example1 and Example2 are 2 different files.. so i have to use the VBA to

Example1.xls

1st - filter out "PC" from Example1.xls

Result would be :

Example1.xls

2nd -compare & get the Highest risk from the result of the filter from Example1.xls and Copy it to Status of Example2.xls

Result :

Example2.xls

that means i have to work with 2 Excel files..
and i need to automate it to do the task for all of the items in "Product Name" of Example2.xls

 

[jindon]

How did you get the result out of the code I posted?

 

[xtinct]

it wasn't the result from the codes you have posted.. i was explaining what i need to do to get the desired result..