New to excel VBA : Need guidance

xtinct

New Member
Joined
Sep 11, 2006
Messages
48
example1.jpg

Example1
example2.jpg

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.
 

Some videos you may like

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.

xtinct

New Member
Joined
Sep 11, 2006
Messages
48
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

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
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

New Member
Joined
Sep 11, 2006
Messages
48
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

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995

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

New Member
Joined
Sep 11, 2006
Messages
48
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

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995

ADVERTISEMENT

I don't think I understand...
try this anyway
Code:
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

New Member
Joined
Sep 11, 2006
Messages
48
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
example1.jpg


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

Result would be :

Example1.xls
example1a.jpg


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
example2a.jpg


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
 

xtinct

New Member
Joined
Sep 11, 2006
Messages
48
it wasn't the result from the codes you have posted.. i was explaining what i need to do to get the desired result..
 

Watch MrExcel Video

Forum statistics

Threads
1,122,509
Messages
5,596,568
Members
414,079
Latest member
Frills

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top