Using Loops to find data

I want to find some code that will search down a column until it finds one of a number of different words. when it finds one of the words it stops and deletes the row on which the word is contained. after deleting the data in the row, it continues searching down the column until it finds another of the words.

for example......see work sheet attached,

I want some code that will search down column B until it finds Sally or Robyn. As soon as it finds either of these names it stops and deletes all the data in that row and then continues searching down column B to find Sally or Robyn again........

Is using a loop the best way to go with this???


MOD: thread moved to parent Programming forum (VBA)

The simplest option would be "filter down the data and then delete".

Originally Posted by yuanmin

Yes, you can use "for each... next" loop to achieve this.

For each loop can be a constraint if you have multiple rows. If you have to do with programming, then findnext would be a better option.

Copied the following code from Microsoft Help:

Please Login or Register  to view this content.

Thanks,
Vikas

Hi,
Filter and delete rows will be my choice. Here is code to do it:

Please Login or Register  to view this content.

Few notes - macro will create "headers" row and after that will delete it again. Add the words you want to search for in the array function.

Buran

As of the moment macro will search for John, Peter and Sally in column B and will delete the rows with this names. If you want to search for John, Peter and Greg for example, then the line will be

Please Login or Register  to view this content.

Please, note that edited my previous post to remove few things.

P.S. Ops, I think you are talking to other member... Sorry

cheers buran,

that works perfectly.......

In the "searchword" section. is there a way to search for part of a word in a field?

for example you are looking for Robyn in a field but the field includes Robyn Smith....

Also how do I cut and the paste "cellTypeVisible" with out cutting and pasting everything inbetween?

cheers for your help

yes, you can use wildcards
For example "Robyn*" will filter all records that start with Robyn

what are a "wild cards"......how do I incorporate it into below

SearchForWords = Array("John", "Peter", "Sally")

don't worry I just figured it out.....

Just a second - I noticed that it deletes one more row than it need to. I will answer to you shortly

I'm affraid i messed something with the code when I edited my first post. sorry
I think this works now correctly:

Please Login or Register  to view this content.

easty.
Please, note that I just edited the above code

Please Login or Register  to view this content.

hi Buran,

Glancing at the OP's file, your use of

Please Login or Register  to view this content.

is fine in this context. However, there is the chance for errors/unexpected consequences if the macro was used on a data set that contains blank rows because it will only define the range as far as the blank row. I recommend explicitly defining the range to be filtered, eg

Please Login or Register  to view this content.

, which then also ties in with your later use of "usedrange".

Rob

Hi Rob,

Thanks for your note. You are right - this is something I wasn't thinking about. Will have this in mind in the future. :-)

Buran

I'm using the new code you gave me

Please Login or Register  to view this content.

but for some reason it is not always deleting all the data required...........I've tried everything to figure out why but have no idea!! the old code seemed to work fine

If you are using the code from my post from yesterday, 11:40 AM, it works fine with the sample file. The code from my post 11:20, when running on sample file delete more rows than it have to, so you should not use it. The problem there was with the visible cells - I messed things when editing post.

Anyway I made change to the code after broro183 remark about using autofilter when there are empty rows within data. Here it is:

Please Login or Register  to view this content.

I tested it with the sample file and it works fine. If you still have problems, you will have to provide more info in order to investgate it. Also you can check the entry that according to you must be delete and is not. For example it can have leading/trailing spaces andthis way it will be different from what you are searching for (unless you are using wildcard).

hi Easty,

Can you please upload a more detailed dummy file which contains the code you are currently using & also shows a "before & after" with explanation?

If there are any further twists, can you please also include these in the post so we can be more effective & resolve your issues in less time?

Rob

hi Easty,

Sorry I may not get to this until the weekend, hopefully someone else can help you before then...

If you want to try a few things yourself, you may find the below links useful:
http://www.excelforum.com/excel-prog...ains-text.html
http://www.excelforum.com/excel-work...orksheets.html
& you may even get something from Stanley's post in http://www.excelforum.com/excel-prog...selection.html

Goodluck
Rob

Hi easty,
try this code:

Please Login or Register  to view this content.

I decided to make it in a way that will allow to search for multiple type of transactions. These are set in the SearchForWords Array. In the code I added also Accountancy fees
It loops trough all clients and adds worksheet, than it change filter applied to the transaction type and copies each separate type of transaction.

Buran

Hi,
I iddn't hink that would be a problem. Here is the code:

Please Login or Register  to view this content.

