moving a macro

When you record the macro, right next to the stop recording button "Relative Reference" button. If you push this during recording, it will record the refrence instead of the actual cell. So if you start in a cell A1 and then select A5, instead of recording it as selecting A5, it will record select 4 cells down.

Basically you want to incorporate "ActiveCell" into your macro. If you paste it (your macro) here, I can give you a more definitive answer.

-rh

Why not invoke a message box asking where to start? For example, here is part of code that asks user a number of items to determine start and end of range (check CStrt and CEnd)(NOTE: I only included start of macro and stripped out a bunch of stuff so, a lot of the variables declared may be invalid):

Sub Promptsforinput()
'Define variables for use in lookups
Dim CStrt As String
Dim CEnd As String
Dim InSheet As String
Dim InFile As String
Dim ASA As String
Dim SLA As String
Dim ATT As String
Dim PCP As String
Dim NoSheet As Integer
Dim SheetCH As String
Dim SheetCtr As Integer
' SheetCtr = 1
'More Variables: row count, calc number of rows, column start and output column
Dim Counter As Integer
Dim NumRows As Integer
Dim ColStart As Integer
Dim OutCol As Integer
Dim CPHH As Integer
Counter = 0
'Ask User how many sheets to loop through
NoSheet = Application.InputBox(prompt:="Enter the number of sheets to loop through.", Type:=1)
'Ask User to define sheet name starting characters
SheetCH = InputBox("This macro assumes sheets have standard names that increment by one (e.g., D1, D2 and so on). Enter the characters that precede the numbers.")
'Check for no entry
If SheetCH = "" Then End
'Ask User to define the sheet number to start at
SheetCtr = Application.InputBox(prompt:="Indicate the sheet number to start at.", Type:=1)
'Ask User what column to put the output in
OutCol = Application.InputBox("Enter the column to put results in relative to column where interval time column is.", Type:=1)
Windows(InFile).Activate
'Ask User cell reference where time intervals start
CStrt = InputBox("Please specify first CELL containing Time Intervals.")
'Check for no entry
If CStrt = "" Then End
'Ask User cell reference where time intervals end
CEnd = InputBox("Please specify last CELL containing Time Intervals.")
'Check for no entry
If CEnd = "" Then End
'Set the Lookup Range so we can count the rows later
InCalls = CStrt & ":" & CEnd
'Turn Screen Updating Off
Application.ScreenUpdating = False

Sub BFROMD()
'
' BFROMD Macro
' Macro recorded 12/31/01
'

'
Range("F4").Select
ActiveCell.FormulaR1C1 = "=RC[-4]-RC[-2]"
Range("F4").Select
Selection.AutoFill Destination:=Range("F4:G4"), Type:=xlFillDefault
Range("F4:G4").Select
Selection.AutoFill Destination:=Range("F4:G600")
Range("F4:G600").Select
End Sub

You could change it to what I have below - but you need to make sure that you select the cell you want to start from before running it.

Sub BFROMD() 
    Dim cellStart As Range 
Set cellStart = ActiveCell 
    ActiveCell.FormulaR1C1 = "=RC[-4]-RC[-2]" 

    Selection.AutoFill Destination:=Range(cellStart, _ 
                        Cells(ActiveCell.Row, ActiveCell.Column + 1)), _ 
                        Type:=xlFillDefault 

    Range(ActiveCell, Cells(ActiveCell.Row, ActiveCell.Column + 1)).Select 
    Selection.AutoFill Destination:=Range(cellStart, Cells(600, 9)) 
End Sub 

Hope this helps,

Russell

(by the way - you know you can just select 2 cells in columns F and G and then double-click on the fill handle to copy the formula down, right? This seems like going to more trouble than pulling down formulas is worth...but I hope it works for you).

BFROMD Macro Macro recorded 12/31/01

Oops - change Cells(9, 600) to Cells(7,600)....(nt)

    Selection.AutoFill Destination:=Range(cellStart, _                      Cells(ActiveCell.Row, ActiveCell.Column + 1)), _                      Type:=xlFillDefault   Range(ActiveCell, Cells(ActiveCell.Row, ActiveCell.Column + 1)).Select  Selection.AutoFill Destination:=Range(cellStart, Cells(600, 9))  End Sub  

Re: Oops - change Cells(9, 600) to Cells(7,600)....(nt)

Thanks for your help.
Yes, I know I could just double click on the fill handle for that. I was just wondering if I could start a macro from the active cell and not hard code the cell in the VB. Also, can you do if-then statements in the VB macro script? Example: if the column D does not equal column B then insert a row (on that row that doesn't equal) in columns D and E.

(hope i'm not confusing you) : You could change it to what I have below - but you need to make sure that you select the cell you want to start from before running it. :

    Selection.AutoFill Destination:=Range(cellStart, _                      Cells(ActiveCell.Row, ActiveCell.Column + 1)), _                      Type:=xlFillDefault   Range(ActiveCell, Cells(ActiveCell.Row, ActiveCell.Column + 1)).Select  Selection.AutoFill Destination:=Range(cellStart, Cells(600, 9))  : End Sub  :