Logic for a FOR loop

H

Hari

Hi,

I want to exit a FOR loop if a particular cell's value meets one of the
target values and would not want to wait till all conditions are checked.

No.of possible target /desired values are 19 and if the cells' value is not
equal to any of those 19 then color the cell with a yellow color and
increase the count for Nooferrors by one.

I just wanted to ask whether the LOGIC in the following loop is correct. (
My syntax seems to be fine as Im able to compile the project without any
problem)

Please advise.


For m = 0 To 18
definedtype = False
temp = UCase(Sheets(sheetName).Cells(n, 3))
If reasons(m) = temp Then
definedtype = True
Exit For
End If
Next m

If definedtype = False Then
Sheets(sheetName).Cells(n, 3).Interior.ColorIndex = 6
NoOfErrors = NoOfErrors + 1
End If


Regards,
Hari
India
 
F

Frank Kabel

Hi
in your For Next loop you also use 'n' as row_index. This value is not
changed within the loop so you could also use the following:

definedtype = False
temp = UCase(Sheets(sheetName).Cells(n, 3))
For m = 0 To 18
If reasons(m) = temp Then
definedtype = True
Exit For
End If
Next m
If definedtype = False Then
Sheets(sheetName).Cells(n, 3).Interior.ColorIndex = 6
NoOfErrors = NoOfErrors + 1
End If
 
H

Hari

Hi Frank,

Thanx a lot. I have been foolish enough to keep the same inside the loop.
( I included "Exit for" which is a new one for me so that processing time is
reduced but didnt see the more obvious one pointed out by u.)

I have one more request. As u request to many people in ur replies I dint
want to multipost the same question.

I posted a doubt "Automatic file opening procedure seeming to hold cache" 3
or 4 days back..

I have a "mortal fear"that nobody went thru that post.

If possible please go thru the same. I have tried some offline resources but
havent been able to resolve the same.

Regards,
Hari
India
 
F

Frank Kabel

Hi
I read this post and had never encountered such a thing. So no answer
from my side :)
And I think this is true for all other people also. If you have posted
a question and haven't received an answer lets say after 1-2 days you
may report your question (stating that this is a repost) and ask the
question again
 
H

Hari

Hi Frank,

Thnx for ur suggestion. In future if it doesnt happen again ( not getting a
response) I will include repost in the subject.

One more request ( Im becoming a pest!!)

Im 3 or so weeks old with newsgroups. I hit upon the concept of newsgroups
purely by luck.

To be frank ( Im sorry, i mean the Verb not the noun ) I dunno whats the
best way to approach the newsgroups.

Please note that I posted this query in outlookexpress newsgroup about
getting the best out of Excel newsgroups but I couldn't get an answer to the
things I desired to know

Im asking from the perspective of how should one read/access the newsgroups
so that one is able to get the maximum out of it.

Presently I want to learn only Excel and at my home and workplace I have set
up outlook express with Microsoft's excel groups. I see that using that
method one can only access messages which are maximum 4 or so months old.

If one uses the link
http://www.microsoft.com/office/community/en-us/default.mspx?pg=1&lang=en&cr
=US&guid=&dg=microsoft.public.excel.programming&fltr=&exp=1
to access newsgroups it is somewhat the same effect.

Google groups is good but I have to be online to read the messages.What I
want to know is 2 things :-

a) How do I configure Outlook express so that Im able to download all the
messages in Google at one go. That is the 1 million or so messages to be
downloaded on to my computer( Sort of silly but dont want to miss
anything..)

b) How do u as a excel guru approach the newsgroups. Like do u access
messages from outlook or do u go online to google's site or do u go online
to Microsoft's site. Please tell me the same if possible. This will help me
in being the most productive in getting the best possible experience. ( I
did want to mail u asking about the same but got alerted by 2 statements I
have encountered generally in many of the people's signature in NG's. One is
" Please keep the discussion in the newsgroup" and second "Unsolicited mails
will be deleted" )

Eagerly awaiting for ur guidance,

Regards,
Hari
India
 
M

Marcotte A

Hari said:
Hi Frank,
Google groups is good but I have to be online to read the messages.What I
want to know is 2 things :-

a) How do I configure Outlook express so that Im able to download all the
messages in Google at one go. That is the 1 million or so messages to be
downloaded on to my computer( Sort of silly but dont want to miss
anything..)
Click to expand...