it seems to get stuck at the code below.........

Please Login or Register  to view this content.

any thoughts?....................I've just run it and it looks good......if I step into the code and F8 it, it gets stuck but when you run macro it gets stuck for a moment then keeps going......cheers for that Buran!! I might come back to you if I have any more queries cheers mate you've been a bloody good help!!

cheers

None. It works with the sample file. And I haven't changed it at all. The lines after Next i just set the column width of all sheets.

at which line is the error (which line is higlighted)?

If you change this line

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

It will loop only trough columns in the used range and not all columns, but apart from that - no ideas.

yes, but in my initial code it runs trough all columns, and it takes some time, so better use the changed one

Don't forget to mark thread solved, if it really is.

hey Buran

I don't want to delete the client's worksheet and insert a new one. I just want to retain each client's worksheet and insert the transactions at the bottom of previous transactions entered (if there are some) into their worksheet.

I've tried to figure it out in your code but it is a bit technical for me.......its bloody good though!!

does this make sense??

Hi easty,
I just came in the office. I will change it in few minutes.

Here it is

Please Login or Register  to view this content.

I also added few comments to explain what's going on.
I tested it with the sample file, but test it carefully yourself too.

Buran

if there are no accountancy fees for a client.....it still selects A2 and xldown and xlright.......so copy and pastes the whole worksheet......

if there is one accountancy fees it appears to copy and paste it 65310 times in the client's worksheet

Ops, my mistake - I haven't drink my coffee yet
I took the previous code, not the one I cahnged yesterday. Will fix it now

here it is

Please Login or Register  to view this content.

