Hello,

I have some VBA code that sends data to an Excel 2000 Spreadsheet.
This data can vary in rows, sometimes 10 rows, sometimes 1000 rows.

In the VBA, I want to apply the following formula in column X to each
row:

477*Q2 <-----that would be for row 2

Then for row 3, same forumla, but Q3....etc etc.

Can someone help me out with some code.

Thanks!

-brian

Hmmm,

I tried this and it seemed to work:

Dim TotalRows As Integer, Row As Integer

TotalRows = ActiveSheet.UsedRange.Rows.Count


With objSht
For Row = 1 To TotalRows Step 1

Cells(Row, "U").Formula = "=477 * Q" & Row & ""
Next Row
End With

See
http://www.mvps.org/dmcritchie/excel/fillhand.htm

Starting with the cell in row 2 selected that has the formula
that will be copied down.

You can use the fill handle if you have contiguous cells to the left
(or if no unhidden cells on the left then contiguous cells to the right).

If you know your range then you can use Ctrl+D to copy the formula
down after placing range in the name box to left of formula bar.

-
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"BerkshireGuy" wrote in message oups.com...
Hello,

I have some VBA code that sends data to an Excel 2000 Spreadsheet.
This data can vary in rows, sometimes 10 rows, sometimes 1000 rows.

In the VBA, I want to apply the following formula in column X to each
row:

477*Q2 <-----that would be for row 2

Then for row 3, same forumla, but Q3....etc etc.

Can someone help me out with some code.

Thanks!

-brian

Give this a try.


Code:
--------------------
Sub AddFormula()
Dim NumRows As Integer, x As Integer
NumRows = ActiveSheet.UsedRange.Rows.Count
For x = 1 To NumRows
Range("X" & x) = "=477*Q" & x
Next x
End Sub
--------------------


HTH


--
bhofsetz
------------------------------------------------------------------------
bhofsetz's Profile: http://www.excelforum.com/member.php...o&userid=18807
View this thread: http://www.excelforum.com/showthread...hreadid=379028

sorry hadn't noticed this was the programming group, so
I will include the following solutions on the page previously referenced.
The solutions below do not use a loop so should work faster
than one with a loop. You'll have to see if it does what you want
but it is closer to what Ctrl+D does though I do not check the
column to right if there is no cell to the left.

In your solution you should get in the habit of using LONG
instead of Integer when referring to rows and columns.

The following macro will simulate fill down (Ctrl+D), as long as there is data to the left. The shortcut would do use right if it
can't use the left.

Sub filld()
'Simulate Ctrl+D (fill down), D.McRitchie 2005-06-14 programming
' http://www.mvps.org/dmcritchie/excel/fillhand.htm#filld (based only on left)
If IsEmpty(ActiveCell) Then Exit Sub
Range(ActiveCell, ActiveCell.Offset(0, -1).End(xlDown).Offset(0, 1)).FillDown
End Sub

The following will fill down as far down as the column to the left has content, as opposed to contiguous content. This version is
closer to what you did in a
a loop, but is based on the last cell with content to the left, not on
the used range. It will continue even if there are gaps in the data
in the column to the left.

Sub filld_to_last_at_left()
'Fill down to lastrow based on cell to left, D.McRitchie 2005-06-14 programming
' http://www.mvps.org/dmcritchie/excel/fillhand.htm#filld
If IsEmpty(ActiveCell) Then Exit Sub
Range(ActiveCell, Cells(Rows.Count, _
ActiveCell.Column - 1).End(xlUp).Offset(0, 1)).FillDown
End Sub
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"BerkshireGuy" wrote in message oups.com...
Hmmm,

I tried this and it seemed to work:

Dim TotalRows As Integer, Row As Integer

TotalRows = ActiveSheet.UsedRange.Rows.Count


With objSht
For Row = 1 To TotalRows Step 1

Cells(Row, "U").Formula = "=477 * Q" & Row & ""
Next Row
End With

Slightly quicker:
======================
Dim TotalRows As Long

TotalRows = ActiveSheet.UsedRange.Rows.Count
Range(cells(1,X),cells(TotalRows,X)).FormulaR1C1=" =477*RC17"
=======================

The R1C1 notation makes it a little easier.
R represents the row of the formula
C17 represents column Q

--
steveB

Remove "AYN" from email to respond
"BerkshireGuy" wrote in message
oups.com...
Hmmm,

I tried this and it seemed to work:

Dim TotalRows As Integer, Row As Integer

TotalRows = ActiveSheet.UsedRange.Rows.Count


With objSht
For Row = 1 To TotalRows Step 1

Cells(Row, "U").Formula = "=477 * Q" & Row & ""
Next Row
End With

Excellent information! Thanks to all!

-Brian