I have the following formula in the active cell
ActiveCell.FormulaR1C1 = _

"=LOOKUP(6.022*10^23,--MID(RC[1],MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},RC[1]&""0123456789"")),ROW(INDIRECT(""1:""&LEN(RC[1])))))"

Please help me to copy the above forumula to the following cells in the same
column by using the following macros . But I have to refer the active cell
formula instead of refering a particular cell like (("B3:B") I have to say
from the active cell to the cell of last row

I need to avoid (("B3:B") and .Range("B3"). Instead I have to use active
cell




Dim Wks As Worksheet
Dim LastRow As Long

Set Wks = ActiveSheet

With ActiveSheet
LastRow = .Cells(.Rows.Count, "C").End(xlUp).Row
.Range("B3:B" & LastRow).FormulaR1C1 = .Range("B3").FormulaR1C1
End With

You have multiple responses to your multiple posts.

pol wrote:

I have the following formula in the active cell
ActiveCell.FormulaR1C1 = _

"=LOOKUP(6.022*10^23,--MID(RC[1],MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},RC[1]&""0123456789"")),ROW(INDIRECT(""1:""&LEN(RC[1])))))"

Please help me to copy the above forumula to the following cells in the same
column by using the following macros . But I have to refer the active cell
formula instead of refering a particular cell like (("B3:B") I have to say
from the active cell to the cell of last row

I need to avoid (("B3:B") and .Range("B3"). Instead I have to use active
cell


Dim Wks As Worksheet
Dim LastRow As Long

Set Wks = ActiveSheet

With ActiveSheet
LastRow = .Cells(.Rows.Count, "C").End(xlUp).Row
.Range("B3:B" & LastRow).FormulaR1C1 = .Range("B3").FormulaR1C1
End With

--

Dave Peterson

Sorry , Thanks I found answer and definitly useful very much thanks


"pol" wrote:

I have the following formula in the active cell
ActiveCell.FormulaR1C1 = _

"=LOOKUP(6.022*10^23,--MID(RC[1],MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},RC[1]&""0123456789"")),ROW(INDIRECT(""1:""&LEN(RC[1])))))"

Please help me to copy the above forumula to the following cells in the same
column by using the following macros . But I have to refer the active cell
formula instead of refering a particular cell like (("B3:B") I have to say
from the active cell to the cell of last row

I need to avoid (("B3:B") and .Range("B3"). Instead I have to use active
cell




Dim Wks As Worksheet
Dim LastRow As Long

Set Wks = ActiveSheet

With ActiveSheet
LastRow = .Cells(.Rows.Count, "C").End(xlUp).Row
.Range("B3:B" & LastRow).FormulaR1C1 = .Range("B3").FormulaR1C1
End With

Dear dave thanks lote

Also Please I have to run the following macros only if the first digits of
data in that
cell is numeric , otherwise it should be blank.

Now it is working . But if there is no numeric data in the cell , the
result will be
'N/A' like that . That should be null . otherwise that should be replaced
with null or empty spaces

Also if there is alphabets in the first digits of the cell , the result
will be null from the Lookup function. Please help



myFormula = "=LOOKUP(6.022*10^23,--MID(RC[1]," _
& "MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},RC[1]" _
& "&""0123456789"")),ROW(INDIRECT(""1:""&LEN(RC[1])))))"

.Range(.Cells(2, res), .Cells(LastRow, res)).FormulaR1C1 = myFormula

"Dave Peterson" wrote:

You have multiple responses to your multiple posts.

pol wrote:

I have the following formula in the active cell
ActiveCell.FormulaR1C1 = _

"=LOOKUP(6.022*10^23,--MID(RC[1],MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},RC[1]&""0123456789"")),ROW(INDIRECT(""1:""&LEN(RC[1])))))"

Please help me to copy the above forumula to the following cells in the same
column by using the following macros . But I have to refer the active cell
formula instead of refering a particular cell like (("B3:B") I have to say
from the active cell to the cell of last row

I need to avoid (("B3:B") and .Range("B3"). Instead I have to use active
cell


Dim Wks As Worksheet
Dim LastRow As Long

Set Wks = ActiveSheet

With ActiveSheet
LastRow = .Cells(.Rows.Count, "C").End(xlUp).Row
.Range("B3:B" & LastRow).FormulaR1C1 = .Range("B3").FormulaR1C1
End With

--

Dave Peterson

myFormula = "=LOOKUP(6.022*10^23,--MID(RC[1]," _
& "MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},RC[1]" _
& "&""0123456789"")),ROW(INDIRECT(""1:""&LEN(RC[1])))))"

becomes

myFormula = "=IF(ISNUMBER(-LEFT(RC[1])),LOOKUP(6.022*10^23," _
& "--MID(RC[1],MIN(SEARCH({0,1,2,3,4,5,6,7,8,9}," _
& "RC[1]&""0123456789""))," _
& "ROW(INDIRECT(""1:""&LEN(RC[1]))))),"""")"

pol wrote:

Dear dave thanks lote

Also Please I have to run the following macros only if the first digits of
data in that
cell is numeric , otherwise it should be blank.

Now it is working . But if there is no numeric data in the cell , the
result will be
'N/A' like that . That should be null . otherwise that should be replaced
with null or empty spaces

Also if there is alphabets in the first digits of the cell , the result
will be null from the Lookup function. Please help

myFormula = "=LOOKUP(6.022*10^23,--MID(RC[1]," _
& "MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},RC[1]" _
& "&""0123456789"")),ROW(INDIRECT(""1:""&LEN(RC[1])))))"

.Range(.Cells(2, res), .Cells(LastRow, res)).FormulaR1C1 = myFormula

"Dave Peterson" wrote:

You have multiple responses to your multiple posts.

pol wrote:

I have the following formula in the active cell
ActiveCell.FormulaR1C1 = _

"=LOOKUP(6.022*10^23,--MID(RC[1],MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},RC[1]&""0123456789"")),ROW(INDIRECT(""1:""&LEN(RC[1])))))"

Please help me to copy the above forumula to the following cells in the same
column by using the following macros . But I have to refer the active cell
formula instead of refering a particular cell like (("B3:B") I have to say
from the active cell to the cell of last row

I need to avoid (("B3:B") and .Range("B3"). Instead I have to use active
cell


Dim Wks As Worksheet
Dim LastRow As Long

Set Wks = ActiveSheet

With ActiveSheet
LastRow = .Cells(.Rows.Count, "C").End(xlUp).Row
.Range("B3:B" & LastRow).FormulaR1C1 = .Range("B3").FormulaR1C1
End With

--

Dave Peterson


--

Dave Peterson