Some basic help for a newbie

druid9855

New Member
Joined
Sep 27, 2006
Messages
20
Hi all!: I taught myself how to write macros in Lotus a long time ago and now I need to learn it in Excel, but I'm an old dog trying deperately to learn new tricks (and this VBA stuff is waaay beyond the little bit of BASIC I learned a million years ago!). I've searched for this stuff, but I guess it's too basic. Please help!
I have a fairly simple schedule laid out and want to automate it as follows:
1. Move the cursor to a cell named SET (that is the first cell of a row numbered 1-6)
2. Pause, prompt the user to move the cursor to their choice of cell #1-6 (denoting the first day of a new schedule), and then resume the macro (I would guess after pressing ENTER).
3. Check that the cursor is in a cell numbered 1-6; if not, go back to Step 1.
4. Move the cursor down 1 row, anchor the cursor and select (for copying) everything below that point (end down) and to the right the number of columns specified in a cell named MDAYS (the number of days in a month)(copy a schedule block).
5. Copy that block and paste it beginning at a cell named CAL (on another worksheet page).
6. Release all selected blocks and return the cursor to the upper left cell on each page (named CALENDAR and WORKSHEET).
Fudging around, I have figured out a couple of these things, but I don't really know how to put it all together. I realize this is kindergarten stuff, but I am grateful for any help and hopeful that seeing how it's done will guide me down the line. Thanks!
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

onlyadrafter

Well-known Member
Joined
Aug 19, 2003
Messages
5,703
Platform
  1. Windows
Hello,

Have done it (I think) how is it working?

Code:
Sub macro1()
    Application.ScreenUpdating = False
    Sheets("Sheet1").Range("SET").Select
ENTRY:
        If MY_CHOICE < 1 Or MY_CHOICE > 6 Then
            MY_CHOICE = InputBox("Please enter 1-6", "PLEASE SELECT")
                If MY_CHOICE < 1 Or MY_CHOICE > 6 Then
                    MY_ENTRY = MsgBox("PLEASE RE-ENTER", vbOKOnly, "ERROR")
                    GoTo ENTRY
                End If
        End If
        Selection.Offset(MY_CHOICE, 0).Select
        Range(Selection, Selection.End(xlDown)).Select
        Range(Selection, Selection.Offset(0, Range("MDAYS").Value)).Copy
        Sheets("Sheet2").Range("CAL").PasteSpecial (xlValues)
        Application.CutCopyMode = False
        Sheets("CALENDER").Range("A1").Select
        Sheets("WORKSHEET").Range("A1").Select
        Application.ScreenUpdating = True
End Sub
 

druid9855

New Member
Joined
Sep 27, 2006
Messages
20
Thanks, Only, One problem: For the ENTER and SELECT section, I don't need to enter a number 1-6; I need to be able to move the cursor.

>....D|E|F|G|H|I|J
4....1|2|3|4|5|6|7
5....X|6|6|6|6|X|X

Cell D4 is named SET. I need the cursor to physically go there, and the prompt to allow the user to move the cursor anywhere between there and cell I4 (#s 1-6), then check that the cursor is not in J7 or beyond. If OK, the cursor should drop down the same column to row 5 and begin the copy/paste procedure.
So far, everything is looking pretty good though, and I'm beginning to get it (just a little bit!), so I will also be playing with it. Thanks!
 

onlyadrafter

Well-known Member
Joined
Aug 19, 2003
Messages
5,703
Platform
  1. Windows
Hello,

DOn't know if this is exactly what you want, and haven't thoroughtly tested, so let me know how you get on.

THis code goes into the relevant sheet code window

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Selection.Count = 1 Then
    If ActiveCell.Address <> "$D$4" And Range("J1").Value = "NO" Then Range("J1").Value = "NO"

  If Range("J1").Value = "START" And ActiveCell.Row = 4 And ActiveCell.Column > 3 And ActiveCell.Column < 10 Then
        Selection.Offset(1, 0).Select
        Range(Selection, Selection.End(xlDown)).Select
        Range(Selection, Selection.Offset(0, Range("MDAYS").Value)).Copy
        Sheets("Sheet3").Range("CAL").PasteSpecial (xlValues)
        Application.CutCopyMode = False
        Sheets("CALENDER").Range("A1").Select
        Sheets("WORKSHEET").Range("A1").Select
        Application.ScreenUpdating = True
    End If
End If
End Sub

and this code goes into a standard module

Code:
Sub GOTO_SET_CELL()
    Sheets("Sheet2").Range("SET").Offset(-1, 0).Select
    MY_ENTRY = MsgBox("Please select a cell from D4 to I4", vbOKOnly, "CELL SELECT")
    Range("J1").Value = "START"
End Sub

Do you need an explanation?
 

druid9855

New Member
Joined
Sep 27, 2006
Messages
20
Only, Thanks for the update, but rein in for a sec...I'm still dizzy. What do you mean by "THis code goes into the relevant sheet code window"?
 

onlyadrafter

Well-known Member
Joined
Aug 19, 2003
Messages
5,703
Platform
  1. Windows
Hello,

I assume you know how to put the code in a standard module.

When you are in the VB window, you may have a window similar to Windows Explorer, if not select VIEW --> PROJECT EXPLORER, in here, it will have a list of the spreadsheets you have open

e.g. VBAProject(Book1)

click the + next to this to show a -

you should see Microsoft Excel Objects, and a list of the TAB names, e.g. Sheet1, Sheet2 etc,

double click on the sheet with the correct name, the main window will change and show (General) and (Declarations) at the top. THis is where the code needs to go (the bigger piece of code).
 

Forum statistics

Threads
1,136,926
Messages
5,678,606
Members
419,775
Latest member
joh93

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top