Macro adjustment
 

Hello,

I have a macro that cuts and pastes as values the contents of cells E3 to
E45 of (Sheet2) to another, hidden, worksheet (sheet6) then unhides and
selects cell C3 of Sheet3.

However, if the values of E3 to E45 ALL equal either "0" or " " then I'd
like to to keep Sheet3 hidden and move on to Sheet 4. But if the value of
even one of the cells from E3 to E45 equals "1", "2", or "3" then I need to
unhide sheet3 and select cell C3 ready for input.

To prove I'm not a complete numpty and I'm learning from you guys, I'm
guessing there's an If and else in there somewhere and that it goes something
like....

If value.Range("E3:E45") = 0 Then
Sheets("Sheet4).Select
Range("C3").Select
Else
Sheets("Sheet3").Visible = True

Sheets("Sheet3").Select
Range("C3").Select
End If

Application.ScreenUpdating = False

End Sub

I tried it but it throws a big red hissy fit at me :-)

Your help is much appreciated.

Regards,

Tel

Macro adjustment
 

Use the worksheetfunction SUM

if WorksheetFunction.Sum(Range("E3:E45")) = 0 then





"Tel" wrote:

Hello,

I have a macro that cuts and pastes as values the contents of cells E3 to
E45 of (Sheet2) to another, hidden, worksheet (sheet6) then unhides and
selects cell C3 of Sheet3.

However, if the values of E3 to E45 ALL equal either "0" or " " then I'd
like to to keep Sheet3 hidden and move on to Sheet 4. But if the value of
even one of the cells from E3 to E45 equals "1", "2", or "3" then I need to
unhide sheet3 and select cell C3 ready for input.

To prove I'm not a complete numpty and I'm learning from you guys, I'm
guessing there's an If and else in there somewhere and that it goes something
like....

If value.Range("E3:E45") = 0 Then
Sheets("Sheet4).Select
Range("C3").Select
Else
Sheets("Sheet3").Visible = True

Sheets("Sheet3").Select
Range("C3").Select
End If

Application.ScreenUpdating = False

End Sub

I tried it but it throws a big red hissy fit at me :-)

Your help is much appreciated.

Regards,

Tel

Macro adjustment
 

On Mon, 6 Jul 2009 03:18:00 -0700, Tel
wrote:


Hello,

I have a macro that cuts and pastes as values the contents of cells E3 to
E45 of (Sheet2) to another, hidden, worksheet (sheet6) then unhides and
selects cell C3 of Sheet3.

However, if the values of E3 to E45 ALL equal either "0" or " " then I'd
like to to keep Sheet3 hidden and move on to Sheet 4. But if the value of
even one of the cells from E3 to E45 equals "1", "2", or "3" then I need to
unhide sheet3 and select cell C3 ready for input.

To prove I'm not a complete numpty and I'm learning from you guys, I'm
guessing there's an If and else in there somewhere and that it goes something
like....

If value.Range("E3:E45") = 0 Then
Sheets("Sheet4).Select
Range("C3").Select
Else
Sheets("Sheet3").Visible = True

Sheets("Sheet3").Select
Range("C3").Select
End If

Application.ScreenUpdating = False

End Sub

I tried it but it throws a big red hissy fit at me :-)

Your help is much appreciated.

Regards,

Tel


Try this:

Sub Tel()
If WorksheetFunction.Sum(Range("E3:E45")) = 0 Then
Sheets("Sheet4").Activate
ActiveSheet.Range("C3").Select
Else
Sheets("Sheet3").Visible = True
Sheets("Sheet3").Activate
ActiveSheet.Range("C3").Select
End If
End Sub

Hope this helps / Lars-Åke

Macro adjustment
 

Many thanks for your help Joel.

Unfortunately it just jumps to sheet 4 irrespective of the sum being above 0.

Is there any way to 'force' it to look at Sheet 2 for the calculation as I
think with all the cutting and pasting that precedes it, I'm leaving it in
the wrong place. I've tried selecting Sheet 2 cell D3 before running the If
statement but that doesn't seem to work.

TVM

Tel



"Joel" wrote:

Use the worksheetfunction SUM

if WorksheetFunction.Sum(Range("E3:E45")) = 0 then





"Tel" wrote:

Hello,

I have a macro that cuts and pastes as values the contents of cells E3 to
E45 of (Sheet2) to another, hidden, worksheet (sheet6) then unhides and
selects cell C3 of Sheet3.

However, if the values of E3 to E45 ALL equal either "0" or " " then I'd
like to to keep Sheet3 hidden and move on to Sheet 4. But if the value of
even one of the cells from E3 to E45 equals "1", "2", or "3" then I need to
unhide sheet3 and select cell C3 ready for input.

