Find last "Not Empty" cell in a column - syntax not working

[fairweather]

Moderator: Tried to follow the instructions of B. Johnson in his reply to original thread "Compilation error, Sub or Function not defined" (at least module problem) and actually started getting some results. Thanks to him for the help and direction. So assume should close out as "SOLVED" since no longer a "Compilation error, Sub or Function not defined". Believe need to be more detailed in the type of problem(s) I still have, hence new thread.

Before I started making some changes in the program code logic, I got the message to display. Yeah !! but nothing further so back to the books and 'net for debugging help and among all of that I read of the "Local" window which sounded like a great tool for monitoring what was going on so opened it up and included it in the file of the latest I attached.

I entered some fictitious data to test it. (sheet 2, "Orig SH Register"). Review of first instructions showed that most was as expected, problem child appears to be the variable, "lastAddrElement" which showed 5 as the number of the last row of data when it should be 25 so I guess the syntax of "LastAddrElement = Cells(Rows.Count, 1).End(xlUp).Row" isn't right. So I would appreciate a little help for the right syntax or whatever else I did wrong. 140729 - 2115 - ExcelForum Submission.xlsm

As always, appreciate your guys time and expertise.

fairweather

[AB33]

Please Login or Register  to view this content.

picks up the last row on the active sheet,in your case sheet ReArrangedAddr,and the no of rows are 5
But if you change it to

Please Login or Register  to view this content.

you get 45 rows

[fairweather]

Moderator: Took note of AB33's comment about the "LastAddrElement = Cells(Rows.Count, 1).End(xlUp).Row" impling the active sheet was the wrong sheet and had to be explicitly identified by the changed instruction "LastAddrElement = Sheets("Orig SH Register").Cells(Rows.Count, 1).End(xlUp).Row" and the variable "LastAddrElement was now correct at 45. I had previously identified it as 25, I was wrong there were 20 more rows I did not see, below the screen bottom edge).

I could continue stepping through a few more increments of "i" until I got a different error message, "Run-time error '1004'; Application-defined or Object-defined error. I don't know what that means as far as an error is concerned or where to go look. Any thoughts or suggestions would be appreciated. Have updated the file to show the line of instruction highlighted in yellow. Will again try to attach the file and as always, "appreciate your guys time and expertise"

fairweather.

[AB33]

Fairweather,
I do not know what the code is supposed to do, but needs major overhall.
You first need to properly reference the lines with proper sheet name. For ,eg.

The reason for getting the error is this line

Please Login or Register  to view this content.

Cells(i, 1) refers to which sheet? Does it refer to sheet ReArrangedAddr or Orig SH Register?

Replace it with this line

Please Login or Register  to view this content.

[fairweather]

Moderator, possibly AB33: from previous thread

Re: Compile error: Sub or Function not defined

The intent of program or code is to convert a column of address elements to a series of rows of the address elements grouped so as to be a complete address for mail merge. The grouping is by blank rows that use to contain personal information such as number of shares to each, that personal information has been cleared so blank.

The line of code that is highlighted in yellow when the command button is clicked is the first line of the code shown below. It is the code behind the button and is for sheet "ReArrangedAddr"



copy to clipboard
Public Sub ReArrangeButton_Click()

Call RearrangeAddresses

End Sub
It is intended to call the program or code to transpose the address elements

Example:
address 1
address 2 TO address 1 address 2 address 3
address 3

Did note the comment about references and made that change and really helped. Unfortunately, books I have been using have very few pages on references or even modules, like what, where, which kinds, which to use etc.. Both subjects driving me crazy, but am trying to learn. Also saw comment about overhauling and agree, went back to flowcharting and saw repetitive nature of some of the code so converted some to a second loop. Am having problems with IF, ElseIF statements especially when done with a "NOT IsEmpty instruction" that is not going the right direction. It is highlighted on the new file attached. The second instruction following also does not appear to be copying the cells(i,1) to the other sheet "ReArrangedAddr"

Hope you can follow all that. Again, appreciate your taking time and your expertise

fairweather

[fairweather]

from thread: Time or how to resubmit a question? mikerickson said it was probably okay to bump this thread since I had not had a reply in 4 days and that probably preferable to duplicating the thread

Moderator's note: The bump is fine, you only need to wait a day to bump

Also, no need to repeat your last post in the bump, all you need to do is *** something (anything really) that will make the thread jump back to the top of the list (thanks for asking if your other thread regarding bumping)

[fairweather]

I am getting confused and lost.

I made a reply that was largely a repeat of a most recent reply post along with a reference to the file (then shown immediately above) that attempted to clarify some points because two more replies had been posted. First by a moderator and also by me so I made a few changes and then ran the program in debug, stepping into the code until tried to explain what where I thought the problem code / instruction was highlighted. If the program is attempted to run to completion, get an "overflow" error because the counter "i" overruns the limit of an "Integer" variable, "32,XXX. Maybe I made a mistake by submitting the code as having been run in debug for 3 iterations of the loop before submitting it , but I did that to try to show what and where I thought the problem instruction was and it got highlighted because it failed on part of the IF, . . . ElseIf instruction and then if continued to be run in debug mode, the problem continues on the wrong path until the counter "i" fails because it exceeds the limit of an "Integer" variable. Refer to the data to see that line 10 which is iteration #3 is "NOT IsEmpty" and as TRUE, should go a different path than the first 2 iterations

And now someone or somehow the explanation in my reply has been deleted or the explanation was somehow wiped out. This is getting terribly frustrating. I don't know how to make an explanation if they get wiped out. In the logic of an IF . . . ElseIf instruction, how do I attempt to show the problem if it gets wiped out? In the code I think the problem instruction is "ElseIf (Not IsEmpty(Cells(i, 1))) Then" I also think the second instruction following "Sheets("ReArrangedAddr").Cells(iRow, jColumn) = Cells(i, 1)" also is wrong, wrong syntax? because it is not copying the value to the other worksheet "ReArrangedAddr". I will reattach the file. I will let you try your hand at debugging, only to say that I think on the third iteration of "i" it fails, (at that time "i" is 3). Else please tell me how to demonstrate or explain.

fairweather

[FDibbins]

Hi fairweather, sorry you are feeling frustrated here

Im not sure what you mean about replies being deleted, the only thread that has any editing - by anyone (it was me) is post # 6 where I added a comment regarding bumping and regarding repeating posts.

It is not just moderator's that answer questions here, all members help...and are helped by...all other members as and where they can. If you are not getting an answer, try another bump, or perhaps try a different way of explaining your question

[protonLeah]

Please Login or Register  to view this content.

Note: It's best not to use Cells and Range without a sheet reference. It makes the code confusing, and if run from a standard module vs. a sheet module can fetch bad data since hey will be tied to the active/visible sheet.

Also, on your Orig... sheet you have text below row 40 so you get LastAddrElement = 45 instead of 25.

[fairweather]

Forum:

Want to thank FDibbins and Protont.eah for their help and suggestions. Used much of it but as well got me to thinking along different lines and that's one way we learn

By the way, do either of you know of a good source explaining "modules". I'm still very confused about the types and how and when do the different types get used. I thought the code for a given worksheet would be in the sheet module for that sheet, but apparently not so. Set up by one of the moderators to be in what looks like a general module or whatever it is, listed on top of the project tree diagram. Thanks again for the time, help and expertise.

fairweather

[fairweather]

second try at marking this thread as "SOLVED". Again my thanks to the moderators and others that contributed, especially FDibbins and Protont.eah

fairweather

[FDibbins]

To mark a thread SOLVED, use the Thread Tools at the top of the page. I have done it for you this time