No of rows as Range
 

Hi,
I need to write the macro that calculate a function that should be fil
all the rows with particular row.
i include the coding.

here the Range will be Range("G3:nRows")
the nRows is the No. of rows with used cells(with contents)
How to calculate the nRows??

code::

Sub CalculateTime()
'
' CalculateTime Macro
' Macro recorded 3/2/2006 by Shah
'
Dim nRows As Long

nRows = Application.ActiveSheet.UsedRange.Rows.Count
'
Range("G3").Select
'nRows = ActiveSheet.UsedRange.Rows.Count
'nRows = G2000

ActiveCell.FormulaR1C1 = "=IF(RC[-4]=R[-1]C[-4],R[-1]C+150,61366)"
Selection.AutoFill Destination:=Range("G3:nRows")
Type:=xlFillDefault
' Selection.AutoFill Destination:=Range("G3:G17750")
Type:=xlFillDefault
' Cells.Value
'Range("G3:G28884").Select

Range("G2").Select
End Su

--
zeesha
-----------------------------------------------------------------------
zeeshah's Profile: http://www.excelforum.com/member.php...fo&userid=2286
View this thread: http://www.excelforum.com/showthread.php?threadid=51813

No of rows as Range
 

Sub CalculateTime()
' CalculateTime Macro
' Macro recorded 3/2/2006 by Shah
Dim nRows As Long

nRows = Application.ActiveSheet.UsedRange.Rows.Count
'
Range("G3").Select
'nRows = ActiveSheet.UsedRange.Rows.Count
'nRows = G2000

ActiveCell.FormulaR1C1 = "=IF(RC[-4]=R[-1]C[-4],R[-1]C+150,61366)"
Selection.AutoFill Destination:=Range("G3:G" & nRows), _
Type:=xlFillDefault
' Selection.AutoFill Destination:=Range("G3:G17750"),
' Type:=xlFillDefault
' Cells.Value
'Range("G3:G28884").Select

Range("G2").Select
End Sub



--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"zeeshah" wrote in
message ...

Hi,
I need to write the macro that calculate a function that should be fill
all the rows with particular row.
i include the coding.

here the Range will be Range("G3:nRows")
the nRows is the No. of rows with used cells(with contents)
How to calculate the nRows??

code::

Sub CalculateTime()
'
' CalculateTime Macro
' Macro recorded 3/2/2006 by Shah
'
Dim nRows As Long

nRows = Application.ActiveSheet.UsedRange.Rows.Count
'
Range("G3").Select
'nRows = ActiveSheet.UsedRange.Rows.Count
'nRows = G2000

ActiveCell.FormulaR1C1 = "=IF(RC[-4]=R[-1]C[-4],R[-1]C+150,61366)"
Selection.AutoFill Destination:=Range("G3:nRows"),
Type:=xlFillDefault
' Selection.AutoFill Destination:=Range("G3:G17750"),
Type:=xlFillDefault
' Cells.Value
'Range("G3:G28884").Select

Range("G2").Select
End Sub


--
zeeshah
------------------------------------------------------------------------
zeeshah's Profile:
http://www.excelforum.com/member.php...o&userid=22867
View this thread: http://www.excelforum.com/showthread...hreadid=518138