CODEWITHSUNDEEP
vbaexcel
Rob
Rob

Reputation: 85

VBA to input a formula not working as expected

I've never used a VBA macro to input a formula. Does anyone have any idea why this doesn't work? I was under the impression that the double quotes should fix my original problem of only showing up to the second quote - but it returns an error.

Sub Macro()

Worksheets("Summary").Range("H3").Formula "=EXACT(G3, COUNTIFS((INDIRECT(CONCATENATE("" '"", RIGHT(B3, LEN(B3) - FIND(""- "", B3) - 1), ""'!"", ""K:K""))), D3, (INDIRECT(CONCATENATE(""'"", RIGHT(B3, LEN(B3) - FIND(""- "", B3) - 1), ""'!"", ""g:g""))), E3, (INDIRECT(CONCATENATE(""'"", RIGHT(B3, LEN(B3) - FIND(""- "", B3) - 1), ""'!"", ""j:j""))), F3))"

End Sub

Upvotes: 0

Views: 139

Answers (1)

timthebomb
timthebomb

Reputation: 230

You will need to modify the formula to add a "=" before the formula begins.

Worksheets("Summary").Range("H3").Formula = "=EXACT(G3, COUNTIFS((INDIRECT(CONCATENATE("" '"", RIGHT(B3, LEN(B3) - FIND(""- "", B3) - 1), ""'!"", ""K:K""))), D3, (INDIRECT(CONCATENATE(""'"", RIGHT(B3, LEN(B3) - FIND(""- "", B3) - 1), ""'!"", ""g:g""))), E3, (INDIRECT(CONCATENATE(""'"", RIGHT(B3, LEN(B3) - FIND(""- "", B3) - 1), ""'!"", ""j:j""))), F3))"

Upvotes: 2

Related Questions