evaluating variables in string vba excel

Question

sReport = "EFGHIJKLM"
sSource = "FGHIJKLMN"

For J = 5 To 18
    For I = 1 To Len(sReport)

wbTarget.Sheets(sheetTargetName).Range(Mid(sReport, I, 1) & J) = "=HK_USD!(Mid(sSource, I, 1) & J+79)+London_USD!(Mid(sSource, I, 1) & J+79)+Labuan_USD!(Mid(sSource, I, 1) & J+79)+NY_USD!(Mid(sSource, I, 1) & J+79)+NISP_USD!F84+OWH_USD!(Mid(sSource, I, 1) & J+79)"

     Next
Next

I have this formula as shown in the line above. Basically, I want that line to evaluated as

wbTarget.Sheets(sheetTargetName).Range("E5") = "=HK_USD!F84+London_USD!F84+Labuan_USD!F84+NY_USD!F84+NISP_USD!F84+OWH_USD!D4"

and the Range will change to F5, G5, H5 etc because of the loops. The left side of the formula works. The problem is the one in the string. How can I change this code so that it will evaluate my variables in the string? Thanks

you can create an array for those strings and call them using the loop variable I. sReport=array("E","F","G","H","I","J","K","L","M") then call them as sReport(I) — Sivaprasath Vadivel
– Sivaprasath Vadivel, Commented Apr 6, 2017 at 6:53
re: '=HK_USD!(Mid(...' you cannot concatenate a string into a true cell reference without INDIRECT. However, INDIRECT is to be (best practices) avoided. Post the formula you want to appear and a better solution sgould be evident. — user4039065
– user4039065, Commented Apr 6, 2017 at 7:03
@Desmond, would you mind taking your time and close your previous questions? — user3598756
– user3598756, Commented Apr 6, 2017 at 8:40

Charles Williams · Accepted Answer · 2017-04-06 07:41:00Z

1

something like this

strFormula="=HK_USD!" & (Mid(sSource, I, 1) & J+79) & "+London_USD!" & (Mid(sSource, I, 1) & J+79) & " +Labuan_USD!" & (Mid(sSource, I, 1) & J+79) & " +NY_USD!" & (Mid(sSource, I, 1) & J+79) & "+NISP_USD!F84+OWH_USD!" & (Mid(sSource, I, 1) & J+79)
wbTarget.Sheets(sheetTargetName).Range(Mid(sReport, I, 1) & J)=strFormula

answered Apr 6, 2017 at 7:41

Charles Williams

23.6k5 gold badges40 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

evaluating variables in string vba excel

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related