Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Error Message Box code
Hi
I am working on a macro to do some copying and pasting but it relies o the active cell of the destination sheet being blank and in a correc column. I need help with code to bring up an error message if th active cell is in a wrong column or has data in it. The active cel must be in any of several columns which has a value of 1 in row 4. My macro was created in part by the recorder and edited by me (and b some miracle it works). I am obviously very new at this so I woul also be grateful to hear suggestions on cleaning it up, please. Her is what the macro is now: Sub Macro6() Application.Goto Reference:="scores" Selection.Copy Sheets("Sheet3").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone SkipBlanks:= _ False, Transpose:=False ActiveCell.Select Application.CutCopyMode = False ActiveCell.Offset(0, -6).FormulaR1C1 = "=TODAY()" Selection.Copy Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone SkipBlanks:= _ False, Transpose:=False ActiveCell.Offset(-1, -5).Select Selection.Resize(1, 5).Select Selection.Copy ActiveCell.Offset(1, 0).Select ActiveSheet.Paste Application.CutCopyMode = False ActiveCell.Offset(0, 5).Select End Sub Thanks in advance for any help -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Error Message Box code
The real question is ... what do you REALLY want to do if the activ
cell on Sheet3 is not blank? All you really want is a message box? OK, here it is. But, better if you go to Sheet3 and select a cel instead of just letting the macro find its own active cell. Sub Macro6() Application.Goto Reference:="scores" Selection.Copy Sheets("Sheet3").Select If activecell = "" then Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks: _ False, Transpose:=False ActiveCell.Select Application.CutCopyMode = False ActiveCell.Offset(0, -6).Value = Date ActiveCell.Offset(-1, -5).Select Selection.Resize(1, 5).Select Selection.Copy ActiveCell.Offset(1, 0).Select ActiveSheet.Paste Application.CutCopyMode = False ActiveCell.Offset(0, 5).Select Else: MsgBox "hey, rummy, this cell ain't blank!" End if End Su -- Message posted from http://www.ExcelForum.com |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Error Message Box code
Thanks for the reply MSP77079.
What I want to do is stop the pasting of any data over existing data s what I REALLY want to do if the 2 conditions aren't met is to have th macro just activate Sheet3. I will then make the correct cell active return to the original sheet and run the macro again. I don't see anything in your code to deal with the second conditio that needs to be met (the value in Row 4 of the active cell's colum must have an existing value of 1 and there are 10 columns meeting tha condition). Since I don't know how to deal with an AND could you hel further with that part? Thanks again for the help so far -- Message posted from http://www.ExcelForum.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Code has been interupted - error message | Excel Discussion (Misc queries) | |||
VBA - error message "Code excecution has been interupted" | Excel Discussion (Misc queries) | |||
Message box causing error in code | Excel Programming | |||
Message box causing error in code | Excel Programming | |||
How? - Error message for turning off autofilter with code | Excel Programming |