Visual Basic

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