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

muhammad susanto

Well-known Member
Joined
Jan 8, 2013
Messages
1,798
Office Version
  1. 2013
Platform
  1. Windows
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
VBA 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 ;
VBA Code:
"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
 

Attachments

  • array_problem.png
    array_problem.png
    7.4 KB · Views: 5

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,455
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.

VBA Code:
    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

Well-known Member
Joined
Jan 8, 2013
Messages
1,798
Office Version
  1. 2013
Platform
  1. Windows
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)?
 

Watch MrExcel Video

Forum statistics

Threads
1,127,263
Messages
5,623,704
Members
415,983
Latest member
MusicMan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top