One way is to pass it is like this:

Option Explicit
Sub testme01()
dim mySheet as worksheet
dim res as boolean
set mysheet = thisworkbook.worksheets("sheet1")

res = myfunc(wks:=mysheet)
if res = false then
msgbox "Failed"
else
msgbox "worked"
end if
End sub

Function myFunc(wks as worksheet) as boolean
if wks is nothing theng
msgbox "it's nothing"
myfunc = false
else
msgbox wks.name
wks.range("A1").value = "hi there"
myfunc = true
end if
end function
==========
Another way to make the variable visible to any other procedure in the
workbook's project:

In a General module:
Public wks as worksheet

And it you set it in one procedure (and never destroy it elsewhere), then it'll
be visible in all other procedures.


msnyc07 wrote:

Hmm of wsCert is not defined

HOWEVER when the code jumps to the next function it is because that is where
the other Cells are written

How can I pass a variable from the function where it is not defined to the
one it is?

In the one it is not I need to add a variable inside

If CASE1
CurrentVariable=X
Else
CurrentVariable=Y

so I want do do a

If CASE
CurrentVariable=X
New Variable = A
Else
New Variable = B
CurrentVariable=Y

I tried doing just that but it didn't work so I am assuming i need to
declare that somewhere too?

"Dave Peterson" wrote:

I don't think it's the targetcerrownumber variable.

I think it's the wsCert variable. I don't think it has been set to a worksheet
(yet).

You could verify this by adding this before the offending line of code:

msgbox targetcertrownumber 'just to check to see what it is!
if wscert is nothing then
msgbox "wscert hasn't been set"
else
msgbox "wscert is ok and points to: " & wscert.name
end if

=======
Someplace in your code, you'll need a line like:

set wscert = workbooks("someworkbook.xls").worksheets("Someshee tnamehere")
or
set wscert = activesheet
or ...



msnyc07 wrote:

I had a coder write me a VBA which was rife with errors and he disappeared on
me so I've spent the last few days somehow managed to slog through and make
fixes (don't know VBA or coding)

However I am stuck on my last fix.

Essentially there are a # of functions within a larger one

I need to set a variable inside an if/then statement and store it in a cell
on the WS to access in a later function.

Based on how he wrote to cells I did this

wsCert.Cells(targetCertRowNumber, 300).Value = "Name"

which worked inside other functions

However I get this error

424

Object Required

Is there something I need to do inside that Function to allow me to use the
variable 'targetCertRowNumber'?

As I said I was able to write to cells like that in other functions.

Thanks in advance, hopefully I provided enough basic information

--

Dave Peterson
.


--

Dave Peterson