ANALYSTBANK
Board Regular
- Joined
- Aug 16, 2013
- Messages
- 58
I have following formula in worksheet(RDRead)cell D2, which retrieves data from another worksheet(RD) and some controller reference from active sheet RDRead only.
above formula brings the correct result, when done manually, directly on worksheet. Row 1, is like header.
But when i route above forumla through VBA Macro as under, it brings absurd result.
Please help. Key issue is some Cells are referred from same worksheet, and some data is referred from another worksheet named RD from where data is retrieved/pulled in worksheet RDRead.
I think, i am forgetting some basic requirement of how absoulte and relative cell reference is made in VBA.
P.S.: I had made this request in other forum but having waited there for over a day, and seeing no response and no members online, i withdrew it from there, so there is no duplicates, trust no offence here. Look forward to learn and get solution.
Code:
=VALUE(SUBSTITUTE(SUBSTITUTE(MID(RD!$B1,FIND($D$1,RD!$B1)+12,(FIND("pChange",RD!B1)-(FIND($D$1,RD!$B1)+12))),",",""),"""",""))
above formula brings the correct result, when done manually, directly on worksheet. Row 1, is like header.
But when i route above forumla through VBA Macro as under, it brings absurd result.
Code:
Cells(lngRowsF, "D").FormulaR1C1 = _
"=VALUE(SUBSTITUTE(SUBSTITUTE(MID(RD!R[-1]C2,FIND(R1C4,RD!R[-1]C2)+12,(FIND(""pChange"",RD!R[-1]C[-2])-(FIND(R1C4,RD!R[-1]C2)+12))),"","",""""),"""""",""""))"
Please help. Key issue is some Cells are referred from same worksheet, and some data is referred from another worksheet named RD from where data is retrieved/pulled in worksheet RDRead.
I think, i am forgetting some basic requirement of how absoulte and relative cell reference is made in VBA.
P.S.: I had made this request in other forum but having waited there for over a day, and seeing no response and no members online, i withdrew it from there, so there is no duplicates, trust no offence here. Look forward to learn and get solution.