I'm trying to set the following formula to a cell using this simple code:
- Code: Select all
Dim ws = xls.Worksheets(2)
Dim wsSSC = xls.Worksheets(0)
For Each r In ws.Range("C2:C10000")
For Each c In r.Cells
If c.Value.Contains("POIBS") Then
Try
Dim formula = _
String.Concat("=HYPERLINK( CELL(", """", "address", """", ";INDIRECT(CONCATENATE(", """", "Active_SSC!", """", ";", """", "B", """", ";MATCH(D2;Active_SSC!B:B;) )));D2)")
'Working Excel Formula: =HYPERLINK( CELL("address";INDIRECT(CONCATENATE("Active_SSC!";"B";MATCH(D2;Active_SSC!B:B;) )));D2)
c.Formula = formula
Catch ex As Exception
Dim ErrorMessage = "Unexpected token.Unexpected token type: Identifier, string value: at position 26. Formula: ..."
End Try
End If
Next
Next
If gives me the error:
{"Unexpected token.Unexpected token type: Identifier, string value: at position 26. Formula: HYPERLINK( CELL("address";INDIRECT(CONCATENATE("Active_SSC!";"B";MATCH(D2;Active_SSC!B:B;) )));D2), Position: 26"}
Does anyone know how to do it?
Cheers