Code won't execute...

I cannot figure out why the following code won't execute...this is my code (without the long equations)...now i am not an expert...this was developed using bits and pieces that I found while searching through these forums...
the part that I am looking at right now is the section starting with "if unit = "" then"
I am using this to delete adjacent cells to the target cell when the target cell is deleted. But it is only doing the first two lines after the if statement. I don't know why, I am guessing it has something to do with the range(target.address) changing, but I am not for sure. This is the first time that I have really gotten into programming in VB and I am not quite sure how some of these things behave. Could someone please help!?


Public inMacro As Boolean

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo GetOut
If inMacro Then inMacro = False: Exit Sub
inMacro = True
If Target.Column = 3 And Target.Row <= 1000 Then
Set unit = Range(Target.Address)
'first part takes care of items deleted, if a unit is deleted then it deletes the rest of the row
If unit = "" Then
unit.Offset(0, -1) = ""
unit.Offset(0, 1) = "" '**********this is the where it stops w/o an error
unit.Offset(0, 2) = ""
unit.Offset(0, 3) = ""
Else
If unit.Offset(0, -1) = "" Then
unit.Offset(0, -1) = 1
If Left(unit, 1) = Worksheets("Remove Price").Cells(2, 4) Then 'Unit is a remove unit, so strip off "I" and look up the unit
unit.Offset(0, 1) = "long equation"
Else 'if unit doesn't have the I then it is assumed to be a install unit
If Left(unit, 1) = Worksheets("Install Price").Cells(2, 4) Then 'install unit is preceeded with "N"
unit.Offset(0, 1) = "long equation2"
Else 'If unit doesn't have an I or an N, then assumed to be an install unit
unit.Offset(0, 1) = "long Equation3"

End If
End If
End If
End If
End If

GetOut:
End
End Sub

D,

Ok, I will tiptoe in here with a couple of questions...
What is \"\" supposed to mean or do? (it won't compile)
Do you get a message, if you change your error handler as follows
and then run your code?
'----------
getout:
Beep
MsgBox "Error " & Err.Number & " " & Err.Description
End Sub
'------------

Jim Cone
San Francisco, USA


"Dolemite"
<[email protected]>
wrote in message
news:[email protected]

I cannot figure out why the following code won't execute...this is my
code (without the long equations)...now i am not an expert...this was
developed using bits and pieces that I found while searching through
these forums...
the part that I am looking at right now is the section starting with
"if unit = "" then"
I am using this to delete adjacent cells to the target cell when the
target cell is deleted. But it is only doing the first two lines after
the if statement. I don't know why, I am guessing it has something to
do with the range(target.address) changing, but I am not for sure.
This is the first time that I have really gotten into programming in VB
and I am not quite sure how some of these things behave. Could someone
please help!?

public inmacro as boolean

