Macro adjustment

[Tel]

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

 

[Lars-Åke Aspelin]

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

 

[Tel]

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]

Yes. specify the sheet in the Range

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

 

[Jacob Skaria]

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

If this post helps click Yes

 

[Tel]

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