Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct Adjustment | Excel Discussion (Misc queries) | |||
formula adjustment | Excel Worksheet Functions | |||
automatic adjustment | Excel Discussion (Misc queries) | |||
Event Macro adjustment needed - need to change font color also | Excel Worksheet Functions | |||
Copy without Adjustment | Excel Discussion (Misc queries) |