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

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

--
Regards
Frank Kabel
Frankfurt, Germany


Hari wrote:
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

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

"Frank Kabel" wrote in message
...
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

--
Regards
Frank Kabel
Frankfurt, Germany


Hari wrote:
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

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

--
Regards
Frank Kabel
Frankfurt, Germany


Hari wrote:
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

"Frank Kabel" wrote in message
...
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

--
Regards
Frank Kabel
Frankfurt, Germany


Hari wrote:
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

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/comm...g=1&lang=en&cr
=US&guid=&dg=microsoft.public.excel.programming&fl tr=&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


"Frank Kabel" wrote in message
...
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

--
Regards
Frank Kabel
Frankfurt, Germany

"Hari" wrote:

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..)

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" )

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

"Hari" wrote in message
...


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

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/comm...g=1&lang=en&cr
=US&guid=&dg=microsoft.public.excel.programming&fl tr=&exp=1
to access newsgroups it is somewhat the same effect.


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..)


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.

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" )


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.

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)

"Hari" wrote in message
...
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

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
"Bob Phillips" wrote in message
...
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)

"Hari" wrote in message
...
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