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.
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
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
 
Upvote 0
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
 
Upvote 0
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:
Upvote 0
Mick tried running your attempt and it got stuck at

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

when it wanted to debug.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
Mick,

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

cheers,
 
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,016
Members
448,543
Latest member
MartinLarkin

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
Back
Top