conditional look up

armagan56

New Member
Joined
Feb 10, 2009
Messages
31
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.
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

justme1122

New Member
Joined
Mar 6, 2011
Messages
47
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

Well-known Member
Joined
Jul 17, 2007
Messages
1,051
Here's a start maybe...
Rich (BB code):
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

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
Try this:-
NB:-Sheet (1) & (2) data start Row (2).
NB:- Your Worsheet (A) = Sheet (1) and Worksheet (B) = Sheet (2)
Code:
[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
 
Last edited:

armagan56

New Member
Joined
Feb 10, 2009
Messages
31

ADVERTISEMENT

Mick tried running your attempt and it got stuck at

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

when it wanted to debug.
 

armagan56

New Member
Joined
Feb 10, 2009
Messages
31
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

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
This is your data in sheet(1)
Code:
[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).
Code:
[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

New Member
Joined
Feb 10, 2009
Messages
31
Mick,

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

cheers,
 

Watch MrExcel Video

Forum statistics

Threads
1,122,217
Messages
5,594,887
Members
413,947
Latest member
gizmolucy

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