VBA - lookup two values

[MartinL]

Here we go again!
I have a sheet that has been created by a database and looks ugh!
I am trying to run some vba over it daily so it is more meaningful.

This is the db export:

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	A&B Sales Orders
2	PREMIUM
3
4				25-Nov-09	26-Nov-09	27-Nov-09	28-Nov-09	Total
5			Trays	90.00	41.00	98.00	96.00	325.00
6			A&B (CUMBERNAU	Trays	41	20	30	0	91
7			A&B (CREWE)	Trays	28	21	33	0	82
8			A&B (BARNSLEY)	Trays	41	18	37	0	96
9			A&B (THATCHAM)	Trays	36	18	37	0	91
10			A&B (BRISTOL)	Trays	22	8	22	0	52
11			A&B (FAVERSHAM	Trays	62	22	58	0	142
12			A&B (HEMEL)	Trays	60	25	62	0	147
13			A&B (IRELAND)	Trays	10	8	13	0	31
14		Actual	Total	Trays	300	140	292	-	732
15			A&B (CUMBERNAU	Trays	0	0	0	34	34
16			A&B (CREWE)	Trays	0	0	0	23	23
17			A&B (BARNSLEY)	Trays	0	0	0	33	33
18			A&B (THATCHAM)	Trays	0	0	0	26	26
19			A&B (BRISTOL)	Trays	0	0	0	22	22
20			A&B (FAVERSHAM	Trays	0	0	0	47	47
21			A&B (HEMEL)	Trays	0	0	0	45	45
22			A&B (IRELAND)	Trays	0	0	0	10	10
23		Program	Total	Trays	-	-	-	240	240
24	AB Extra Fine Asp 125g X008	Total	Trays	300.00	140.00	292.00	240.00	972.00
25	3617	Total		Trays	300	140	292	240	972
26			Trays	300.00	140.00	292.00	240.00	972.00
27			A&B (MAYLANDS)	Trays	79	0	0	0	79
28		Actual	Total	Trays	79	-	-	-	79
29	AB Fine Beans 165g X012	Total	Trays	79.00	0.00	0.00	0.00	79.00
Sheet1

sorry its quite big!

This is what I want to produce from that data:

Excel Workbook
	M	N	O	P	Q	R	S	T	U	V	W
8	UID		25-Nov-09	26-Nov-09	27-Nov-09	28-Nov-09		25-Nov-09	26-Nov-09	27-Nov-09	28-Nov-09
9	3617	Actual	300	140	292	-	Program	-	-	-	240
10	10541	Actual	79	-	-	-
Sheet1

As you can see the db puts the dates in the wrong column but I rectify this in my end output

Thsi is what I have so far until i realised I was pulling in the wron row of data, now I am stuck!

Sub GetSalesData()
Dim TestForUID As Range
Dim TestForSaleType As Range
Dim UID As Range
Dim SaleType As Range
Dim Flag As Boolean
Dim HostInfo As Range
Dim Host As String
Dim wbThis As Workbook
Dim Lastrow1 As Long
Dim Lastrow2 As Long
Dim a As Long
Dim b As Long
Dim c As Long
Dim d As Long
Dim ArrivalDate As String
Dim txtString As String
Dim TestRange As String

Set wbThis = ThisWorkbook
a = 1
b = 1
'unhide worksheet
Sheets("SALESDATA").Visible = True
'Set start of paste range
Sheets("SALESDATA").Select
Range("A" & a).Select
'calculate last row for clean up
With ActiveSheet
    Lastrow1 = .Cells(Rows.Count, "A").End(xlUp).Row
End With

Workbooks.Open Filename:="L:\Commercial Sales Summary\ab.xls"

'find last row of Sales
With ActiveSheet
    Lastrow2 = .Cells(Rows.Count, "A").End(xlUp).Row
End With
'Get UID
With ActiveSheet
    Set TestForUID = .Range("A1:A" & Lastrow2 - 4)
End With
Range("A" & b).Select
For Each UID In TestForUID.Cells
    If UID.Value <> "" And IsNumeric(UID.Value) Then
    
        'Get Sales Data
        Intersect(ActiveCell.EntireRow, Columns("A:H")).Copy
        wbThis.Activate
        Sheets("SALESDATA").Select
        Selection.PasteSpecial Paste:=xlPasteValues
        
        'Get Description
        Range("B" & a).Select
        ActiveWindow.ActivatePrevious
        Range("A" & b - 1).Copy
        wbThis.Activate
        Sheets("SALESDATA").Select
        Selection.PasteSpecial Paste:=xlPasteValues
        
        'set cursor to next line
        a = a + 1
        Range("A" & a).Select
        ActiveWindow.ActivatePrevious
    End If
b = b + 1
Range("A" & b).Select
Next UID
ActiveWorkbook.Close False
'cleanup
Range("A" & a).Select
For c = a To Lastrow1
    Intersect(ActiveCell.EntireRow, Columns("A:AJ")).ClearContents
    a = a + 1
    Range("A" & a).Select
Next c
'Hide worksheet
Sheets("SALESDATA").Visible = False
End Sub

anyone interested in getting their hands dirty

 

[MartinL]

OK heres what I have done, it now works but if there is a neater way cool...

Sub GetSalesData()
Dim TestForUID As Range
Dim TestForSaleType As Range
Dim UID As Range
Dim SaleType As Range
Dim Flag As Boolean
Dim HostInfo As Range
Dim Host As String
Dim wbThis As Workbook
Dim Lastrow1 As Long
Dim Lastrow2 As Long
Dim a As Long
Dim b As Long
Dim c As Long
Dim d As Long
Dim ArrivalDate As String
Dim txtString As String
Dim TestRange As String
 
Set wbThis = ThisWorkbook
a = 2
b = 1
d = 1
 
'unhide worksheet
Sheets("SALESDATA").Visible = True
'Set start of paste range
Sheets("SALESDATA").Select
Range("A" & a).Select
'calculate last row for clean up
With ActiveSheet
    Lastrow1 = .Cells(Rows.Count, "A").End(xlUp).Row
End With
 
Workbooks.Open Filename:="L:\Commercial Sales Summary\ms.xls"
'Get date headers
Range("D4").Select
Intersect(ActiveCell.EntireRow, Columns("D:G")).Copy
wbThis.Activate
Sheets("SALESDATA").Select
Range("C1").Select
Selection.PasteSpecial Paste:=xlPasteValues
ActiveWindow.ActivatePrevious
 
'find last row of Sales
With ActiveSheet
    Lastrow2 = .Cells(Rows.Count, "A").End(xlUp).Row
End With
'Get UID
With ActiveSheet
    Set TestForUID = .Range("A1:A" & Lastrow2 - 4)
End With
Range("A" & b).Select
For Each UID In TestForUID.Cells
    If UID.Value <> "" And IsNumeric(UID.Value) Then
            Range("A" & b).Copy
            wbThis.Activate
            Sheets("SALESDATA").Select
            Range("A" & a).Select
            Selection.PasteSpecial Paste:=xlPasteValues
            ActiveWindow.ActivatePrevious
        'Get Active Sales Data
            With ActiveSheet
                Set TestForSaleType = .Range("B" & d & ":B" & b)
            End With
            For Each SaleType In TestForSaleType.Cells
            If SaleType.Value = " Actual" Then
                Range("B" & d).Select
                Intersect(ActiveCell.EntireRow, Columns("E:H")).Copy
                wbThis.Activate
                Sheets("SALESDATA").Select
                Range("C" & a).Select
                Selection.PasteSpecial Paste:=xlPasteValues
                ActiveWindow.ActivatePrevious
        'Get Program Sales Data
            Else
                If SaleType.Value = " Program" Then
                Range("B" & d).Select
                Intersect(ActiveCell.EntireRow, Columns("E:H")).Copy
                wbThis.Activate
                Sheets("SALESDATA").Select
                Range("G" & a).Select
                Selection.PasteSpecial Paste:=xlPasteValues
                ActiveWindow.ActivatePrevious
            Else
                'nothing
                End If
            End If
            d = d + 1
        Next SaleType
 
        'Get Description
            wbThis.Activate
            Sheets("SALESDATA").Select
            Range("B" & a).Select
            ActiveWindow.ActivatePrevious
            Range("A" & b - 1).Copy
            wbThis.Activate
            Sheets("SALESDATA").Select
            Selection.PasteSpecial Paste:=xlPasteValues
 
        'set cursor to next line
        a = a + 1
        Range("A" & a).Select
        ActiveWindow.ActivatePrevious
    End If
b = b + 1
Range("A" & b).Select
Next UID
ActiveWorkbook.Close False
'cleanup
Range("A" & a).Select
For c = a To Lastrow1
    Intersect(ActiveCell.EntireRow, Columns("A:AJ")).ClearContents
    a = a + 1
    Range("A" & a).Select
Next c
'Hide worksheet
Sheets("SALESDATA").Visible = False
End Sub

 

[Jobowo]

Hi Martin

I tried to help but this is difficult to unfathom for an outsider and I gave up before I got far. Could I make a couple of general coding suggestions?

As far as I can see, you are working with the Rawdata input and then moving your cleaned up data to a Summary sheet. You seem to be using a worksheet "SALESDATA". These may not be the best names but try something this:

Dim ShRawData as worksheet
Dim ShSummary as worksheet
Dim ShSalesData as worksheet

set ShRawData = ActiveSheet
workbooks("X").Open
Set ShSummary = Workbooks("X").worksheets("Sheet1")

Then you can move data backwards and forwards without flipping between active/previous windows (which is really confusing to read).

ShSalesData.cells(x,y) = ShRawData.cells(x,z)

Note: no copy/paste and flipping windows.

Another note: I see that at the bottom of the code, you are varying 'a' but above that a,b,c etc are constant, yet you repeatedly use "A" & b rather than "A1", "A2". The latter is far easier to read.

Hope this is helpful.