vba Find a last row, loop down, enter data to cell, check for errors

hi guys need urgent help pls

I need to find last row in my sheet and and enter the number into the indicated cell, i found how to do with MsgBox, but teacher doesnt want meassage box just a last row number in that cell
And I have to do with VBA

then I have to loop down the number of students and enter the total into indicated cell ( total number of students) again with vba. also there is 1 or 2 blank rows, so I have to avoid counting them

I have to calculate percentage in 2 coloums, i.e colomn a is 5, colomn b 10, i have to loop down and calculate 5/10*100. some rows has "abs" there so I have to edit the code to check for "abs" and avoid it and pick only numbers
also I have to check the result wit another colum f
I normally use iferror(colomuna/columb,abs)
but she wants VBA
pls help

last I have to debug following sub to display each students name

I thank you all

Please Login or Register  to view this content.

Welcome to the forum

Please take a moment to read the forum rules and then amend your thread title to something descriptive of your problem - not what you think the answer might be. (think google search terms?). Once you have done this please send me a PM and I will remove this request. (Also, include a link to your thread - copy from the address bar)

Many members search our previous posts, and thread titles play a big part of the search. I doubt anybody would do a search based on your title?

To change a Title on your post, click EDIT POST then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(note: this change is not optional )

Also...
Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

(I will add them for you - this time )

Hi cili,
_ You have explained well what you want. I get the general jist
_ On the face of it, it looks fairly easy to give you what you want.
_ But you will appreciate you have your files and stuff in front of you and have a very clear picture of everything.
_ But for someone seeing the project for the first time ( especially if they not too bright like me ! ) it is very difficult to get a clear “Picture” of what you have and what you want.

_ It would be very helpful if you can:

_ Give some test data to demo what you typically have. Keep the data to the minimum required to demonstrate all typical scenarios. ( Usually with VBA any code done for you which works on a small amount of data will work on a much larger amount of data with little or no modification. But it makes it a lot easier to work on, debug, and to show clearly in the Thread for other’s benefit if the data is kept to the minimum. Desensitize the data if necessary, ( - it up if you like, just make sure you choose the data carefully so that it has typical format and in all other ways representative of real data types . )
_ Hand fill in the results that you want the code to give you based on that particular given test data. (Sometimes it is helpful or easiest to have a “Before” Worksheet and an “After” Worksheet.
The “Before” should show what you have, and the “After” should show what you want the output to look like after running any code we do for you, based on the “Before” )

_ To get that info over to us, see for example what I wrote in Post #4 here
http://www.excelforum.com/showthread...t=#post4419333
_.. read the bit about “ ...[b]... _B) Please avoid using Images..….[b]…..“
( I prefer the Screenshot method as everyone can see clearly in the Thread, but you can also upload a file here.. when the Forum Software is working, lol...)
_..........

_. If you can get that info across, and if no one else picks up the Thread, then I will try to do it for you tomorrow

Alan

Hi

The file looks nothing like the one you explained
I expect you have uploaded the wrong file
If you upload the right one, remember to note all I said about showing me
what you have
and
what you want
Thanks
Alan

Originally Posted by cili

Hi alan,

yes this is different file.

the one I mentioned is no longer for me, I dont have to do that.
Now they have given me different task and file is this

thanks for your help

There is no file in your last Post

Hi cili

Let me see if I understand now.

You had a requirement.
When I asked for more information you responded with

Originally Posted by cili

