Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can't call macro from another
Hi All. I have the following code for a UserForm. It copies a list of dropped
golfers, goes to another part of the spreadsheet and finds the 1st empty cell in the column, pastes it and then moves 1 cell to the right and enters the date. All that works fine. The problem is that after it completes that it doesn't go to macro "DropButtonContinue". It loops again. Can anyone tell me what I am missing? Private Sub DropButton_Click() Range("DROPS").Select Selection.Copy strDrops = Range("TEAMDROPS").Value Range(strDrops).Select For DropsCounter = 1 To 100 If ActiveCell.Value = "" Then Selection.PasteSpecial Paste:=xlValues Application.CutCopyMode = False ActiveCell.Offset(0, 1).Select ActiveCell.Value = Date DropButtonContinue Else ActiveCell.Offset(1, 0).Select End If Next DropsCounter End Sub Private Sub DropButtonContinue() <<<More Code Here Thanks for any help. -- Jim T |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can't call macro from another
Substitute the Cell references to the first row and column where the data is
to be pasted and try this. Private Sub DropButton_Click() With cells Range("DROPS").Select Selection.Copy strDrops = Range("TEAMDROPS").Value Range(strDrops).Select set rng = .Range(.Cells(1,1),.cells(1,1)).End (xlDown) rng.Offset(1,0).Activate Activecell.PasteSpecial End With DropButtonContinue End Sub -- Best wishes, Jim "Jim Tibbetts" wrote: Hi All. I have the following code for a UserForm. It copies a list of dropped golfers, goes to another part of the spreadsheet and finds the 1st empty cell in the column, pastes it and then moves 1 cell to the right and enters the date. All that works fine. The problem is that after it completes that it doesn't go to macro "DropButtonContinue". It loops again. Can anyone tell me what I am missing? Private Sub DropButton_Click() Range("DROPS").Select Selection.Copy strDrops = Range("TEAMDROPS").Value Range(strDrops).Select For DropsCounter = 1 To 100 If ActiveCell.Value = "" Then Selection.PasteSpecial Paste:=xlValues Application.CutCopyMode = False ActiveCell.Offset(0, 1).Select ActiveCell.Value = Date DropButtonContinue Else ActiveCell.Offset(1, 0).Select End If Next DropsCounter End Sub Private Sub DropButtonContinue() <<<More Code Here Thanks for any help. -- Jim T |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can't call macro from another
I forgot to add this line
Dim rng As Range -- Best wishes, Jim "Jim Tibbetts" wrote: Hi All. I have the following code for a UserForm. It copies a list of dropped golfers, goes to another part of the spreadsheet and finds the 1st empty cell in the column, pastes it and then moves 1 cell to the right and enters the date. All that works fine. The problem is that after it completes that it doesn't go to macro "DropButtonContinue". It loops again. Can anyone tell me what I am missing? Private Sub DropButton_Click() Range("DROPS").Select Selection.Copy strDrops = Range("TEAMDROPS").Value Range(strDrops).Select For DropsCounter = 1 To 100 If ActiveCell.Value = "" Then Selection.PasteSpecial Paste:=xlValues Application.CutCopyMode = False ActiveCell.Offset(0, 1).Select ActiveCell.Value = Date DropButtonContinue Else ActiveCell.Offset(1, 0).Select End If Next DropsCounter End Sub Private Sub DropButtonContinue() <<<More Code Here Thanks for any help. -- Jim T |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can't call macro from another
Jim - Thanks for helping me with my problem. Your suggested code called for
the range to be set starting in A1, and you told me to replace that with the cell reference where the DROPS are to be pasted. Unfortunately, the value of TEAMDROPS is the result of a formula based on a selection from a ComboBox and changes each time the ComboBox is clicked. Therefore, the range referenced changes also. Also, the first time TEAMDROPS is accessed (where ever it is) it is empty. That was one of the reasons I was wrapping it all in an IF statement. Any ideas on what to do now? -- Jim T "Jim Jackson" wrote: I forgot to add this line Dim rng As Range -- Best wishes, Jim "Jim Tibbetts" wrote: Hi All. I have the following code for a UserForm. It copies a list of dropped golfers, goes to another part of the spreadsheet and finds the 1st empty cell in the column, pastes it and then moves 1 cell to the right and enters the date. All that works fine. The problem is that after it completes that it doesn't go to macro "DropButtonContinue". It loops again. Can anyone tell me what I am missing? Private Sub DropButton_Click() Range("DROPS").Select Selection.Copy strDrops = Range("TEAMDROPS").Value Range(strDrops).Select For DropsCounter = 1 To 100 If ActiveCell.Value = "" Then Selection.PasteSpecial Paste:=xlValues Application.CutCopyMode = False ActiveCell.Offset(0, 1).Select ActiveCell.Value = Date DropButtonContinue Else ActiveCell.Offset(1, 0).Select End If Next DropsCounter End Sub Private Sub DropButtonContinue() <<<More Code Here Thanks for any help. -- Jim T |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can't call macro from another
Does this do what you need?
Private Sub DropButton_Click() With cells Range("DROPS").Select Selection.Copy strDrops = Range("TEAMDROPS").Value Range(strDrops).Select If Activecell = "" then Activecell.pastespecial elseIf activecell.Offset(1,0) < "" then set rng = .Range(.Cells(1,1),.cells(1,1)).End (xlDown) rng.Offset(1,0).Activate Activecell.PasteSpecial Else Activecell.Offset(1,0).Activate Activecell.pasteSpecial end if End With DropButtonContinue End Sub -- Best wishes, Jim "Jim Jackson" wrote: Substitute the Cell references to the first row and column where the data is to be pasted and try this. Private Sub DropButton_Click() With cells Range("DROPS").Select Selection.Copy strDrops = Range("TEAMDROPS").Value Range(strDrops).Select set rng = .Range(.Cells(1,1),.cells(1,1)).End (xlDown) rng.Offset(1,0).Activate Activecell.PasteSpecial End With DropButtonContinue End Sub -- Best wishes, Jim "Jim Tibbetts" wrote: Hi All. I have the following code for a UserForm. It copies a list of dropped golfers, goes to another part of the spreadsheet and finds the 1st empty cell in the column, pastes it and then moves 1 cell to the right and enters the date. All that works fine. The problem is that after it completes that it doesn't go to macro "DropButtonContinue". It loops again. Can anyone tell me what I am missing? Private Sub DropButton_Click() Range("DROPS").Select Selection.Copy strDrops = Range("TEAMDROPS").Value Range(strDrops).Select For DropsCounter = 1 To 100 If ActiveCell.Value = "" Then Selection.PasteSpecial Paste:=xlValues Application.CutCopyMode = False ActiveCell.Offset(0, 1).Select ActiveCell.Value = Date DropButtonContinue Else ActiveCell.Offset(1, 0).Select End If Next DropsCounter End Sub Private Sub DropButtonContinue() <<<More Code Here Thanks for any help. -- Jim T |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Call Macro | Excel Discussion (Misc queries) | |||
How can run a macro ( call a macro) on selection of any filtercriteria? | Excel Worksheet Functions | |||
Call an Access macro from an Excel macro | Excel Discussion (Misc queries) | |||
Excel Macro call Word Macro with Parameters | Excel Programming | |||
Call macro stored in Excel workbook from Outlook's macro | Excel Programming |