Absolute Reference to cell in different worksheet in VBA code

[ANALYSTBANK]

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.

=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.

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.

 

[ANALYSTBANK]

or is it the case that multiple usage of SUBSTITUTE, FIND, VALUE is causing some error

 

[steve the fish]

It would be useful in this case to have a bit of sample data to attempt to see what you are trying to achieve.

 

[ANALYSTBANK]

thanks @steve the fish,

here is the sample file with worksheet name RD (source worksheet) and RDRead (output worksheet).

Output worksheet, just for better understanding, I have currently put formula manually which brings the correct/desired result, when done manually.

But i want this to get updated through macro, hence sample macro is made 'test' which does not bring the data as desired.

Just in case you accidentally lose data, i have made extra sheet Sample output Data to restore manual formulas.

Thanks

Im getting message that i can not make attachment hence the excel file that i use in 97-2003 format is uploaded here

[URL]https://www.sendspace.com/file/franw4[/URL]

 

[ANALYSTBANK]

Look forward from expert to solution, I already shared what i seek and what efforts i made !

 

[steve the fish]

Ok you dont need to use loops to put formulas in the cells like that. Altering your sample code try this:

Dim lngRowsF As Long, sh As Worksheet
 
With Sheets("Sample OutputData")
    lngRowsF = .Cells(.Rows.Count, "A").End(xlUp).Row
    .Range("B2:B" & lngRowsF).FormulaR1C1 = "=MID(RD!R[-1]C2,FIND(R1C2,RD!R[-1]C2)+17,20)"
    .Range("C2:C" & lngRowsF).FormulaR1C1 = "=SUBSTITUTE(SUBSTITUTE(MID(RD!R[-1]C[-1],FIND(R1C3,RD!R[-1]C2)+13,(FIND(""bestSell"",RD!R[-1]C[-1])-(FIND(R1C3,RD!R[-1]C2)+13))),"","",""""),"""""""","""")"
    .Range("D2:D" & lngRowsF).FormulaR1C1 = "=VALUE(SUBSTITUTE(SUBSTITUTE(MID(RD!R[-1]C[-2],FIND(R1C4,RD!R[-1]C[-2])+12,(FIND(""pChange"",RD!R[-1]C[-2])-(FIND(R1C4,RD!R[-1]C[-2])+12))),"","",""""),"""""""",""""))"
    .Range("E2:E" & lngRowsF).FormulaR1C1 = "=VALUE(SUBSTITUTE(SUBSTITUTE(MID(RD!R[-1]C2,FIND(R1C5,RD!R[-1]C2)+12,(FIND(""closePrice"",RD!R[-1]C2)-(FIND(R1C5,RD!R[-1]C2)+12))),"","",""""),"""""""",""""))"
    .Range("F2:F" & lngRowsF).FormulaR1C1 = "=VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(MID(RD!R[-1]C2,FIND(R1C6,RD!R[-1]C2)+12,(FIND(""companyName"",RD!R[-1]C2)-(FIND(R1C6,RD!R[-1]C2)+12))),"","",""""),"""""""",""""),""}]"",""""))"
End With

 

[steve the fish]

Slightly different formulas which i would prefer to use:

With Sheets("Sample OutputData")
    lngRowsF = .Cells(.Rows.Count, "A").End(xlUp).Row
    .Range("B2:C" & lngRowsF).FormulaR1C1 = "=TRIM(LEFT(SUBSTITUTE(REPLACE(RD!R[-1]C2,1,FIND(R1C,RD!R[-1]C2)+LEN(R1C)+2,""""),"""""""",REPT("" "",99)),99))"
    .Range("D2:F" & lngRowsF).FormulaR1C1 = "=0+TRIM(LEFT(SUBSTITUTE(REPLACE(RD!R[-1]C2,1,FIND(R1C,RD!R[-1]C2)+LEN(R1C)+2,""""),"""""""",REPT("" "",99)),99))"
End With