opposite of nothing

[Luis939]

hey,
how would i write the opposite of Is Nothing as in, to test if activecell is something instead of nothing

I tried
if not activecell is nothing then....
but that didn't work

any ideas?? thanks!

 

[SkipVought]

Well, the problem is that ActiveCell IS ALWAYS SOMETHING. It has all kinds of properties associated with it!

Are you wanting to test for the presence or absence of a VALUE?

If IsEmpty(ActiveCell.Value) Then...

or

If Not IsEmpty(ActiveCell.Value) Then...

???

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Luis939]

Actually i shouldn't have written activecell
i want to test if a range is something
basically im running a for loop that will test every sheet to see if it has a certain value (by running the .find method)
so if the range is found i want to continue the for loop so that it does the rest of the instruction, but if the range is not found i want it to go back to the beginning of the for loop with the next i

i keep getting errors, i dont know if i should run 1 or 2 loops, how would i tell it to go back tothe top of the loop
without using "next i" becuase then i'll have 2 next i instructions and get an error because one of those next i doesnt have a for loop with it, thanks!

 

[ssVBAdev]

Here's an easy whay to "re-do" a loop, but I'm not sure if that's what you are looking for in terms of your original post.

StartHere:
For ...
Blah, Blah
If Not .... Then Goto StartHere
Next

********************
What's the best way to get the answers you need?? See FAQ222-2244 for details!

 

[Luis939]

oh the blasted goto statement i was warned to not touch lol, but nah the object is to also increment the counter, so by repeating the loop ill start back at increment = 1 which is where i wanted to start becuase im starting with the first worksheet, but thanks for replying

 

[ssVBAdev]

I am having trouble following what in hech your last post was supposed to mean (it IS Friday, isn't it!??!) But...

You said:
"...the object is to also increment the counter, so by repeating the loop ill start back at increment = 1 which is where i wanted to start becuase im starting with the first worksheet"

Which is it? Do you want to go back to the first worksheet if the .find fails or do you want to continue on to the next sheet if the .find fails?

Or, do you want to go back to the first sheet if the .find fails and start with a new .find?

Or, am I completely missing the point (which by the way still does not address the original point of this thread. Did Skips "IsEmpty()" response help you out?)

********************
What's the best way to get the answers you need?? See FAQ222-2244 for details!

 

[SkipVought]

Mebe it IS Friday

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Luis939]

basically starting at the first worksheet, if the .find method failed then move on and test the next worksheet, but if it was succesful, then just continue on to the rest of the code in the for loop...this is how i finally did it, through recursion, if theres a better way then i would love your suggestions

Sub outtersub()
forloop 1
End Sub

Sub forloop(j As Integer)
For i = j To ThisWorkbook.Sheets.Count
Set testsheet = ThisWorkbook.Sheets(i).Range("A:A&quot.Find(what:="Usage", lookat:=xlwhole, searchorder:=xlByColumns)

If testsheet Is Nothing Then forloop j + 1

'Rest of Instructions

Next i

End Sub

Thanks guys!

 

[ssVBAdev]

Well, in that case, using the forbidden Goto statement try somthing like this (pseudo code):


For i = 1 To ThisWorkbook.Sheets.Count
Set testsheet = ThisWorkbook.Sheets(i).Range("A:A&quot.Find(what:="Usage", lookat:=xlwhole, searchorder:=xlByColumns)

If testsheet Is Nothing Then Goto NextSheet

Blah, Blah, Blah

NextSheet:
Next i



********************
What's the best way to get the answers you need?? See FAQ222-2244 for details!

 

[Luis939]

yea that actually is better, sorry for not being clear before, thanks!