I'm not Frank, or an Excell guru, but I'll help with what I can. I'm not familiar with Outlook Express so I can't help you with that, but see below.
b) How do u as a excel guru approach the newsgroups. Like do u access
messages from outlook or do u go online to google's site or do u go online
to Microsoft's site. Please tell me the same if possible. This will help me
in being the most productive in getting the best possible experience. ( I
did want to mail u asking about the same but got alerted by 2 statements I
have encountered generally in many of the people's signature in NG's. One is
" Please keep the discussion in the newsgroup" and second "Unsolicited mails
will be deleted" )
Click to expand...

I access this newsgroup through the Microsoft website (www.msdn.microsoft.com). I too use it to help develop my skills while at work, and to get answers to specific questions I have regarding spreadsheets I'm currently working on. I spend a lot of time just browsing through past posts looking for things that are pertinent to whatever I'm working on. There is a search engine that can be helpful, but it's not perfect. In addition to the multilple links that Frank and others post, there are a lot of links on the MS site to articles and such about different aspects of XL and other MS apps. The one thing I haven't found yet is a newgroup for more general questions such as this (i.e. general newgroup etiquette).

As for maximizing your return for time invested in newsgroup reading, I would look for links to good beginners pages. Also, buying an Excel or VBA book is a good idea, as a good one will explain ideas behind doing things a certain way, or introduce you to concepts that you were unaware of. As mentioned, searching or browsing past threads may lead to a topic related to what you are working on, and if all else fails, you can always post specific questions.

Hope this helps.

Marcotte
 
B

Bob Phillips

Hari,

Surely, if it compiles okay, then you test it, and see if the results are as
expected. You won't learn by asking us to validate your code before you have
even tried to see whether there is a problem or not.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
H

Hari

Hi Bob,

Im really really sorry for not mentioning this in my initial post.

I tried the code on my own and it did work.

But being new to this I wanted to be very very sure ( as the format will be
a standard one which will be used by others to give data to me)
that what I wanted to do and what I have translated in to VBA is the same or
not. I was thinking I may have overlooked some scenario in this, like I
wasnt fully confident about having a Exit for statement within a If
statement.

(Im now thankful that I did ask u folks because Frank suggested one
improvement in my code to speed up which I had overlooked.)

Thanx again,
Regards,
Hari
India



On the
 
H

Hari

Hi Marcotte,

Thanx a lot for sharing ur ways of working.

I will definitely buy a book and will try to make the most of NG, Book and
yes MS links which I havent explored as of now.

Thanx again,
Regards,
Hari
India


Marcotte A said:
I'm not Frank, or an Excell guru, but I'll help with what I can. I'm not
Click to expand...
familiar with Outlook Express so I can't help you with that, but see below.
I access this newsgroup through the Microsoft website
Click to expand...
(www.msdn.microsoft.com). I too use it to help develop my skills while at
work, and to get answers to specific questions I have regarding spreadsheets
I'm currently working on. I spend a lot of time just browsing through past
posts looking for things that are pertinent to whatever I'm working on.
There is a search engine that can be helpful, but it's not perfect. In
addition to the multilple links that Frank and others post, there are a lot
of links on the MS site to articles and such about different aspects of XL
and other MS apps. The one thing I haven't found yet is a newgroup for more
general questions such as this (i.e. general newgroup etiquette).
As for maximizing your return for time invested in newsgroup reading, I
Click to expand...
would look for links to good beginners pages. Also, buying an Excel or VBA
book is a good idea, as a good one will explain ideas behind doing things a
certain way, or introduce you to concepts that you were unaware of. As
mentioned, searching or browsing past threads may lead to a topic related to
what you are working on, and if all else fails, you can always post specific
questions.
 
B

Bob Phillips

To be frank ( Im sorry, i mean the Verb not the noun ) I dunno whats the
best way to approach the newsgroups.
Click to expand...

That's an adjective, not a ver b or a noun (vbg>
Im asking from the perspective of how should one read/access the newsgroups
so that one is able to get the maximum out of it.

Presently I want to learn only Excel and at my home and workplace I have set
up outlook express with Microsoft's excel groups. I see that using that
method one can only access messages which are maximum 4 or so months old.

If one uses the link
http://www.microsoft.com/office/community/en-us/default.mspx?pg=1&lang=en&cr
=US&guid=&dg=microsoft.public.excel.programming&fltr=&exp=1
to access newsgroups it is somewhat the same effect.
Click to expand...

Accessing the NGs through a webpage is a pain IMO, far too slow.. I think
the on ly way to gop is to configure a newsreader to access the news servers
directly.
Google groups is good but I have to be online to read the messages.What I
want to know is 2 things :-

a) How do I configure Outlook express so that Im able to download all the
messages in Google at one go. That is the 1 million or so messages to be
downloaded on to my computer( Sort of silly but dont want to miss
anything..)
Click to expand...

