Ron deBruins copy5 code amendment

G

Guest

Hi everyone and thanks for taking a look and for generally increasing my
knowledge enormously.

But alas not enough.

I wanted to amend Ron's code for all unique values in a column and
successfully made the minor alterations to get it to work, as a start.

However I don't want all unique values I only want them according to
named areas on a separate sheet "DATA" I have and intend to hide before
release. "SheetNames" & "CodeNames"

This contains (among a lot of other stuff) two columns with headers - as
it happens they are Data!N1:N8 (SheetNames) and Adjoining O1:08 (CodeNames)

N2 to N8 contain the worksheet names required
O2 to O8 contain the codes contained in the filter column in the source
sheet.

SheetNames CodeNames
Cash CSH, ATM
Bcard CC1
Capital1 CC2
RBSVisa CC4
RBSVisa CC4
All&Leic CC5
Tesco CC6
Bank DD, STO, OTR, CQ, CC3

I am not sure we can put several codes in one cell?

I would want to delete each sheet name if it is pre-existing

Then the first time round create the sheet Cash and fill in values from
the filtered source sheet.

Easy eh? Well I'm sorry to say that after two days my brain appears to
have melted.

I got as far as creating the sheet with the right name, but code tries to
take the filtered data from the "DATA" worksheet instead of the one
defined.

<rant> I've been database programming since about 1984 and could do this
in an hour with a few SQL statements in visual Foxpro, but no-one wants it
since MickeySoft have killed a perfectly good language.</rant>

Is this enough info to point me in the right direction? Or do I have to
show the world my poor attempt at amending the code.

I must say the help the experts here provide to people like me is the most
impressive thing I have seen about excel. I can't keep pace with the
traffic in here.

regards, Alan
 
R

Ron de Bruin

Hi Alan

You can use application.match to test the unique value against the data table

If IsError(Application.Match(Cell.Value, _
Sheets("DATA").Range("A1:A200"), 0)) Then ...............
 
G

Guest

Hi Ron,

Well, I'm afraid I couldn't see where your suggested code might fit.
However I now have your amended code *partly* working.

I can create each new sheet (as per Sheetnames list below) and populate
them with *part* of the data required.

I can parse out, for example the left and right portions of the list
"CodeNames". So the first criteria for worksheet "Cash" is "CSH" and the
second is "ATM". That works.

Where there is only one "code" that works too, but the last in the list
has 5 codes and that is the stumbling block (for me).

How can I do the autofilter with several criteria (on the one field)? I
have found something called a filter collection which sounds like it might
do the trick, but the MS examples don't enlighten me as to how I might
incorporate them.

Is it possible? or do I have to do them all separately and concatenate all
the data blocks before finally sorting on a different (date) field. That
way seems inelegant somehow.

Not only that but ISTM that if there were to be changes, the code would
have to be altered instead of just changing the named areas as required.

I am being pressured into getting this done soon. I know it can be done, I
just want it to be relatively maintenance free afterwards.

regards, Alan

*From:*
(e-mail address removed)
*Date:* Tue, 01 Sep 2009 02:51:44 -0500

Thank you for the pointer, I will try and make sense of it!

regards, Alan
Click to expand...


regards, Alan
 
G

Guest

Ron, that is incredibly generous of your time. Thank you very much.

However I am very pig headed and NEED to learn this stuff (my wife thinks
I am nuts working on this in the evenings till VERY late - because this is
a separate contract to my normal daytime work). I believe (after another
10 hours at it!) that I THINK I have a sight of a solution and will
persist for a day or two longer.

With your permission I will send you what I have for criticism when I have
exhausted what brains I have. I will be prepared to be humbled. :-D

It is very irritating knowing what you want to do, but lacking the
language & syntax knowledge. Also irritating is that VBA has similar
functions to Excel but with different syntax.

Thanks again, it's great to have a backstop.

regards, Alan

*From:* "Ron de Bruin" <[email protected]>
*Date:* Thu, 3 Sep 2009 21:49:59 +0200

Hi Alan

You can send me a small workbook and tell me what you want
and I try to look at it for you this week

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm




<[email protected]>
wrote in message
Click to expand...


regards, Alan
 
G

Guest

Nearly there I think (phew!) I'm quite pleased that it all (seems to) work
up to here.

1. Do you still want to look at it? (maybe give you a laugh!) I have one
or two "cheats" in there because of time constraints.
2. If so do I send it to the address here?
3 It will demonstrate better as the whole spreadsheet (408Kb) will that be
OK?

my addy is a_pengelly_ampersand_cix.co.uk

(Remove underscores and replace ampersand with @)

No problem
Click to expand...


regards, Alan
 
G

Guest

I won't waste your time, since it is working. I have too much regard for
your expertise. Thank you for your assistence so far.

I was only interested if you had any criticism's on methodology /
weaknesses.

*From:* "Ron de Bruin" <[email protected]>
*Date:* Wed, 9 Sep 2009 23:00:21 +0200

Sorry, I have no time to look at it this week
If you still have problems send it to my private address
and I will look at it next week
Click to expand...


regards, Alan
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Method to group lines 1
VB Help 3
revise CONCATENATE code 2
Sum of sub groups 3
Send mail code from Ron 2
Creating a Chart on form from selected data in query. 0
change code name 1
Efficient use of sheet "codenames" 5

Top