By the way - in the previeous code (where it doesn't check if anything left visible after the filter is applied), when there is accountancy fee, it copy it only once, not 65K times. At least with the sample file. Are you sure it copy it 65K times?

Hi easty,
I checked this with the sample file and if there is only one row of data it selects only this one, not the entire sheet. I don't know why do you think the oposite. Did you went using F8 trough the code? And I still don't understand why is this a problem - even if it selects the entire sheet (It DOES NOT) there are only blank cells and nothing will be copied to the client sheet. and if there is another transaction after that, it will be added in the first blank row.

Hi easty,
I'm afraid i misunderstood you. You were right. When I checked it with the sample file it was only one row of data, but not on row 1. Here is a code that fix this (or at least what I understand is the problem):

Please Login or Register  to view this content.

yes, it is possible.
easty, sorry, but I'm not able to work for you any more - I spent on this more time than I spent on my paid projects.

hi all,

Easty,
I'll post a modified version of Buran's code sometime over the next couple of days (hopefully tonight) for you to review & then you can reraise any outstanding issues.

Buran,
I appreciate your openness about time spent, but this can be minimised. I (& I imagine, others) also appreciate that you are being open but not touting/pushing for paid business (btw, check the forum rules re your sig as it may be a bit "borderline").
In line with some of the recommendations on the RentACoder site:
- I recommend clarifying exactly what is required before starting to code & then testing code as you go before posting. we're all human but I think I have seen more than a couple posts where you apologise for an error in your suggestion which could probably have been caught with a proper once over.
I'll try to comment the changes I make to your code so far so you can see my logic - of course it may not be better!
- Push for an accurate/representative sample file to be uploaded right from the start (from memory, I think you've done that in this post).


hth
Rob

@ broro183
I'm not pushing for paid business at all. If this is what you (or someone else) understood from my last post - this is NOT the case. I'm spending time on this forum as I can learn a lot from more experienced from me and I'm willing to help if I'm able to do so. My last post was simply explanation, why I can not reply on easty's changing requests anymore. It wouldn't be nice simply to stop answering his posts, isn't it? This thread started as filter by one column, after that it turned to be two columns, single search term and working on this I suggested that it could be made in a way that allows for multiple words search. Now it turns to be something different. So I decided that I'm not able/willing to follow this thread anymore. Once again - I'm not pushing for, asking or whatever payment from easty. As a general remark - my understanding is that everyone is devoting as much time to this forum as he wants/can.

Regarding my signature - when I added, there was no such rule #13, I checked. I think it was added few days ago 02-07-2010. If mods consider it violation of the rules, I will remove it. However - rule #13 is about "competitor forums" and I don't think my profile on freelance site is a competitor forum. I don't think it is different from a link to a homepage, for example.

Buran

hi Buran,

I'm sorry, I didn't mean to come across all heavy but I posted rashly in haste. I agree that your communication approach was a good one & I was just intending to give some tips. I apologise, I had a bad day in the office & I let it come through in my post.

I wasn't being sarcastic when I said "I appreciate that you are being open but (insert: replace with "and") not touting/pushing for paid business" - perhaps I should have worded it as "and" instead of "but". I DO appreciate your intent - in fact it was only from the link in your signature (in another thread) that I found out about Rentacoder's existence!

Yep, your understanding is right, we give as much time as we want/can & I really hope that my abrasive post doesn't put you off. You've made some good contributions & it would be great to see you continuing to develop
(I'm still learning too & hopefully we can share insights again)

re the signature, I don't think that Mod's will ping you at all & I suspected that you wouldn't know about rule #13. I just thought I'd let you know because I feel the communication about the rule hasn't been as thorough as it should be...


I've been taking a back seat on this thread because I didn't want to cut in. I can "tag team in" to see if we can get Easty as far as he wants to go...

Apologies again,
Rob

@ broro183

No need to apologize, Rob. No offence here, really. :-) May be it was due to my non-native English, that I misunderstood you last night (and it was really late here). I'm glad that we made all this clear.

Originally Posted by broro183

I really hope that my abrasive post doesn't put you off.

It needs much more to push me away from the nice community here.

Originally Posted by broro183

I've been taking a back seat on this thread because I didn't want to cut in. I can "tag team in" to see if we can get Easty as far as he wants to go...

Hey, no "private" threads here, I think :-) Will be glad to jump in and contribute again, if I can add anything to your code.

Also, thanks for pointing me the new rule and for the link in your last posting. Even if my sig is not exactly "a violation", I will change it.

@easty - don't let be shocked so easy :-)

Buran

hi all,

Buran, I'm pleased you're not easily put off

Here's the best I have (see next post), without going back & rereading the entire thread. I've had a go at commenting the code/changes & once Easty has an understanding of it, he can probably delete half of the comments...

I've separated some code out into functions (or copied & pasted functions that I have previously used) to prevent duplication & because you may be able to use it in other coding tasks.

I think it is important to identify what all data is to prevent any ambiguity so I have added in the HdrArr which can be edited to provide the name of the header cells for each column. With this in mind, I have removed the deletion of the inserted header row, & I also copy it in to each of the Client sheets as they are created. Easty, you can amend HdrArr as necessary, provided you put everything inside double appostrophes & separate the strings with a comma & space. If your SearchForWords list becomes much larger I'd move it from the defined VBA array & into a separate list sheet in the spreadsheet, which can be grabbed (like the client names) & iterated through.

I'm sure I've missed some points so please fire away with questions etc
Also please note that at the moment the code will re-insert a new (or second) header row if you rerun the macro on the same dataset.

see next post...


hth
Rob

Here's my thoughts on the code...

Please Login or Register  to view this content.

hth
Rob

Hi easty,
in his code Rob assumes that you have sheet named "Main". Rename the sheet where all clients data are to "Main"

Buran

Ooopps, my mistake!
(I'm sure I saw that somewhere in the thread?)


Thanks Buran :-)

Easty, does it work on your real data?
& more interesting for me - can you notice any difference in speed?
If you're happy, can you please mark the post as solved?

Rob

Thanks Rob,

How do I delete the headers and deactivate the filter at the end of the macro? I've tried a few different things but it always comes up as an error.

Also, with the spreadsheet I'm applying this to, it doesn't seem to capture all the clients in column C. I've manipulated the data to decrease the rows and then they seemed to get captured. Is there a limit to the amount of rows the macro searches down column C to find new clients?

Also, again with my spreadsheet, it doesn't seem to capture the "accountancy fees", only the equity purchases.

I certainly can notice the difference in speed......is there a general code I can use to apply to other macros to make it speed up?

Cheers Rob

Easty

hi Easty,

Originally Posted by easty

How do I delete the headers and deactivate the filter at the end of the macro? I've tried a few different things but it always comes up as an error.

I recommend keeping headers as it makes the data more auditable & removes much more potential for ambiguity. To remove them from the "Main" sheet try changing this section near the bottom of the code:

Please Login or Register  to view this content.

Originally Posted by easty

Also, with the spreadsheet I'm applying this to, it doesn't seem to capture all the clients in column C. I've manipulated the data to decrease the rows and then they seemed to get captured. Is there a limit to the amount of rows the macro searches down column C to find new clients?

Hmmm, no there shouldn't be a limit. Is the entire range selected if you change

Please Login or Register  to view this content.

Originally Posted by easty

Also, again with my spreadsheet, it doesn't seem to capture the "accountancy fees", only the equity purchases.

hmmm
It worked in the sample file for me. Can you please upload a new sample file with the latest code included?
Also, can you please identify the rows on the Main sheet with "accountancy fees" that don't end up where you expect them to be after you run the macro?

Originally Posted by easty

I certainly can notice the difference in speed......is there a general code I can use to apply to other macros to make it speed up?

Cool, optimising for speed before having a working product is not really the best way to do it - but it still gives me a buzz!

The main changes I made that improve the speed are commented in the code ie removing any ".select" & ".activate", and decreasing the amount of times information is pulled from a cell to be used in the VBE or pushed back into the spreadsheet. Also, the use of the "ToggleRefreshApp" sub improves the execution speed. It improves the speed because: turning off screenupdating stops Excel from "repainting the screen"; turning off calculation stops Excel trying to recalculate each time the autofilter is changed (depending on your version) & turning off events can stop other Excel macros/actions from trying to interrupt (I think this is unlikely in this case). You could use this sub at the start & end of other macros to help speed them up, as long as you aren't relying on the results of a calculation (or an event - eg refreshing a pivot table) in the macro.

Have a read of this thread (http://www.excelforum.com/excel-prog...id-in-vba.html), some knowledgeable people have put in some words of wisdom. If you look at this thread, make sure to read more than one post (around the post describing the technique you want to use) because some points are rebutted - esp mine!
As I say, I'm still learning

hth
Rob

I've tried it all out and tinkered with it a bit and it all seems to work very well!!.......

I would like to use the same code to select different data from the "Classification2" column and copy and paste it to a different column (column J for example) in each of the client's worksheets.

I can't figure out where in the code it selects the client's worksheet and then finds the cell in the worksheet to paste the data specific to that client from the Main worsheet.

Essentially, I would like to paste different data from the main worksheet into the client's worksheet in a different column of the client's worksheet.

I am very close to finishing what I want to achieve.

Thanks so much for your help Rob!!! it has been fantastic.......and Buran too!!

Easty

hi Easty,

Yay, I'm glad we are getting closer!

We'll achieve what you want to achieve but now is the time to be very explicit in describing what else you want to happen.
Can you please upload a new sample file with the latest code included*?
Can you please also include "before & after" examples & clear explanation of the logic behind the results?
*Have the working code identified & if you have problems with the below suggestions, you could store the attempted code in another module. To name the modules in the vbe, click on the module in the Project Tree on the left & press [F4] to bring up the Properties where you can change its name.

Originally Posted by easty

I would like to use the same code to select different data from the "Classification2" column and copy and paste it to a different column (column J for example) in each of the client's worksheets.

Include this in the sample file. btw, you can change the headers to what they actually are, by changing the words in the "HdrArr" line of code. To do this, just change the words within the appostrophes & be sure to separate them all by a comma & a space.

Originally Posted by easty

I can't figure out where in the code it selects the client's worksheet and then finds the cell in the worksheet to paste the data specific to that client from the Main worsheet.
Essentially, I would like to paste different data from the main worksheet into the client's worksheet in a different column of the client's worksheet.

The below bolded section of code shows what makes the copying occur & the non-bolded section shows where it is being copied to (ie destination).. The bit that starts with a dot (".cells(..." is referring/relates to the object in the previous (still "open") With statement which is "With wsTarget". "wsTarget" is defined shortly before that.

Please Login or Register  to view this content.

To break it down to column by column copying you will need to adjust the start of the above line of code from

Please Login or Register  to view this content.

On an aside, if there is too much copying into different areas your macro will become slower & it would probably be more effective to modify the layout of your Main sheet. This means that the changes only need to be made once (before the filter & copy to separate sheets), rather than repeatedly for each sheet. Does that make sense?

hth
Rob

Congratulations!
I hope everyone's well...

I'll glance at this thread occasionally, but when you do pop up a new post it may pay to p.m. me a link to the new post to remind me.

Rob

Hi Rob,

Please see attached the spreadsheet which I hope will give you a clearer idea of what I’m trying to achieve.

I’ve used a before and after worksheet for the “Smith Super Fund” as an example (Smith Super Fund = before and Smith Super Fund 1 = after).

So the first step is to copy and paste (from Transaction Type 2 – column G) the “Equity Purchase”, “Equity Sale”, “Bonus Issue” and “Rights Issue Takeup” for the Smith Super Fund (as the Master Name) to the Smith Super Fund worksheet. I only want to copy and paste the data as shown in the Smith Super Fund worksheet (Account Name, Date, Narration and Amount) not the other irrelevant data. Ideally I would like the most recent transactions to be pasted at the top of the worksheet. Pasting it at bottom them sorting by date – newest to oldest is probably a lot easier.

The next step is to copy and paste “Other Outgoings(Non-Asset Related)”, “Addition of Funds to Account” and “Accountancy Fees” to the Smith Super Fund worksheet. This is a bit trickier as we need to insert a column at Column I first, then copy and paste the formulas from cells J8:J11 (relating to December 2009) across to cells I8:I11, then paste the above data into Capital Flows section of the worksheet. We could also probably paste it at the bottom and then sort by date to make it easier. I need to be able to include the new month in the formula in cells “H1”, “H2”, “H3”, “H4”, “H5” and “H6” and then add one month to cell “H12”.

That is pretty much it I think.

I hope it all makes sense.

Cheers Rob

Easty

hi Easty,

I have a few questions:
- Are you happy for the file to contain a Template sheet which holds the layout & the required formulae for each created sheet?
- How is the "PTF Current Value" populated?
- I haven't used financial formulae for the past 10 years & am quite rusty with them. Is there any alternative to the Power formulae in H1:H6 which will allow for a range to be entered*, rather than each cell individually (& still achieve the same result)?
*it could then be easier to achieve the inclusion of new columns.
- After 12 months of data, won't the formulae in H1:H6 need to be changed for each yearly mile post?
- How often will the file be renewed?
eg, every year or will it be ongoing once the design is finalised?
(there are only 256 columns in Excel 2003 which does allow for 21 years of monthly data, but inserting the columns will upset the print area on each sheet)
- Will there only ever be one month of transactions in the Main sheet?
- Once the macro is run, do you want the info removed from the Main sheet to prevent it being run twice?

Rob

hi Easty,

When looking in slightly more detail (checking for unique values) I see that the below Type2 Transactions are not being copied to either the first section or to the "Capital flows" section. Can all these values/lines be ignored?

"Unit Trust Distribution", "Convertible Note Interest", "Dividend Received", "Increase Total Number of Units", "Miscellaneous Interest", "Portfolio Management Fee", "Purch Shares - new issue/float", "Custody Fee", "PCMF/CTFE Interest", "Final Return of Capital", "Write Out Asset", "Write-In - Actual Units", "Asset Amalgamation", "Term Deposit Purchase", "Dividend - DRP", "RIGHTS ISSUE", "Adjustment Acquisition Cost", "AMALGAMATIONS/RECONST'N/SPLITS", "Rights Entitlement", "RI PAYMENT OF CALL MONEY"

Rob

Hi Easty,

Sorry I've been distracted, I'll have a test file up for you some time tomorrow (UK time).

Updating the Power formulae is the tricky one at the moment. Would you be happy with it stating something along the lines of "Not Applicable" if that particular number of month's data doesn't yet exist for the particular fund?

Rob

hi Easty,

I think the attached file will do almost everything you're after...
I haven't fixed the Power formulae & I'm not sure if the current logic is correct!?
I've created a test sheet with some variations & references as I tried to figure it out but I couldn't make it easy by referencing just one or two cells. If the existing logic is correct (with one cell reference for each month), it is a painful way of creating a formula for 10 years of monthly data - perhaps this could be the topic of a new thread...).

I've changed the filtering to use a Helper column which is now based on two dynamic lists on the Reference Table sheet (ie add a transaction phrase to one of the lists & it should be copied to the client sheet when the macro runs). All new sheet creation is based on the Template (nb: power formulae need fixing) & adds them in order of their appearance in the data list. If you want the sheets sorted alphabetically, a quick google search should point you in the right direction.

Sorry I haven't documented the file at all, but have a play & hopefully you'll learn more from working through it.

Let mw know if you have any questions - I have my fingers crossed that it's reasonable because I've run a couple of tests & it seems to work.

After running the macro (from the button on the Data sheet), I have manually copied the data from the Backup back onto the Data sheet before re-running it.

hth
Rob

hi Phil,

Are you getting much sleep with your little one?

1. I'm sorry, I'm not sure how that happened, but you spotted it - can you fix it?
Just kidding (see attached file) ;-)
2. Again, sorry, I meant to comment that line out. The purpose for having the test in the code was to allow me to run the code "automatically" until it was upto the Smith client. When the code stopped at this point, it allowed me to step through the code one line at a time using the [F8] key.

I've added some comments to the header cells in the attached (version 6) file for the Ref Table sheet, added an extra test at the start of the button macro & added comments for the Helper column. btw, I have absolutely no idea why I put so much complexity into the Helper column's formula in version 5, and I have simplified this in v6.

hth
Rob

hi Easty,

Sorry - I'm not sure where I got the "Phil" from!

Try the below...

Please Login or Register  to view this content.

The "... (Backup)" sheet is deleted (if one already exists), then recreated by making & renaming a copy of the "... Input" sheet. The contents are then deleted off the "... Input" sheet, to prevent the macro being run twice on the same data set. This backup of the data allows for all the details to be available for any extra investigation.

Hopefully, I've got it right this time - at the moment, I'm certainly not living up to my past recommendations of testing code soundly first!

The main issue that I haven't attempted to overcome is changing the Power formulae on either the template sheet or existing Fund's sheets.

hth
Rob

Hi Easty,

Do you mean cell I16?

Yes, you're making sense
Try this one out...

It makes use of a reasonably common way of hardcoding eg

Please Login or Register  to view this content.

- see if you can spot it in the code ;-)

