Hi all,

My code used to work when i only had one sheet in the workbook, it done
Selection.Find(What:=Interiminput.regint.Value, LookIn:=xlValues,
SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False).Activate

now that i have added more sheets it comes up with "object variable or
with block variable not set" when it gets to the code.

This must be real simple but I am banging my head against the wall!

any ideas?

Duncan

First if not already implemented put

Option Explicit

at the very top of your module.
Compile your project and make sure that every variable is declared. May be
that you have mistyped the name of a variable at some point and it doesn't
know what it is.

Second, if your Selection spans multiple worksheets then this might be
causing your problem, or if your find returns multiple worksheets, that too
could be the problem. I.e., how does the system activate multiple
worksheets? I don't know, never tried, never seen that happen. Might also
fix the problem if you use 'Call' at the beginning of the code line.


"Duncan" wrote:

Hi all,

My code used to work when i only had one sheet in the workbook, it done
Selection.Find(What:=Interiminput.regint.Value, LookIn:=xlValues,
SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False).Activate

now that i have added more sheets it comes up with "object variable or
with block variable not set" when it gets to the code.

This must be real simple but I am banging my head against the wall!

any ideas?

Duncan

Is Interiminput the codename of a sheet.

Do you want to search on multiple sheets

Dim sVal as String, sh as Worksheet
Dim rng as Range
sVal = Interiminput.regint.Value
for each sh in thisworkbook.worksheets
sh.Activate
set rng = sh.Cells.Find(What:=sVal, _
LookIn:=xlValues, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
if not rng is nothing then
rng.Select
MsgBox "Found!"
exit sub
end if
Next
if rng is nothing then
msgbox sVal & " was not found"
end if

--
Regards,
Tom Ogilvy


"Duncan" wrote in message
oups.com...
Hi all,

My code used to work when i only had one sheet in the workbook, it done
Selection.Find(What:=Interiminput.regint.Value, LookIn:=xlValues,
SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False).Activate

now that i have added more sheets it comes up with "object variable or
with block variable not set" when it gets to the code.

This must be real simple but I am banging my head against the wall!

any ideas?

Duncan

could it be that im doing this in a sub and not a module? Im new to VBA
and dont understand about variables and suchlike, I only know by
finding out what works and what doesnt!

I have tried putting option explicit in one of my modules which holds
the functions that i use but your comment made me think that perhaps i
have structured it wrong to start with? The selection.find activates
when a button is pressed on a form, this is within the sub which also
goes on to do all the rest of the code.

This is giving me a headache, I think perhaps i should have defined the
parameters of the find within a module and called the find from the
sub, trouble is it worked fine until ive started to add extra
functionality to this project (i.e extra sheets which are used as
templates for a print command upon submission of data) and its all
started to unravel, once you get this far in its hard to start again
from scratch!

Tom,

Not long to write this message, ill follow this up in the morning.

Thank you for your post, I will try your code tommorow and see if I can
manipulate it.

In answer: No, only want to search within a worksheet called data.
Interim input is the name of the form which holds the texbox which is
the value i am looking for so that when found I can add more info to
that line.

Anyway i have to rush off so i will re-read the posting tommorow AM,
thank you both for your time.

Duncan

Assuming by form you mean a Userform named Interiminput with a textbox named
regint

Private Sub Commandbutton1_Click() '<== for example
Dim sVal as String, sh as Worksheet
Dim rng as Range
sVal = Interiminput.regint.Value
set sh = Worksheets("Data")
set rng = sh.Cells.Find(What:=sVal, _
LookIn:=xlValues, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
if not rng is nothing then
sh.Activate
rng.Select
MsgBox "Found!"
Else
msgbox sVal & " was not found"
end if

End sub

--
Regards,
Tom Ogilvy


"Duncan" wrote in message
oups.com...
Tom,

Not long to write this message, ill follow this up in the morning.

Thank you for your post, I will try your code tommorow and see if I can
manipulate it.

In answer: No, only want to search within a worksheet called data.
Interim input is the name of the form which holds the texbox which is
the value i am looking for so that when found I can add more info to
that line.

Anyway i have to rush off so i will re-read the posting tommorow AM,
thank you both for your time.

Duncan

Okay, perspective has been provided... I understand that you are a new user.
Some basics then...

A module, is a location in which code can be stored and operated on.

There are modules and class modules. Class modules are a special type of
module, that allow you to group data together creating your own class (Like
Integer, Long, worksheets... These things are in essence classes, as they
have their own rules of usage, their own stored data, etc...)

Within any type of module, you can have sub routines and functions...

Functions will perform actions and return a value. You do not always have
to store the value returned but it will/*must* return a value nonetheless..
(To call a function whose return value you do not want to use, you must
preface the call with the word Call (I.e., Call ReturnTwoTimesTwo() ) This
function would be written to return the value 4 and is used here as an
example:

Public Function ReturnTwoTimesTwo() as integer
ReturnTwoTimesTwo = 2 * 2
end function

A subroutine, does not return a value... Persay... It may modify a value
that is passed to it.. (A potential concern of the programmer.) But it will
perform actions either with or to the data.

Subroutines can call other functions or other subroutines. Functions can do
the same thing. (Yet another concern of potentially getting into an infinite
loop.)

Modules provide a handy way of grouping similar data/operations.

VBA/Visual Basic, also provide Userforms. Userforms also have areas with
code, and can contain subroutines and functions. Typically code in the
userform code is specific to the userform and not *usually* something that is
called from other modules or userforms.

This would be a good point to talk about public and private functions, and
subroutines. Public means, that any module, userform, or worksheet could
use/access the code. (This potentially means as discussed above, that data
could inappropriately be revised by the programmer depending on how it is
passed from one area to another. This is more of an issue when multiple
programmers are working on a single project, but could also be a reason why
the final output doesn't look like what was intended/expected by a single
programmer.)

Private subs and functions typically mean that they can not be called by any
other module, form or worksheet other than the one in which the private code
exists. This is not true though for worksheets. If a user "knows" about a
private function, they can use it on the worksheet, but if the name of the
private code is not known, then they would have to go find the name to be
able to use it.

Variables are like in algebra. X is a variable. If X is defined as an
integer, (Dim X as Int) then X can only be a whole number. Any programming
language also puts a limit on the value of an integer. In some languages it
is larger than others. Typically it is a number that can be equally negative
as positive. However if you try to set X = 3.6 it will be either stored as 3
or 4 depending on the rules of that language or it may say that there is an
error. Again depending on the language...

In some cases a wider range of an integer like value is desired. For
example if you wanted to refer to the last available row of worksheet I think
you must declare the integer as a long integer (Dim X as long). That just
means it uses more "memory" to declare the variable.

That leads to something else. Every data type requires a specific amount of
memory, some data types expand, and you can define your own data types that
will expand or even shrink. This was significantly more an issue (memory
management) in the older days, than now, but is still an issue even today.
For every declaration there is time associated to the amount of memory that
is used, the amount of resources to set aside that memory, and the amount of
work necessary to free that memory when done with the data.

So for example, if you needed to know if something were true or false, you
could say it's equal zero or one, or you could use a boolean, and say true or
false.

Part of what you are talking about (putting the find in it's own
function/sub routine) is called object oriented programming, or at least as I
have come to understand it and though it may not fully object oriented, at
least broaches the boundary of it. I typically will pull a "routine" job out
of code because I know that it will be used in a number of places. One
reason to do that is, picture the find function being used in 10 places.
Each time it's finding the same piece of data, from the same worksheet. Now
one day I think... You know, this find function should be looking at a
different worksheet, because I have rearranged my data. I have to find every
instance that I did a find on that worksheet and replace it with the new
worksheet name.... Okay, if I had put the find all by itself, I could change
it one time, and be done...

Then again, I could make the find a little more robust. Say I pass to the
find "algorithm" the sheet on which I want to find a particular object, or I
pass to it what I am looking for... Again all depending on where I am headed
with the particular program some of those attributes may be necessary..
However, if you go "too far" then you are basically doing the same find as if
you just kept it in line as necessary. But then again, it may be "prettier"
to you and you can reuse it in other applications. (This is part of the
looking forward to adaptability and revision.)

It's definetly hard to start from scratch. You may not need to do that
persay. Sounds/looks like you might need to sit down with the code in front
of you, a piece (or pieces) of paper, and step through the code, documenting
the value(s) of all variables through your code and identify what action(s)
are actually occurring as compared to what you expect to occur. Try to look
at it with a fresh set of eyes, do not document what you expect, document
what the thing is doing. Remember garbage in garbage out and that the
computer only does what you tell it to do...

Hopefully this plus other help provided will get you moving forward.


"Duncan" wrote:

could it be that im doing this in a sub and not a module? Im new to VBA
and dont understand about variables and suchlike, I only know by
finding out what works and what doesnt!

I have tried putting option explicit in one of my modules which holds
the functions that i use but your comment made me think that perhaps i
have structured it wrong to start with? The selection.find activates
when a button is pressed on a form, this is within the sub which also
goes on to do all the rest of the code.

This is giving me a headache, I think perhaps i should have defined the
parameters of the find within a module and called the find from the
sub, trouble is it worked fine until ive started to add extra
functionality to this project (i.e extra sheets which are used as
templates for a print command upon submission of data) and its all
started to unravel, once you get this far in its hard to start again
from scratch!

Yep, Just tried that code and with a few changes to my ifs ive got it
working a treat, I see you used the cells.find instead of the
selection.find and i suppose doing it that way allowed you to put
(worksheetname-cells.find etc etc) which must be ensuring that it has
the right worksheet, I tried doing it worksheets("name").selection.find
but that didnt work and i didnt know about the cells.find.

Thank you for you posting GB which i am going to keep re-reading until
it sinks in!

Many thanks to you both

Duncan