Turning a query formula into a working formula

1
votes

i need to turn a dynamic formula, made of a query into a real, working formula somehow. The formula looks as follows:

=INDEX('C:\EXCHANGER\[FracReport.xlsb]ProppantAndFluid'!B57;1;1)

And despite that it starts with "=" it does nothing until i do ctrl+H, replace "=" with "=". The VBA solution that i have found so far:

Dim a As String
Dim b As String
Dim c As String


Sheets("ProppantAndFluid").UsedRange.Replace What:="=", Replacement:="", LookAt:=xlPart
a = "="
b = ActiveSheet.Range("B10").Value
c = a & b

MsgBox c
ActiveSheet.Range("F10") = c

It msgboxes c alright, but when it comes to the actual concatenation of "=" and formula and assigning it to a range, it throws

Run-time error '1004': Application-defined or object-defined error

Any solutions for query obtained formulas, or workarounds for Application, object defined errors, please?

1
excelvba
what's the outcome of MsgBox c? – HTH
=INDEX('C:\EXCHANGER[FracReport.xlsb]ProppantAndFluid'!B57;1;1) – Андрей Романов
try replacing all ";" to ",". and there's a "\" missing between "EXHANGER" and "[FRAC..." – HTH
ActiveSheet.Range("F10") = c should be ActiveSheet.Range("F10").Formula = c – Variatus
@Variatus, that is not an issue: .Value property is implicitly assumed and it works as .Formula one, provided the Value meets formula syntax requirements, of course – HTH

1 Answers

0
votes

Thanx everyone for your help and giving me a good path for solving the problem, the ultimate code that makes a query generated formula work is this:

Dim a As String
Dim b As String
Dim c As String


Sheets("ProppantAndFluid").UsedRange.Replace What:="=", Replacement:="", LookAt:=xlPart
Sheets("ProppantAndFluid").UsedRange.Replace What:=";", Replacement:=",", 
LookAt:=xlPart
a = "="
b = ActiveSheet.Range("B10").Value
c = a & b

ActiveSheet.Range("B10") = c
Range("B10").FormulaLocal = Range("B10").FormulaLocal