[.....
this is the link to the file that you have asked,,,,,

That file has nothing to do with the original request.

When I questioned that you replied with:

Originally Posted by cili

....
yes this is different file.
the one I mentioned is no longer for me, I dont have to do that.
Now they have given me different task and file is this
.. pls refer to the file you saw on my earlier post
.....

And you have supplied no information anywhere about what is required to be done.

Have I understood all that correctly?

Alan

Hi cili,

I opened the file you linked above and didn't see any instructions. Are you sure you have linked the correct file? Is this for a class? What occupation are you in that you need this problem urgently solved?

Wow...

When I went to your link and opened it my default was Google Sheets. I did open it and didn't see any Developer Tab. I wonder if part of your problem is your default spreadsheet app? Excel is different than Google Sheets (if that is what they call it?). Perhaps because you put it up on the google.drive site the default is to use Sheets instead of Excel. I can't seem to get it downloaded onto my hard drive and open in Excel.

I wish I could help but I can't seem to understand the question.
BTW - I see you Private Messaged me the same info you gave to Alan above. I don't know exactly why you are sorry to me in the next message above. Explain?

This is typical of the "code" in the attachment:

Please Login or Register  to view this content.

Nothing is more frustrating than to be working on homework and to get stuck. Without guidance, you might have no hope of finishing the assignment before the deadline. The Internet can be a big help in that respect, but without learning how to get the right answer, asking others to solve your homework questions for you simply becomes a downward spiral (and it's cheating).

We do not want to contribute to you cheating yourself out of your education, but we also acknowledge that seeking assistance to learn a concept is a legitimate request.

If you are genuinely interested in receiving help in the form of tutoring or coaching, then please rephrase the title of your original post to clearly indicate you are seeking coaching or tutoring help. Any forum members (who are willing to assist as a tutor) will modify their responses accordingly to facilitate your learning. Tutors don't tell you the answers, they help you figure it out for yourself; so don't expect answers, expect suggestions, or just plain hints. Also, be specific in describing the function/formula or technique you trying to learn, and tell us what you have attempted so far. Otherwise, expect your plea for homework answers to be ignored.

Hi cili,

I got to wondering if Google Sheets allowed VBA and if it could simply work with Excel VBA. See a web answer at:

http://stackoverflow.com/questions/2...le-spreadsheet

Evidently they are different languages. I wonder which one your teacher wants you to learn. I'm pretty good with VBA but know nothing about GAS.

Did you want the Excel VBA answer? Do you have Excel or Google Sheets as that is what was seemingly posted on your link.

would this code make sense to you?

Cells(9,"L") = Cells(Rows.Count,"A").End(xlUp).Row

?

Hey Chilly,

It looks like you called me Alan and Martin (both incorrectly) in past email. You have also Private Messaged me 3 times which is like begging. You could at least gotten my name correct.!!

Hope my last suggestion makes sense and helps you.

Read these links to understand more about finding the last in a range.

http://www.ozgrid.com/VBA/ExcelRanges.htm

@ MarvinP
When I click on the OPs original link I get this, _....
GoogleFileLink.JPG

And his Dropbox link gives this
DropboxFileLink.JPG

_........and when I click where indicated the file just downloads normally and if I open it ( either through my browser or by double clicking on it in Windows Explorer or by selecting it through Open in Excel ) then it just comes up as a normal Excel File.
I confess I know nothing about Google Sheets. I and my Computer ( or the settings I have ) did not, as far as I am aware, recognise what downloaded as anything other than an Excel File.

But Google Sheets I confess i have no idea about, ......
Alan

-................................

@ cili
Hi cili

You really must make some attempt and then ask for Help
In your haste you are not helping anyone and making things worse
You registered back in October and have clearly not even read the rules yet
http://www.excelforum.com/forum-rule...rum-rules.html
( Different languages here http://www.excelforum.com/forums-rules/ )

And it does not look like you have read what protonLeah wrote in post #17.
You really must make some attempt and then ask for Help

A lot of good people here are willing to Help.
You are not asking for our help.

Somebody could answer all the questions for you, and explain very carefully what each step is doing. I do that a lot. I enjoy helping people to learn. But it would appear you would likely make no attempt to read any explanations, so our time would be wasted.

I do sympathise. If at the outset you had clearly stated your case , that you were in need of help in your homework, then you would have helped us to help you. You have just “thrown” things at us, with no clear explanation.

No more PM’s please. I am subscribed to this thread so I will be notified of, and can respond to, any request for help. In my opinion you have not yet asked for help. Spend the time you spend sending PM’s to people to make some attempt at your Homework.

If your deadline has not already expired to get your Homework done, then read the Forum rules, read this entire Thread carefully, respond to anyone who has asked anything, like MarvinP in post #20. He also asked questions in Post #12. You did not answer them all.
Make a start at doing your Homework.
Post when you get stuck, and explain what you do not understand. I will try to respond and Help.

There is no point anyway in anyone giving you a code, or codes. Would you know what to do with it or them? ) Apart from handing it in to your Teacher ! ).

Alan

Hi cili
_1) re your last PM to me:
We do sympathise, and want to help . But you are making it very, very difficult again for us to help you.
You are still not reading and answering our questions.
For example, I asked you not to PM me again.
But I just received this PM from you

Originally Posted by cili

Dear Alan,
I read the the rules and you are right about a lot of good people here willing to help. Unfortunately, I do not have enough time to sit and take a few days to work my around it slowly, hence as you can see MarvinP indicated that I am desperate. Otherwise, I would have just google search and tried few things myself. I did not sleep all night and trying to finish up other exercises such as arrays pivots etc
So I would really appreciate guys if you could help me with this.
I apologies if I offended or mislead anyone, but please try to understand and help me.
Many thanks again.

If you continue to ignore what we say to you, there can be no chance whatsoever of anyone helping you.
No one is ( or should ) just do your Homework for you.
_................................


_2) When must you have this Homework finished?



Alan

EDIT: I see you have answered MarvinP question
That is a good start Well done.
Iwill leave MarvinP to answer that for you

Hi cilli

Please do not tell any of us again that it is urgent or that you need to get it finished as soon as possible. We all know this now.

