Executing forumula in excel with vba for all rows in worksheet 2

[mrtauntaun]

Hi there. Im new to using VBA in excel, so bear with me.
I have a workshet with numerous rows. I need to make a snipet of code that checks a row, takes the values from a cell, runs it through a formula, and puts it in a cell at the end of a row. Once it is done, the have it do so for the next row and so on until each row is done. At which point have it terminate.
For example, for row 1:

Take value from Cell A1, place the result of formula (8-A1)*10 into cell E1. Proceed to next row, using cells A2 and E2.

I hope that is clear. Thanks!

 

[Bong]

I'm no expert so there will undoubtedly be better ways to do this than what I propose. That said...

First you want to find the last row used (assuming the used rows are contiguous):

Set lcell = Range("a65536").End(xlUp)
Cells(lcell.Row + 1, 1).Select
lastrow = Selection.Row

Now you want to loop through the rows from 1 to lastrow (for i=1 to lastrow), find the value in column A (1), do some function, and put the result in column E (5):

for i=1 to lastrow
   cells(i,5).value=YourFunction(cells(i,1).value)
next

or in your example:

for i=1 to lastrow
   cells(i,5).value=(8-cells(i,1).value)*10
next

_________________
Bob Rashkin

 

[firefytr]

How about no loop, and all in one shot? ...

Dim rngCalc as range
Set rngCalc = Range("E1:E" & Cells(Rows.Count, 1).End(xlup).row)
rngCalc.Formula = "=(8-A1)*10"
rngCalc.Value = rngCalc.Value

HTH

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[mrtauntaun]

Thank you both! Both codes work, though I am going with firefytrs code as its more compact, thanks again!