Re: [ExcelVBA] Re: AutoTransfer

 

Good solution Paul.... and as you say.... *fast*!!!

Lisa

-----Original Message-----
From: Paul Schreiner schreiner_paul@att.net [ExcelVBA] <ExcelVBA@yahoogroups.com>
To: ExcelVBA <ExcelVBA@yahoogroups.com>
Sent: Thu, Aug 17, 2017 12:25 pm
Subject: Re: [ExcelVBA] Re: AutoTransfer

"Beyond me"??

I don't think so.

Breaking it down, it's combining a lot of simple techniques.

I was introduced to the Dictionary Object several years ago.

I work with a LOT of large data sets.

Several over 100,000 rows.

If I utilized a database engine like SQL server, Access or (my favorite) Oracle(r), then I could index fields and make them easier to search.

the Dictionary Object makes this EXTREMELY quick.

I often read through the data sets and load multiple Dictionaries with the fields I'm indexing.

Often, the VALUE can be a delimited string (like a comma separated list of the rows that have a certain value in column "C")

The basic technique is that you want to "search" through the data set for a specific value and update a designated column.

You COULD do exactly that... trigger a "search"

But that's time (CPU time) consuming.

Instead, I loaded the search field (Name) into the Dictionary once.

then performed the search in computer memory instead of Excel worksheets.

I'd be glad to answer any questions you have or help understand anything you find confusing.

 

Paul

-----------------------------------------
"Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can." - John Wesley
-----------------------------------------

On Wednesday, August 16, 2017 4:03 PM, "grahamfellows2002@yahoo.co.uk [ExcelVBA]" <ExcelVBA@yahoogroups.com> wrote:

 

Paul, Thank you so much. I've been combing the web for ages trying to find a suitable method for this and you have provided one in the first few hours that this post has been live. This solution was beyond me; I've never used the Scripting.Dictionary Object before and wow it works perfectly.  I take your point about ensuring the "paid" cell is blank at the beginning of the process. I'll get onto that. Thanks again Paul I'm very grateful for this.

Regards

Graham

__._,_.___

---

Posted by: Green <1z@compuserve.com>

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (6)

---

Have you tried the highest rated email app?

With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.

---

----------------------------------
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.

----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA

----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com

----------------------------------

Visit Your Group

• New Members 1

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___