hth
Rob

another thing Rob,

For every month going forward, I want to update all client's worksheets whether they have "capital transactions" or "equity transactions" or not...... as I want to be able to update their portfolio values in cell I7 each month.

I've just been testing it with a lot more data and this is something that stands out as a risk......

I hope this is an easy fix.

I've tried the new hardcoding code for the date and the portfolio value.......for each client the "template's MT value" is hardcoded....not the MT value specific to each client. I've stopped the code just before it calls InsertColAndOrHardCode(ClientSht, False).....and the cell B1 in the client's worksheet isn't updated......I've checked that the formulas in excel options is on auto updated, but it still doens't seem to work.....any thoughts?.........don't worry mate, i worked it out. I put a range("b1").calculate straight before it calls the InsertColAndOrHardCode(ClientSht, False), and it works a treat!! sweet!!!
Cheers mate

hi Easty,

Sorry about the slow response. I'm not sure if I understand correctly, so, if the below code changes to the FilterAndCopyToSeparateClientShts macro (near the end) don't work, you may need to explain further...

Please Login or Register  to view this content.

It seems there may be some changes to your file layout ie "portfolio values in cell I7" is different from the v8 file which uses cell I8. So, if this suggestion doesn't work, can you please upload a new sample file with the new layout (& ideally, only the code that is currently being used)?


