Programatically selecting a subset of a column of data

SRD · General User Joined: 06 Mar 2007 Posts: 10

Hi,

I have a column of product codes. I wish to select a subset of these, based on a smaller set of codes elsewhere, either in a text file or in another column.

Is a macro the best way to do this?

Thanks,

SRD

noranthon · Super User Joined: 07 Jul 2005 Posts: 3318

By "select" you mean "select the cells containing the data" or "display the data in another part of the spreadsheet" or something else? A tangible example would help.
_________________
search forum by month

SRD · General User Joined: 06 Mar 2007 Posts: 10

Sorry, I want to select the cells and copy and paste the cell contents into another column.

The column contains product codes for a marine and boating shop. Only some of the products have a jpeg image at the moment. The problem is that there is no pattern to what codes have an image available, so the task of manually picking out each code that does have a jpeg image, and pasting that code into the jpeg column (the jpeg will be [product code].jpg) is tedious.

However, I do have the list of specific product codes that do have a matching jpeg image in a seperate text file, which I'm wondering could be used somehow in a macro or a procedure to select all cells in the first product code column that match those in the text file or an array of some kind. Once these cells are selected, they can then be copied and pasted into the jpeg column.

The list is long, hence the preference for an automated solution.

noranthon · Super User Joined: 07 Jul 2005 Posts: 3318

If you can get the list from the text file into the spreadsheet in regular order, your task will be a lot simpler. By regular order I mean a column of names and a column of corresponding codes.

Once the data is in the spreadsheet, you can use it as a lookup range. The VLOOKUP function would be adequate.

You may have to use a medium like a Writer table to get the data into regular order. That's the main hurdle but it's a lot easier, I suspect, than trying to devise script to read and compare a text file and a spreadsheet range - unless someone has already done that.
_________________
search forum by month

Mark B · Posted: Tue Mar 06, 2007 11:55 pm Post subject:

Hi

I'm a great believer of only doing a job once. The following macro checks for the existence of the jpg for you and then enters the location. It asumes that you have you product codes in Sheet 1 column A, and post the results into column B:

SRD · General User Joined: 06 Mar 2007 Posts: 10

Hi Mark,

That is brilliant, thanks!

A question: can the img directory be in windows format. ie. img_dir = "E:\WebApplications\site\images\" ?

This is the only variable I've changed and the macro's not worked as yet.

I'm on WinXP.

Thank you,

Steve

SRD · General User Joined: 06 Mar 2007 Posts: 10

I've also tried the following format:
"file:///e|/WebApplications/site/images/"

and, a relative path
"/images/"

but they're not working.

What is the standard path syntax in OO macros?

Villeroy · Posted: Thu Mar 08, 2007 8:19 am Post subject:

Try:

SRD · General User Joined: 06 Mar 2007 Posts: 10

Thanks Villeroy

The ConvertToURL function fixed it.

I will delve into the latter code when I need to.

Thank you (all).