Selecting a non-specific range and inserting formula

[cannon_lab]

Hi everyone,
I've created a macro that adds a new column to a worksheet, and then puts a title of "Minutes" on the top of the column. The column to the left of the minutes column is in HH:MM:SS time format, and the minutes column uses a formula (which works perfectly) to put the time format into minutes. The code for this is below:

Sub Analysis2()
'
' Analysis2 Macro
' Macro recorded 05/12/2007 by pwilson to convert duration into minutes
'

'
    Columns("H:H").Select
    Selection.Insert Shift:=xlToRight
    Selection.NumberFormat = "#,##0.00"
    Range("H1").Select
    ActiveCell.FormulaR1C1 = "Minutes"
    Range("G2").Select
    Selection.End(xlDown).Select
    Range("H25854").Select
    ActiveCell.FormulaR1C1 = _
        "=(HOUR(RC[-1])*60)+(MINUTE(RC[-1]))+(SECOND(RC[-1])/60)"
    Range("H25854").Select
    Selection.Copy
    Range("H25853").Select
    Range(Selection, Selection.End(xlUp)).Select
    Range("H2:H25853").Select
    Range("H25853").Activate
    ActiveSheet.Paste
    ActiveSheet.Paste
    Application.CutCopyMode = False
End Sub

The problem with the code is that when I recorded the macro I used control+down to select the range that I needed. However, Excel took this to be that I was specifically refering to cell H25853. This cell was the last row to contain data (which is what happens when you navigate using ctrl+down).

What I need is the formula to be inserted into every cell in the H column that has data in the adjacent G column. For instance, if the data goes to only 10 rows, then I want the formula applied to only those rows. If it goes to 64000 rows, the formula needs to be applied to all of these rows.
Can anyone help?

[Leith Ross]

Hello cannon_lab,

Here is the amended macro code...

Sub Analysis2()
'
' Analysis2 Macro
' Macro recorded 05/12/2007 by pwilson to convert duration into minutes
'
  Dim Cell As Range
  Dim LastRow As Long
  Dim Rng As Range

    LastRow = Cells(Rows.Count, "G").End(xlUp).Row
    Set Rng = Range("G2", Cells(LastRow, "G"))

    Range("H:H").NumberFormat = "#,##0.00"
    Range("H1").Value = "Minutes"

      For Each Cell In Rng
        If Cell <> "" Then Cell.Offset(0, 1).Formula = "=(HOUR(RC[-1])*60)+(MINUTE(RC[-1]))+(SECOND(RC[-1])/60)"
      Next Cell

End Sub

Sincerely,
Leith Ross

[mudraker]

Hers another version

Sub Analysis2()
'
' Analysis2 Macro
' Macro recorded 05/12/2007 by pwilson to convert duration into minutes
' Modified by mudraker at excelforum 11/12/2007
' in thread http://www.excelforum.com/showthread.php?t=626353
'
   Dim lLR As Long
   Columns("H:H").Insert Shift:=xlToRight
   Columns("H:H").NumberFormat = "#,##0.00"
   Range("H1").Value = "Minutes"
   lLR = Cells(Rows.Count, "g").End(xlUp).Row
   Range("h2:h" & lLR).FormulaR1C1 = _
      "=(HOUR(RC[-1])*60)+(MINUTE(RC[-1]))+(SECOND(RC[-1])/60)"
End Sub