Visual Basic

Public xlsBookFds As Excel.Workbook
    Public xlsSheetFds As Excel.Worksheet
    Public xlsCellFds As Excel.Range

    Public xlsBookTs As Excel.Workbook
    Public xlsSheetTs As Excel.Worksheet
    Public xlsCellTs As Excel.Range
    Public xlsCellTs1 As Excel.Range

    Public xlsBookMdrs As Excel.Workbook
    Public xlsSheetMdrs As Excel.Worksheet
    Public xlsCellMdrs As Excel.Range

.Range("SheetName!A" & NRow).

!

.Range("BookName!SheetName!A" & NRow).

frankiebaby2 wrote:

is the !the code so the computer can seperate the sheet name from the cell location?

If myString.StartsWith("BEN*JE*ZZ*") Then
    ....

IvanIT wrote:

save and except the conditional values of myString.

Dim knownChars() As String = {"BEN*JE*ZZ*"}
Dim lines() As String = newString.ToString().Split(knownChars, StringSplitOptions.None)

''' If the long formula can be broken into parts, where the second part can
''' be replaced by a dummy function, this approach can be used...
Public Sub LongArrayFormula()
    Dim theFormulaPart1 As String
    Dim theFormulaPart2 As String
    theFormulaPart1 = "=IF(MONTH(DATE(YEAR(NOW()),MONTH(NOW()),1))-" & _
                          "MONTH(DATE(YEAR(NOW()),MONTH(NOW()),1)-" & _
                          "(WEEKDAY(DATE(YEAR(NOW()),MONTH(NOW()),1))-1)+" & _
                          "{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1),""""," & _
                          "X_X_X())"
                          
    theFormulaPart2 = "DATE(YEAR(NOW()),MONTH(NOW()),1)-" & _
                      "(WEEKDAY(DATE(YEAR(NOW()),MONTH(NOW()),1))-1)+" & _
                      "{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1)"
        
    With ActiveSheet.Range("E2:K7")
        .FormulaArray = theFormulaPart1
        .Replace "X_X_X())", theFormulaPart2
        .NumberFormat = "mmm dd"
    End With
    
End Sub