Vba : Can't Added Cell in Array (.....), Maybe Too Long?

[muhammad susanto]

hi all..

i have code to extract data from a multiple sheet into one sheet.
this code work properly, but i have problem to adding cell in array..but cell to added is too long (maybe)
how to fix it :
this code

Sub Consolidate_Worksheets_To_Master_Using_Arrays()
    Dim ws          As Worksheet
    Dim sh          As Worksheet
    Dim a           As Variant
    Dim i           As Long
    Dim r           As Long
    
    Application.ScreenUpdating = False
        Set sh = ThisWorkbook.Worksheets("master")
        sh.Range("A1").CurrentRegion.Offset(1).ClearContents
        r = 2
        
        For Each ws In ThisWorkbook.Worksheets
            If ws.Name <> "master" And ws.Name <> "etc.." Then
                With ws
                    a = Array("C25", "C47", "C69", , , , , "etc")
                    For i = LBound(a) To UBound(a)
                        a(i) = .Range(a(i)).Value
                        If i >= 10000 Then a(i) = Val(AlphaNum(CStr(a(i))))
                    Next i
                    sh.Range("A" & r).Resize(, UBound(a) + 1).Value = a
                    r = r + 1
                End With
            End If
        Next ws
    Application.ScreenUpdating = True
End Sub
Function AlphaNum(txt As String, Optional numOnly As Boolean = True) As String
    With CreateObject("VBScript.RegExp")
        .Pattern = IIf(numOnly = True, "\D+", "-?\d+(\.\d+)?")
        .Global = True
        AlphaNum = .Replace(txt, "")
    End With
End Function

here this my new cell can be added in array ;

"C25","C47","C69","C91","C113","C135","C157","C179","C201","C223","C245","C267","C289","C311","C333","C355","C377","C399","C421","C443","C29","C51","C73","C95","C117","C139","C161","C183","C205","C227","C249","C271","C293","C315","C337","C359","C381","C403","C425","C447","C30","C52","C74","C96","C118","C140","C162","C184","C206","C228","C250","C272","C294","C316","C338","C360","C382","C404","C426","C448","C26","C48","C70","C92","C114","C136","C158","C180","C202","C224","C246","C268","C290","C312","C334","C356","C378","C400","C422","C444","C27","C49","C71","C93","C115","C137","C159","C181","C203","C225","C247","C269","C291","C313","C335","C357","C379","C401","C423","C445","C33","C55","C77","C99","C121","C143","C165","C187","C209","C231","C253","C275","C297","C319","C341","C363","C385","C407","C429","C451","E33","E55","E77","E99","E121","E143","E165","E187","E209","E231","E253","E275","E297","E319","E341","E363","E385","E407","E429","E451","E31","E53","E75","E97","E119","E141","E163","E185","E207","E229","E251","E273","E295","E317","E339","E361","E383","E405","E427","E449","E34","E56","E78","E100","E122","E144","E166","E188","E210","E232","E254","E276","E298","E320","E342","E364","E386","E408","E430","E452","C37","C59","C81","C103","C125","C147","C169","C191","C213","C235","C257","C279","C301","C323","C345","C367","C389","C411","C433","C455","C43","C65","C87","C109","C131","C153","C175","C197","C219","C241","C263","C285","C307","C329","C351","C373","C395","C417","C439","C461"

how to make it well..
i attach problem with picture too.
thank in advance.

.sst

 

[Domenic]

Try using the line continuation character sequence, which is a space followed by an underscore. Here's an example with only a few lines. You can add the rest accordingly.

    a = Array("C25", "C47", "C69", "C91", "C113", "C135", "C157", "C179", "C201", "C223", _
              "C245", "C267", "C289", "C311", "C333", "C355", "C377", "C399", "C421", "C443", _
              "C29", "C51", "C73", "C95", "C117", "C139", "C161", "C183", "C205", "C227")

Hope this helps!

 

[muhammad susanto]

hi Domenic...
My code by default can convert or transpose data to be as general and for data number can be convert to be number (general format).
i have problem, for data like "= 10.785.000.000,00" without quote can not be convert / tranpose to be as number. The data contains mark =. dot, comma, excesive spaces.
How to make that data can be convert (result of transpose) to be number 10785000000, and how to modify and adding above code (Posting #1)?