CODEWITHSUNDEEP
excelvbaexcel-formula
Edward
Edward

Reputation: 896

Excel VBA code, formula and function, not working as expected

I have an excel formula I have used:

=IFNA(LEFT(INDIRECT(ADDRESS(3,MATCH("Target Ship*",Ed!1:1,0),,,"Ed")),
 FIND(" ",INDIRECT(ADDRESS(3,MATCH("Target Ship*",Ed!1:1,0),,,"Ed")))-1),"")

I am trying to get it to work in VBA, with a function that pulls the Sheet2 name, but with the code below it throws the error

"Compile Error: Syntax Error"

Worksheets(1).Cells(l + i * 2 - 2, j).Formula = "=IFNA(LEFT(INDIRECT(ADDRESS(3,MATCH(""Target Ship*"","&shtName&"!1:1,0),,,""&shtName&"")),FIND("" "",INDIRECT(ADDRESS(3,MATCH(""Target Ship*"","&shtName&"!1:1,0),,,""&shtName&"")))-1),"""")"

Also to the above my code below is not setting the shtName from the function.

shtName = sheetName(2)

Function sheetName(num As Integer) As String
    shtName = Sheets(num).Name
End Function

Upvotes: 0

Views: 145

Answers (1)

Tim Williams
Tim Williams

Reputation: 166126

I would favor use of tokens and Replace() over a bunch of concatenation:

Const FRM = _
  "=IFNA(LEFT(INDIRECT(ADDRESS(3,MATCH(""Target Ship*"",'<sht>'!1:1,0),,,""<sht>""))," & _
   "FIND("" "",INDIRECT(ADDRESS(3,MATCH(""Target Ship*"",'<sht>'!1:1,0),,,""<sht>"")))-1),"""")"

Dim f as String
f = Replace(FRM, "<sht>", sheetName(2))
f = Replace(f, "<x>", someOtherVariable) 'other substitutions as needed...

Worksheets(1).Cells(l + i * 2 - 2, j).Formula = f

Your function is not setting the return value correctly:

Function sheetName(num As Integer) As String
    sheetName = ThisWorkbook.Sheets(num).Name '<< sheetName not shtName
                '  ^ be specific about the workbook to look in
End Function

If you use Option Explicit it will warn you about this type of error since shtName is an undeclared variable in this context.

Upvotes: 5

Related Questions