Lookup solution needed

**drucey** · 04-21-2006, 08:44 AM

Hi Excel people,

I have been sat here for 2 hours, trying to think of a solution to a problem i have.

I have spent a few days making a macro that:

Opens a CAP (like txt) file in excel
Trims it down to the required data
Finds the necessary bits, copies it all to a workbook
Extract first lot of data, and paste into the correct date column in a summary sheet.
Extract the second lot of data, do some lookup and put into the correct date column in a summary sheet.

It's the third lot of data i can't work out how to get it into the summary sheet.

The third lot of data is: Discount Method, and discount value.
Basically, it records how much discount has been given in a day, and by what code (ie. 50% off, 20% off, 3 for £12)
The CAP files only list which discounts have been used that day.

With the CAP file data in a "temp" sheet in my workbook, is there any way i could look which discount method it is, look at the value of that method, then go back into my main summary sheet and put it into the relevant discount row, in the correct date column (which is called capdatecol).

The main problem i have is i have to make Rows with the discounts in beforehand. I could do this, and possibly a MsgBox come up telling me if the Discount wasn't present in the summary sheet?

So, what i'm trying to do is:

Macro to look in "temp" worksheet - to find the discounts (ie. 1 cell below "DISCOUNT REASONS" down to "TOTAL DISCOUNTS"), to find out what values are against them.. then to go into "Till02", and put in against the Discount row headings. (The column it has to go in should already be named inthe macro as CapDateCol)

The cap size changes, so i can't reference it directly.

I would really, really appreciate any help on this.
I have attached what i've done so far, please don't laugh at my simple and long winded macro, i'm proper learning!

(The sheets to take notice of: Till02 and temp (temp being right at the end)

What i've done so far:

http://www.teampsp.co.uk/Stores.rar
(Main sheet plus a few example cap files - to run the macro, click the man in the top left corner on "Till02")

Ardus Petus · 04-21-2006, 09:20 AM

Could you please post some sample data?

TIA
--
AP

"drucey" <[email protected]> a écrit dans
le message de news:[email protected]...
>
> Hi Excel people,
>
> I have been sat here for 2 hours, trying to think of a solution to a
> problem i have.
>
> I have spent a few days making a macro that:
>
> Opens a CAP (like txt) file in excel
> Trims it down to the required data
> Finds the necessary bits, copies it all to a workbook
> Extract first lot of data, and paste into the correct date column in a
> summary sheet.
> Extract the second lot of data, do some lookup and put into the correct
> date column in a summary sheet.
>
> It's the third lot of data i can't work out how to get it into the
> summary sheet.
>
> The third lot of data is: Discount Method, and discount value.
> Basically, it records how much discount has been given in a day, and by
> what code (ie. 50% off, 20% off, 3 for £12)
> The CAP files only list which discounts have been used that day.
>
> With the CAP file data in a "temp" sheet in my workbook, is there any
> way i could look which discount method it is, look at the value of that
> method, then go back into my main summary sheet and put it into the
> relevant discount row, in the correct date column (which is called
> capdatecol).
>
> The main problem i have is i have to make Rows with the discounts in
> beforehand. I could do this, and possibly a MsgBox come up telling me
> if the Discount wasn't present in the summary sheet?
>
> So, what i'm trying to do is:
>
> Macro to look in "temp" worksheet - to find the discounts (ie. 1 cell
> below "DISCOUNT REASONS" down to "TOTAL DISCOUNTS"), to find out what
> values are against them.. then to go into "Till02", and put in against
> the Discount row headings. (The column it has to go in should already
> be named inthe macro as CapDateCol)
>
> I would really, really appreciate any help on this.
> I have attached what i've done so far, please don't laugh at my simple
> and long winded macro, i'm proper learning!
>
>
> --
> drucey
> ------------------------------------------------------------------------
> drucey's Profile:
http://www.excelforum.com/member.php...o&userid=32553
> View this thread: http://www.excelforum.com/showthread...hreadid=534932
>

**drucey** · 04-21-2006, 09:33 AM

Hi Ardus,

My sheet so far and some example cap files are http://www.teampsp.co.uk/Stores.rar

Thanks for looking!
(I've commented the macro to help you understand it)