MrExcel Publishing
Your One Stop for Excel Tips & Solutions

moving a macro


Posted by Jason on January 04, 2002 9:28 AM

I have a macro running cells G4 to H600. The formula I need is set. The only part I need help on is: how can I run the macro on those two columns but starting where I select the cells, and not starting back up at G4 every time? I don't always need to start at the beginning every time but G4 is hardcoded in the VB script. I don't know how to change it to start where I select the cells. It gets quite redundant when I get past the first page having to run the macro and then scroll down every time. I'm using Excel 97. Thanks for your help.


Posted by Scott on January 04, 2002 9:44 AM

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.

Posted by Russell Hauf on January 04, 2002 9:46 AM

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

Posted by DonnaW on January 04, 2002 9:48 AM

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

Posted by Jason on January 04, 2002 12:31 PM

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

Posted by Russell Hauf on January 04, 2002 1:41 PM

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

Posted by Russell Hauf on January 04, 2002 1:43 PM

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  
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 : '

Posted by Jason on January 04, 2002 2:40 PM

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  : 
: 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