re the hardcoding - yeah, solved!
That's great :-)


hth
Rob

hi Easty,

I've made a few changes (scattered throughout the code & worksheets) which I've tried to document on the Notes sheet. Also, while trying to test I've come across an error which won't let me delete the "Backup" sheet before repopulating it, but I think that is more due to something in my actual Excel setup (than it is to the file). Hopefully you won't get the same "can't enter break mode" error as I'm currently getting...
There may be other changes needed but I'm off to bed now

btw, when you are debugging code & a line highlights in yellow, you can press [ctrl + L] to identify any macros which precede the highlighted one. This is how I found out where the "ws" worksheet wasn't being defined prior to the "InsertColAndOrHardCode" sub being called.


hth
Rob

hi Easty,

I'll try & look at this tomorrow...
- I'm getting a similar "object doesn't..." macro error too & may even start a new thread to see if someone can explain what's happening. I think it relates to either VBE, [alt + t + o], & unticking "Require variable declaration" (not what I want to do!) OR possibly VBE, [alt + t + r] relating to "Microsoft excel 12.0 Object Library" as I appear to have two references to files in different locations

Keep testing at your end & you may solve it before I get back to it. If you have access to a printer (I don't), I suggest printing out the code & seeing if you can spot any flaws in the logic...

Rob

hi Easty,

It may be easier, but we pretty close, so I'll carry on. Also, we can still have a different repsonse based on the existence of any transactions for the current month, so I'll just try & get the logic right.

Sorry, I'm taking so long. We are down to the tough bit now - where there are diminishing returns & I'm finding it harder to focus on the code. I may try to print it out tomorrow & follow it through on paper.

Rob

hi Easty,

I may have cracked it!
Not prettily, but hopefully cracked
Can you please check out the attached & let me know if you spot any errors?

hth
Rob

Cool, I have my fingers crossed

How's the new baby, are you getting any sleep?

Rob