Don't even try, it is far too many and you will never be able to absorb
everything that is given there. Work smarter not harder, look for topics
that may have an interesting subject that catches your imagination, or ones
that have many responses, especially if the thread levels get very deep as
that often means that the topic is unravelling, or maybe even pick a
favourite responder and look for their answerr (not Frank though, he's tgoo
damned prolific).

b) How do u as a excel guru approach the newsgroups. Like do u access
messages from outlook or do u go online to google's site or do u go online
to Microsoft's site. Please tell me the same if possible. This will help me
in being the most productive in getting the best possible experience.
Click to expand...

Do use Google to see if you question has been addressed in the past (most
times you will find it has) before posting to the NGs, as this will save a
post, and will help you to stand on your own feet.
( I
did want to mail u asking about the same but got alerted by 2 statements I
have encountered generally in many of the people's signature in NG's. One is
" Please keep the discussion in the newsgroup" and second "Unsolicited mails
will be deleted" )
Click to expand...

It's best to keep it in the NG as others can benefit then as well, but if
you really mailing direct, ask the person. Most are amenable to that, it's
unsolicited mail that really galls.
 
H

Hari

Hi Bob,

Thanx a lot for ur nice suggestions.

I will search Google. Only problem is ( which is a major one I think) it
becomes very difficult to use the correct keywords for finding a post
similar to my problems. Its like if one is a little more exposed to
VBA/Excel then in the case of a problem one may know what the solution will
look like but at the same time one might not know how to effect such a
solution. In that scenario knowing that the solution will involve using such
and such function I may use the name of the function in the keyword. For ex.
if I had come across "Workbook_BeforeSave" and I wanted something similar to
"workbook_afteropen" but couldnt find in VB help or in google and later
learnt from NG that it is "Workbook_Open"

It seemed that the more one knows about the topic of one's search more are
the chances for finding what u want.But one can learn things better only if
they get answers for what they are searching. ( A sort of Catch 22
situation)

Frankly speaking (!!) I wanted to write "i mean the adjective not the noun "
but had a discussion with coworker on this who said the word frank is a verb
and I dissented and in some other frame of mind ended up writing verb ( slip
of the finger ?). I meant noun because I thought the name Frank is a noun

Regards,
Hari
India
 
I

ijb

I concur that the hardest part is often correctly phrasing the question in
the newsgroup search engines.
For what it's worth, I tend to use groups.google.com to search newsgroups
and outlook express connecting to either news.btinternet.com or
news.microsoft.com.
Happy hunting. If you don't find the answer to the relevant question, try
changing a few words and searching again, often you will find interesting
articles along the way.

--

HTH

ijb


Not MCSD, MVP, TLA, P&P, PCMCIA, etc just trying to help
 
Top