Replies

Thanks…but just to clarify, I need this to paste-value the range as the cells within CURRENT contain formulas.

The field size is definately only 4 characters & there aren’t any spaces. The data is downloaded (by me) from our antiquated mainframe & uploaded to an SQL Server (6.5) for storage.

OK, just did some more testing. If I put “####”, it returns data that isn’t “####” BUT it also omits blank fields, which I require.

Sorry if I seem to be leading you a merry dance.

Yes, there are lots of other text (and blank) entries.

In a query I wish to omit data where the text a the field is 4 hashes.

“####” returns no data at all. Why?

Am I missing something fundamental about the hash key here?

Forgot something – the data resides on SQL Server 6.5

Just for clarification, shouldn’t it be
CELL(“Col”,LFIELDS)

??

That’s the ticket.

Haven’t used INDEX before – hadn’t needed to.

Thanks to all of you.

…and now FILE_A

Guys,

I can see how it works, but unfortunately when I copy the Index formula in, I get the value 0. The only thing I added was the reference to the other file.

If someone could spot where I’ve gone astray, I will then be able to stop headbutting my desk!!

I’m attaching the 2 files. FILE_A is my result area, BRIEF_22 is the source data

BRIEF_22 first

Off the top of my head you have at least 2 ways.

1. Assume data starts in A1. Sort by column A. In B2 put the formula =IF(A2=A1,”DUP”,””). Copy this down as far as required & then paste value the results in place. You can then sort by column B and eliminate the duplicates.

2. Data – Filter – Advanced Filter. It should be easy enough to follow from there

Closer – but the ‘A38420000’ in File ‘B’ could be in any row. So I need something to find out where that is first.

Looks good, but unfortunately the required Uwyr (in File ‘B’) could be in any column – I should have put more sample data in for File ‘B’

I didn’t have a lot of libraries referenced, so I checked “Microsoft Access 9.0 object library” and it compiled OK, BUT…when I ran it, I received the error message “Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another”

Sorry, my mistake. I’m after VBA code to replace the update queries, not to actually run them.

Do you want VBA code to run existing update queries? YES

And do you want the result of AMT * n% to be stored in the AMT field itself? YES

Hans,

Just after posting it, I did just that. Edited it and clicked away. HOW SIMPLE!!

Thanks
John