private sub worksheet_change(byval target as range)
on error goto getout
if inmacro then inmacro = false: exit sub
inmacro = true
if target.column = 3 and target.row <= 1000 then
set unit = range(target.address)
'first part takes care of items deleted, if a unit is deleted then
it deletes the rest of the row
if unit = \"\" then
unit.offset(0, -1) = \"\"
unit.offset(0, 1) = \"\" '**********this is the where it stops
w/o an error
unit.offset(0, 2) = \"\"
unit.offset(0, 3) = \"\"
else
if unit.offset(0, -1) = \"\" then
unit.offset(0, -1) = 1
if left(unit, 1) = worksheets(\"remove price\").cells(2, 4)
then 'unit is a remove unit, so strip off \"i\" and look up the unit
unit.offset(0, 1) = \"long equation\"
else 'if unit doesn't have the i then it is assumed to be a
install unit
if left(unit, 1) = worksheets(\"install price\").cells(2,
4) then 'install unit is preceeded with \"n\"
unit.offset(0, 1) = \"long equation2\"
else 'if unit doesn't have an i or an n, then assumed to be
an install unit
unit.offset(0, 1) = \"long equation3\"

end if
end if
end if
end if
end if

getout:
end
end sub
--
Dolemite

As far as the \"\" is concerned, I have no idea what that is...I think that is something that has to do with the message board. If you look at my original post everywhere I have "" (2 double quotes), your reply has the \"\"...so I can see how that wouln't compile. The double quotes (with no space between them essentially gives me a blank cell) I know there are other ways to do this..but I stuck with what I knew worked...or thought worked...

When I add that code to my my code, all it said was "Error 0" and it had an ok button and that was it.

As posted, my code runs without errors, it just doesn't behave like I expected it to.

where I added the comment '*******...
that is where the code stops executing and I can't figure out why...there is no conditional statement there for it to kick out, I would expect it to just go from line to line, but it doesn't...I don't get it.

D,

I should have guessed what was going on.
Your code runs ok for me after cleaning up the extra characters.
It is Event code, in that it runs whenever there is any change to cell content
on the worksheet - look at the title of the sub.
I think that what you believe is "quitting" is simply the code reacting to the
cell change event. In other words, the cell is changed, your code runs and
changes the cell which cause the code to run again. The module level variable
"inmacro" at the top of the module causes the cycle to abort, but not before
the code jumps to the top and tries to start over.
You may prefer the following modification which uses a code line to turn off
the event trigger. It must be turned back on before exiting the sub or it remains
and all events in the entire Excel application will not work
Note that "Exit Sub is added just before the error handler.

Jim Cone
San Francisco, USA
'------------------------
Option Explicit

Private Sub worksheet_change(ByVal target As Range)
On Error GoTo getout
Dim unit As Excel.Range
Application.EnableEvents = False
If target.Column = 3 And target.Row <= 1000 Then
Set unit = Range(target.Address)
'first part takes care of items deleted, if a unit is deleted then
'it deletes the rest of the row
If unit = "" Then
unit.Offset(0, -1) = ""
unit.Offset(0, 1) = ""
unit.Offset(0, 2) = ""
unit.Offset(0, 3) = ""
Else
If unit.Offset(0, -1) = "" Then
unit.Offset(0, -1) = 1
'unit is a remove unit, so strip off "i" and look up the unit
If Left(unit, 1) = Worksheets("\remove price\").Cells(2, 4) Then
unit.Offset(0, 1) = "long equation"
Else 'if unit doesn't have the i then it is assumed to be a install unit
'install unit is preceded with "n"
If Left(unit, 1) = Worksheets("install price").Cells(2, 4) Then
unit.Offset(0, 1) = "long equation2"
Else 'if unit doesn't have an i or an n, then assumed to be an install unit
unit.Offset(0, 1) = "long equation3"
End If
End If
End If
End If
End If
Application.EnableEvents = True
Set unit = Nothing
Exit Sub
getout:
Beep
Application.EnableEvents = True
MsgBox "Error " & Err.Number & " " & Err.Description
End Sub
'--------------------

"Dolemite"
<[email protected]>
wrote in message
news:[email protected]

As far as the \"\" is concerned, I have no idea what that is...I think
that is something that has to do with the message board. If you look
at my original post everywhere I have "" (2 double quotes), your reply
has the \"\"...so I can see how that wouln't compile. The double
quotes (with no space between them essentially gives me a blank cell)
I know there are other ways to do this..but I stuck with what I knew
worked...or thought worked...
When I add that code to my my code, all it said was "Error 0" and it
had an ok button and that was it.
As posted, my code runs without errors, it just doesn't behave like I
expected it to.
where I added the comment '*******...
that is where the code stops executing and I can't figure out
why...there is no conditional statement there for it to kick out, I
would expect it to just go from line to line, but it doesn't...I don't
get it.
--
Dolemite

You are indeed a lifesaver!!!!! Thanks for the explanation as well, it usually helps when someone can explain what is going on rather than just giving the solution.

Once again thanks!