macro to copy following cells in the same column

P

pol

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
 
D

Dave Peterson

You have multiple responses to your multiple posts.
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
Click to expand...
 
P

pol

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 said:
You have multiple responses to your multiple posts.
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
Click to expand...
Click to expand...
 
D

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]))))),"""")"
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 said:
You have multiple responses to your multiple posts.
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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

To store the formula in a string 4
To extract the number if there is niumeric in right most digits 4
To give paste special (value) in macros 1
IF function (copy cell) in a macro 4
Please help to to derive a formula 1
end it 2
Autofill & On Error Resume Next 2
Trying to run excel vba code when cell value changes from empty to entered value. 1

Top