Find function in nested loop breaking down - not on first time through loop

**adamstarr12** · 10-09-2014, 09:06 PM

I really appreciate the site's contributors' offering your experience and expertise.

So, I am trying to perform a somewhat complex task with the loop(s) & If(s) below; to briefly describe: if a row's column L = "UE", then get the dollar amount in column T and find its negative equivalent in column T; If found, see if several other things match, and if they do then put a number in column 32. Seemed pretty simple to me starting out, but it got tricky for me..

When I run the loop, on step #13 it comes back

The excel file and the steps that the macro are trying to accomplish are attached.

**jaslake** · 10-09-2014, 11:11 PM

Hi adam

Well, have to admit, you've got me a bit intrigued. I'm an old bean counter (CPA Retired). Have done thousands of reconciliations in over 35 years in the business.

You posted quite a dissertation in your Word Document. What you didn't say is what works and what doesn't.

From your Thread Title it appears things are going South in Step 13. Tell me a bit about yourself...and...tell me in your own words...what is Step 13 supposed to be doing?

When it blows out in Step 13 these evaluate as follows:

TCfind = ActiveCell.Offset(0, 8).Value2 evaluates to 0
LCfind = ActiveCell.Offset(0, 10).Value2 evaluates to 0
GCfind = ActiveCell.Offset(0, 12).Value2 evaluates to 0.02
CurrentPC = ActiveCell.Offset(0, -6) evaluates to P3091
CurrentRow = ActiveCell.Row evaluates to 27

So, what are we doing with this information?

**adamstarr12** · 10-11-2014, 12:13 AM

Hey John,

Thank you for your time and interest! My story and task has remarkable parallels to your story; I am a "Bean Counter" (haha) and we recently did an accounting system "upgrade" and I am sorting through several accounts trying to label any entries that haven't been cleared yet (with the colors in the word doc).

You have definitely pinpointed the problem I am having - in Step #13 (#14,etc haven't been tested). One thing I am trying right now: Before running the macro, rewrite the "PreviousMatch = MatchRow + 1" in step #13 to be "PreviousMatch = MatchRow", and then a different error comes up much later in the dataset (same loop).

TCfind = ActiveCell.Offset(0, 8).Value2 evaluates to (13,221,361)
LCfind = ActiveCell.Offset(0, 10).Value2 evaluates to (8,213,556)
GCfind = ActiveCell.Offset(0, 12).Value2 evaluates to (13,221,261)
CurrentPC = ActiveCell.Offset(0, -6) evaluates to P4209
CurrentRow = ActiveCell.Row evaluates to 152

Step 13 is trying to find two entries within the dataset that offset each other in all three currencies (TransactionC,LocalC,GroupC) - one with document type "UE" and the other "AB"; the entries must be within the same "profit center" (P****) and neither can have been identified as a match to any of the steps before 13 (so column "activecell.offset(0,20) = "" - in the If statement)

I tried to set the loop up to find the number of offsetting TC amounts, then to search through each of those matches to see if the other criteria are matched as well.

I've tried very hard to get this to work, and honestly believe I should pay someone for their efforts to have this work. Please let me know what you think

Thanks John!

Adam

**jaslake** · 10-11-2014, 04:04 PM

Hi Adam

Please give some examples of records in your Data File that would meet the test of Step 13. I looked and could not see anything obvious.

Also, explain this Code

Please Login or Register  to view this content.

Should it not be

Please Login or Register  to view this content.

**adamstarr12** · 10-12-2014, 08:31 PM

Hey John,

Unfortunately, I deleted a lot of the data when I was uploading the file here on the site, and there are no examples of "#13" left in the file; that defeats the purpose of the data set, I apologize. An example is: (with many columns deleted)

Example#13.JPG

Regarding whether the it should be "<>" or "<", I think it should be as it is because I only put that in there to prevent vba multiplying zero by -1. All other numbers should be multiplied by -1 though.

Adam

**jaslake** · 10-12-2014, 09:27 PM

Hi Adam

I'll work with this with you if you'll incorporate the data into your example file. It's not possible to test otherwise.

**adamstarr12** · 10-12-2014, 10:41 PM

John,

Thank you very much for working with me!

I apologize - I didn't realize I could change my data that I originally uploaded. I changed the data around so that there are three matches now:

UE:
MatchExampleUE.JPG
AB:
MatchExampleAB.JPG

The file above reflects these matches -(1) rows 158 & 153 (2) rows 149 & 187 (3) rows 195 & 220

Adam

**jaslake** · 10-13-2014, 01:28 PM

Hi Adam

I'm able to find only one matching Record in the Data File...Records 153 and 158. Records 149 and 187 have different Dollar Amounts. Records 195 and 220 are AO Records.

There's two different versions of Step 13 Code in the attached. Try them both (one at a time of course)...they appear to give the same results.

I've truncated your original Code to run only through Step 13. You can change that of course once Step 13 is performing as required.

**adamstarr12** · 10-14-2014, 07:38 AM

Hey John,

I really appreciate your time and expertise for making this work. The macro you assembled works seamlessly! I have one question though..

The file I downloaded has the 'Old_Code' and 'New_Code', but also has some other macros that I'm not able to get rid of and seems engrained in vba - no matter where I go. Here is a screen shot of the extra macros:

OtherMacros.JPG

And when I try to delete it, I get this screen:

ATP.JPG

These sort of things make me very uneasy; however, it may be due to my lack of understanding. Could you please explain what these macros are for and why I can't get rid of them?

Again, thank you for your time.

Adam

**jaslake** · 10-14-2014, 09:30 AM

Hi Adam

None of these three files are an issue. ATPVBAEN and FUNCRES are part of Analysis Toolpack Addin. Google on either of the File names to learn how to manage these Files.

PERSONAL.XLSB was created either by you or someone who previously used your Computer. Google on the File name to learn how to manage it.

**jaslake** · 10-14-2014, 09:32 AM

Hi Adam

You're welcome...glad I could help.

If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED

Find function in nested loop breaking down - not on first time through loop

LinkBack

Thread Tools

Rate This Thread

Display

Find function in nested loop breaking down - not on first time through loop

Re: Find function in nested loop breaking down - not on first time through loop

Re: Find function in nested loop breaking down - not on first time through loop

Re: Find function in nested loop breaking down - not on first time through loop

Re: Find function in nested loop breaking down - not on first time through loop

Re: Find function in nested loop breaking down - not on first time through loop

Re: Find function in nested loop breaking down - not on first time through loop

Re: Find function in nested loop breaking down - not on first time through loop

Re: Find function in nested loop breaking down - not on first time through loop

Re: Find function in nested loop breaking down - not on first time through loop

Re: Find function in nested loop breaking down - not on first time through loop

Thread Information

Users Browsing this Thread

Similar Threads

Find function in nested loop breaking down - not on first time through loop

Next without For error in nested loop - Escaping a Nested Loop?

Breaking a Find Loop

For each loop nested; getting stuck on one cell in first iteration of nested loop

[SOLVED] Trying to execute function after negative if conditional nested in For each loop

Bookmarks

Bookmarks

Posting Permissions