I do not think it is within my abilities or I have enough time to work with you to answer all the questions before this evening. Sorry. I am not that good - and I expect we would need a few days of hard work in communications to get you to understand the solutions.

If you wish I will help you with the second question. ( MarvinP has offered some help to the first question )
' Loop down the worksheet to count the number of companies and enter the number into Cell L17 '

If you want me to help with that, then please make some attempt yourself and tell me where you get stuck. Show me your code attempt ( Put the code In code tags – you have read the rules so I presume you know how to do that now )

Alan

P.s. MarvinP did not offer you a working code. He asked if you understood that code line

@Alan,
Citi has PMed me 4 times now. It seems he copies his post an puts it in a PM. Here is his last PM.

PHP Code: 

Huge sorry, I think I got your name wrong only once but shouldnt have happened
 you got my name wrong too not Chilly "Cili" pronounced [kili]

 thanks for your help
 Cells(9,"L") = Cells(Rows.Count,"A").End(xlUp).Row
 code did not work, it enters the numbe "1" in cell 9,l which resu lts as false answer

 thank you guys,
 hopefully you can help me and I can finish this bloody exercise by this evening

 cili 


cili didn't get that I called him Chilly, knowing that was wrong as he called me Alan and Martin in past posts.

@ cili
The code I gave you needs to be broken down to understand. It was:
Cells(9,"L") = Cells(Rows.Count,"A").End(xlUp).Row

1. The stuff on the left of the "=" sign is replaced by the stuff on the right of the equal sign.
2. Cells(9,"L") is one of the many ways of refereeing to L2 or Range("L9"). I prefer to use the Cells(Row,Column) notation in my VBA code.
3. Rows.Count is the very last row number in your sheet. It is different for earlier versions of Excel.
4. The End(xlUp) is the same as pressing a Ctrl+UpArrow key combination.
5. The final ".Row" returns the row where the cursor stops after moving up.

The big picture of my code you might understand is this in words.
Put in cell I9 the row number of going down the bottom of the A column and the press Ctrl+Up arrow keys. This would be the first non blank it hit, or the bottom entry in column A.
NOW - You said my code didn't work as it gave back the number 1. My assumption is then that you have nothing in Column A!!!!! Try putting something in the cell A5 and do the code again and it should put a 5 is I9.

After looking at the copy and paste that Alan did above it looks like you need to change the "A" in my formula to the column letter that has the most stuff in it. Perhaps this code will do what you want.
Cells(9,"L") = Cells(Rows.Count,"B").End(xlUp).Row

OH - and please stop PMing me too. I read all recent questions I answer to insure there aren't add-on questions, so PMing me is not needed. Did you know we work for free? It is completely on our own that we decide to help others. Sometimes we don't want to give fish to people who don't want to learn how to fish. <get it?> <I knew your name was cili and I still owe you one more wrong name>

Hi cili

I am a fairly junior member here, but people like Marvin share their wealth of knowledge and help people here daily at a tremendous and outstanding rate such that I am often amazed. I need hours or even days sometimes with a problem that they solve in minutes.
Certainly no need to suggest to them that they share their knowledge. They certainly do that,

I do appreciate under the pressure and frustrations you have when learning. We have all been there,. As far as VBA is concerned I still am!!! Sometimes however I learn the most by answering a Thread or two, so I try.

Sorry we could not help more. But you must appreciate you know exactly what you want. We had no idea , and initially the necessary info we needed was just not there. Like in real life , often I think, with programming requirements, most time is spent in initial communication to get the point and across and find out what is required. ( I am, By The Way not a programmer – for 2 years I did not know anything at all about computers and learnt most participation in Forums like this one )

Hence it really is useful to check out all the info on Posting.
Seriously No offence at all intended to you here, - ( I too, took a long while to understand my way around the Forum ) , but your posting really suggested you had not read or understood the Rules / posting guidelines
http://www.excelforum.com/forum-rule...rum-rules.html
http://www.excelforum.com/forums-rules/

I am sure if you read back you will agree. As you said it is easy when you know how. Similarly it is obvious to you what you want. But not to us, initially.
It made it very difficult for us to help.
Also, The great thing about the internet is that we can get to know and help people from all over the world. That is great, but it adds another communication difficulty sometimes. I do not know your native tongue. If it is not English, you may find the Rules in your language in those links above.

Good luck. I assume you are past your current Homework deadline?. You did not answer my last offer of help ( Post #26 ) , one way or the other.
So if this Thread is finished for you please mark it as solved, thanks.

Alan

P.s. A PM or two to people here is OK, that is normal. Just not too many. – We are limited on the number we may receive. So people who help so much, like Marvin, soon get their Post box filled and then miss messages. So just do not PM too much.

P.s Here how to mark a Thread as solved:

ThreadSolved.JPG