To execute a formula upto in a cell

[pol]

Please give me help

How I can write a macro in excel for copy and paste a formula with
depending upon the value of next right cell

For example I have a formula in cell A1 to get numeric value from alpha
numeric value in cell B1 . That forumula I have to copy down from A upto
the data contained in next cell B . ie, that may to copy from A1..A10 or from
A1..30 that is depending the data of B cell

Please Help
With Thanks and Regdards
Pol

 

[Joel]

The best way is to use a worksheet change macro. When the data in colum nB
is entered automatically put the formula to the cell immediately to the left.

Private Sub Worksheet_Change(ByVal Target as Range)
if Target.Column = 2 then
Target.offset(0,-1).formula = "=B" & Target.Row & "+1"
end if
End Sub

Which will put in Column A5 "= B5 + 1" when cell B5 is changed

 

[Dave Peterson]

Option Explicit
Sub Testme()
Dim Wks as worksheet
dim LastRow as long

set wks = activesheet

with activesheet
lastrow = .cells(.rows.count,"B").end(xlup).row
.range("a1:A" & lastrow).formular1c1 = .range("A1").formular1c1
end with
End sub

 

[pol]

Thnks a lot . thnks again

Option Explicit
Sub Testme()
Dim Wks as worksheet
dim LastRow as long

set wks = activesheet

with activesheet
lastrow = .cells(.rows.count,"B").end(xlup).row
.range("a1:A" & lastrow).formular1c1 = .range("A1").formular1c1
end with
End sub