To prove I'm not a complete numpty and I'm learning from you guys, I'm
guessing there's an If and else in there somewhere and that it goes something
like....

If value.Range("E3:E45") = 0 Then
Sheets("Sheet4).Select
Range("C3").Select
Else
Sheets("Sheet3").Visible = True

Sheets("Sheet3").Select
Range("C3").Select
End If

Application.ScreenUpdating = False

End Sub

I tried it but it throws a big red hissy fit at me :-)

Your help is much appreciated.

Regards,

Tel

Macro adjustment
 

Yes. specify the sheet in the Range

if WorksheetFunction.Sum(Sheets("Sheet4").Range("E3:E 45")) = 0 then


"Tel" wrote:

Many thanks for your help Joel.

Unfortunately it just jumps to sheet 4 irrespective of the sum being above 0.

Is there any way to 'force' it to look at Sheet 2 for the calculation as I
think with all the cutting and pasting that precedes it, I'm leaving it in
the wrong place. I've tried selecting Sheet 2 cell D3 before running the If
statement but that doesn't seem to work.

TVM

Tel



"Joel" wrote:

Use the worksheetfunction SUM

if WorksheetFunction.Sum(Range("E3:E45")) = 0 then





"Tel" wrote:

Hello,

I have a macro that cuts and pastes as values the contents of cells E3 to
E45 of (Sheet2) to another, hidden, worksheet (sheet6) then unhides and
selects cell C3 of Sheet3.

However, if the values of E3 to E45 ALL equal either "0" or " " then I'd
like to to keep Sheet3 hidden and move on to Sheet 4. But if the value of
even one of the cells from E3 to E45 equals "1", "2", or "3" then I need to
unhide sheet3 and select cell C3 ready for input.

To prove I'm not a complete numpty and I'm learning from you guys, I'm
guessing there's an If and else in there somewhere and that it goes something
like....

If value.Range("E3:E45") = 0 Then
Sheets("Sheet4).Select
Range("C3").Select
Else
Sheets("Sheet3").Visible = True

Sheets("Sheet3").Select
Range("C3").Select
End If

Application.ScreenUpdating = False

End Sub

I tried it but it throws a big red hissy fit at me :-)

Your help is much appreciated.

Regards,

Tel

Macro adjustment
 

If WorksheetFunction.Sum(Sheets("Sheet2").Range("E3:E 45")) = 0 Then
Sheets("Sheet4).Select
Range("C3").Select
Else
'your code continues

If this post helps click Yes
---------------
Jacob Skaria


"Tel" wrote:

Hello,

I have a macro that cuts and pastes as values the contents of cells E3 to
E45 of (Sheet2) to another, hidden, worksheet (sheet6) then unhides and
selects cell C3 of Sheet3.

However, if the values of E3 to E45 ALL equal either "0" or " " then I'd
like to to keep Sheet3 hidden and move on to Sheet 4. But if the value of
even one of the cells from E3 to E45 equals "1", "2", or "3" then I need to
unhide sheet3 and select cell C3 ready for input.

To prove I'm not a complete numpty and I'm learning from you guys, I'm
guessing there's an If and else in there somewhere and that it goes something
like....

If value.Range("E3:E45") = 0 Then
Sheets("Sheet4).Select
Range("C3").Select
Else
Sheets("Sheet3").Visible = True

Sheets("Sheet3").Select
Range("C3").Select
End If

Application.ScreenUpdating = False

End Sub

I tried it but it throws a big red hissy fit at me :-)

Your help is much appreciated.

Regards,

Tel

Macro adjustment
 

Thanks guys,

I've ticked all the boxes for all of you.

Just a point to others, when using this solution make sure your numbers are
true numbers and not stored as Text - It did my bleeding 'ead in until I
realised the error of my ways. (make sure you don't have " " around your
numbers :-))

Tel

"Tel" wrote:

Hello,

I have a macro that cuts and pastes as values the contents of cells E3 to
E45 of (Sheet2) to another, hidden, worksheet (sheet6) then unhides and
selects cell C3 of Sheet3.

However, if the values of E3 to E45 ALL equal either "0" or " " then I'd
like to to keep Sheet3 hidden and move on to Sheet 4. But if the value of
even one of the cells from E3 to E45 equals "1", "2", or "3" then I need to
unhide sheet3 and select cell C3 ready for input.

To prove I'm not a complete numpty and I'm learning from you guys, I'm
guessing there's an If and else in there somewhere and that it goes something
like....

If value.Range("E3:E45") = 0 Then
Sheets("Sheet4).Select
Range("C3").Select
Else
Sheets("Sheet3").Visible = True

Sheets("Sheet3").Select
Range("C3").Select
End If

Application.ScreenUpdating = False

End Sub

I tried it but it throws a big red hissy fit at me :-)

Your help is much appreciated.

Regards,

Tel