conditional look up

[armagan56]

I'm trying a conditional look up, I have worksheet A which lists stock trades

Worksheet A

Stock Date Price Quantity Cost
X 1/12/10 5 2 10
Y 1/1/11 2 2 4
Z 13/1/11 3 3 9
Z 17/1/11 2 4 8

on another worksheet B I have defined sectors for each of these 3 companies (X, Y & Z)

Worksheet B

X - Commodities
Y - Finance
Z - Industrial

what I want is a macro to look up on worksheet A all trades in January and copy the finance trades (looked up on worksheet B) on a worksheet called "finance", and industrial on the "industrial" worksheet.

so worksheet finance will look like

Y 1/1/11 2 2 4

and worksheet industrial will look like

Z 13/1/11 3 3 9
Z 17/1/11 2 4 8

If someone can point me in the right direction for a VBA Macro to do this, I would be grateful.

 

[justme1122]

Ok so this is the hard way to do things from someone that is new to VBA:

You can use a formular like:

=IF(MONTH(b2)=1,b2,"")

Then record a macro to filter/sort data

 

[Warship]

Here's a start maybe...

Sub OrganizeTrades()
    Dim r As Long    
    Dim notFound As String
    Dim found As Range
    Dim secSh As String
    Dim nxRow As Long    
    Dim TradesTable As Range
    Dim SectorTable As Range
    Set TradesTable = Sheets("Worksheet A").Cells(1, 1).CurrentRegion
    Set SectorTable = Sheets("Worksheet B").Cells(1, 1).CurrentRegion
    
    For r = 2 To TradesTable.Rows.Count
        Set found = SectorTable.Find(TradesTable.Cells(r, 1), , , xlWhole, xlByColumns)
        If Not found Is Nothing Then
            secSh = found.Offset(, 1)
            nxRow = Sheets(secSh).Cells(Rows.Count, 1).End(xlUp).Row + 1
            Sheets(secSh).Cells(nxRow, 1) = TradesTable.Cells(r, 1)
            Sheets(secSh).Cells(nxRow, 2) = TradesTable.Cells(r, 2)
            Sheets(secSh).Cells(nxRow, 3) = TradesTable.Cells(r, 3)
            Sheets(secSh).Cells(nxRow, 4) = TradesTable.Cells(r, 4)
            Sheets(secSh).Cells(nxRow, 5) = TradesTable.Cells(r, 5)
        Else
            notFound = notFound & TradesTable.Cells(r, 1) & vbLf
        End If
    Next r
    
    MsgBox "Not found " & vbLf & notFound
    
End Sub

 

[MickG]

Try this:-
NB:-Sheet (1) & (2) data start Row (2).
NB:- Your Worsheet (A) = Sheet (1) and Worksheet (B) = Sheet (2)

[COLOR=navy]Sub[/COLOR] MG17Mar10
[COLOR=navy]Dim[/COLOR] Mth [COLOR=navy]As[/COLOR] [COLOR=navy]Integer[/COLOR]
[COLOR=navy]Dim[/COLOR] Sht2 [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Dim[/COLOR] Ac [COLOR=navy]As[/COLOR] [COLOR=navy]Integer[/COLOR]
[COLOR=navy]Dim[/COLOR] Temp [COLOR=navy]As[/COLOR] Variant
[COLOR=navy]Dim[/COLOR] sht [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Dim[/COLOR] Rng [COLOR=navy]As[/COLOR] Range, Dn [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Dim[/COLOR] c [COLOR=navy]As[/COLOR] [COLOR=navy]Long[/COLOR]
Mth = InputBox("Enter a Month between 1 and 12", "Select Month", 1)
[COLOR=navy]With[/COLOR] Sheets("Sheet2")
[COLOR=navy]Set[/COLOR] Sht2 = .Range(.Range("A2"), .Range("A" & Rows.Count).End(xlUp))
[COLOR=navy]End[/COLOR] With
[COLOR=navy]With[/COLOR] Sheets("Sheet1")
[COLOR=navy]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
[COLOR=navy]End[/COLOR] With
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] sht [COLOR=navy]In[/COLOR] Sht2
    ReDim Ray(1 To Rng.Count, 1 To 5)
    [COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng
        [COLOR=navy]If[/COLOR] Dn = sht And Month(Dn.Offset(, 1)) = Mth [COLOR=navy]Then[/COLOR]
             c = c + 1
             [COLOR=navy]For[/COLOR] Ac = 1 To 5
                  [COLOR=navy]If[/COLOR] Ac = 2 [COLOR=navy]Then[/COLOR]
                    Ray(c, Ac) = Format(Dn(, Ac), "dd/mm/yyyy")
                  [COLOR=navy]Else[/COLOR]
                    Ray(c, Ac) = Dn(, Ac)
                  [COLOR=navy]End[/COLOR] If
              [COLOR=navy]Next[/COLOR] Ac
        [COLOR=navy]End[/COLOR] If
        [COLOR=navy]Next[/COLOR] Dn
    [COLOR=navy]If[/COLOR] c > 0 [COLOR=navy]Then[/COLOR]
        [COLOR=navy]With[/COLOR] Sheets(sht.Offset(, 1).Value)
            Temp = .UsedRange.Resize(1).Value
            .UsedRange.ClearContents
            .Range("A2").Resize(c, 5) = Ray
            .Range("A1").Resize(, 5) = Temp
        [COLOR=navy]End[/COLOR] With
     [COLOR=navy]End[/COLOR] If
        c = 0
 [COLOR=navy]Next[/COLOR] sht
MsgBox "Run !!"
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

 

[armagan56]

Mick tried running your attempt and it got stuck at

With Sheets(sht.Offset(, 1).Value)

when it wanted to debug.

 

[armagan56]

Just me I was actually after a more comprehensive code,

Warship yours seems to stop at

nxRow = Sheets(secSh).Cells(Rows.Count, 1).End(xlUp).Row + 1

but I can't figure out why, again have adjusted your code to allow for the worksheets to be renamed.

 

[MickG]

This is your data in sheet(1)

[COLOR=royalblue][B]Row No [/B][/COLOR][COLOR=royalblue][B]Col(A) [/B][/COLOR][COLOR=royalblue][B]Col(B)     [/B][/COLOR][COLOR=royalblue][B]Col(C) [/B][/COLOR][COLOR=royalblue][B]Col(D)   [/B][/COLOR][COLOR=royalblue][B]Col(E) [/B][/COLOR]
1.      Stock   Date        Price   Quantity  Cost   
2.      X       01/12/2010  5       2         10     
3.      Y       01/01/2011  2       2         4      
4.      Z       13/01/2011  3       3         9      
5.      Z       17/01/2011  2       4         8      
6.      X       01/01/2010  5       2         10

This is your Data in sheet(2).

[COLOR=royalblue][B]Row No [/B][/COLOR][COLOR=royalblue][B]Col(A) [/B][/COLOR][COLOR=royalblue][B]Col(B)      [/B][/COLOR]
1.      Stock   Name        
2.      X       Commodities 
3.      Y       Finance     
4.      Z       Industrial

The code expects you to have in your workbook all the sheets shown in column "B" (Name) of sheet(2), If the code looks for them (Ref:- Sheets(sht.Offset(, 1).Value) and there not there , an Error will result.
I would first try to run the code on the limited data, get that workng first. !!
Mick

 

[armagan56]

Mick,

I've just done it, thank you, you are a star.
Thank you to all those who made suggestions.

cheers,