Auto Vlookup...

[m0atz]

Afternoon all,

Unfortunately I have no admin rights on this laptop therefore cant install excel jeanie to show you what I mean, however hopefully will be able to explain my request as best I can.

I have a sheet with all my customer details. Each customer has one row of data, and the columns go from A:BE. There is a whole load of customers and part of the data is the date of their last visit to me and the price i charged them. The way the sheet is set up is it allows for up to 15 visits by the customer in a year (generally they wont be over 10) so Col AB:AP is entitled Date1 to Date15. Col AQ:BE is entitled Price1 to Price15 and each price will obviously be attributed to each visit, i.e. date1 = 17/03/09 and price1 = £25.00 for example, i.e. they will be linked on my userform (when its finished..!)

What I'm after is for a sheet called "Data" to run through all my customers and present the dates in Column B and the associated Price in Column C, i.e. as a table list. Ideally with a heading of the customer name for ease of reference - this will make referencing and displaying further reporting data much easier.

So, in short then, I need some code to run through my customer sheet, and lookup the dates and prices and tabulate them

Does that make any sense - hope so..!?
Thanks in advance.

Col

 

[onlyadrafter]

Hello,

does this macro work as expected?

Sub COPY_TO_DATA()
    For MY_ROWS = 2 To Range("A" & Rows.Count).End(xlUp).Row
        For MY_COLS = 28 To 42
            If Not IsEmpty(Cells(MY_ROWS, MY_COLS).Value) Then
                With Sheets("DATA")
                    .Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = Range("A" & MY_ROWS).Value
                    .Range("B" & Rows.Count).End(xlUp).Offset(1, 0).Value = Cells(MY_ROWS, MY_COLS).Value
                    .Range("C" & Rows.Count).End(xlUp).Offset(1, 0).Value = Cells(MY_ROWS, MY_COLS + 15).Value
                End With
            End If
        Next MY_COLS
    Next MY_ROWS
End Sub

 

[m0atz]

I didnt get anything on the Data sheet - does it need a reference to the customers page where the data is held?

Thanks for your help, much appreciated.

 

[Weaver]

Try running it with the original customer data as the active sheet.

Make sure you have another sheet called 'data' that's ready to accept the results.

 

[onlyadrafter]

Hello,

you need to be on the tab with all the details on.

 

[m0atz]

Thank you both - this works exactly as required. thanks for the time and effort with this - much appreciated..!

(ExcelR8R - two probs solved in two posts - you're on fire..!!)