Variable Excel Macro

[lakers8176]

I don't use macros or VBA very often, but I need to create a macro in Excel XP. I need to start on a random cell, key in data, tab over two cells, key in data, tab over two cells, key in data, then go back to the cell I started in and go down four rows.

I would like to have the macro wait for my data input before continuing. Currently, when I record a macro it records the exact cell number that I'm in. I want it to run the same regardless of where I may be in the sheet.

Any help would be appreciated.....Thanks.

 

[SkipVought]

Hi,
You really cant have a macro that does the waiting for you to enter data stuff.

BUT there are worksheet events, like Worksheet_Change from which you can do stuff like what you want.

If you want to do it, it's pretty straight forward.

1. right-click the sheet tab and select "View Code" -- This puts you into the code for that sheet object.
2. at the top of the code window, there are 2 dropdowns - Object and Procedure. Select Worksheet from the Object dropdown
3. Select Change from the Procedure dropdown
4. In the Worksheet_Change sub enter for following code...

Dim iCount As Byte
Private Sub Worksheet_Change(ByVal Target As Range)
    With Target
        Select Case iCount
        Case 0
            .Offset(0, 2).Select
            iCount = 1
        Case 2
            .Offset(4, -iCount * 2).Select
            iCount = 0
        Case Else
            .Offset(0, 2).Select
            iCount = iCount + 1
        End Select
    End With
End Sub

hope this helps


Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[lakers8176]

My users are telling me that the macro to do this was origianlly in Lotus. A number of years ago they converted over to Excel 97 and it worked just fine. Now that we have moved up to Excel XP that macro doesn't work. That's why I'm under the impression that a macro can be be created to do this because it worked in Lotus and Excel 97.....

 

[SkipVought]

Send me a copy of the workbook that worked

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com