Convert Array set elements to Excel sheet and allow to user to add new data

Hi,

I have "array" set which representing branches name. For the time being, there are 4 branches and it expected to be grown in future. Using "array" set code in VBA would not help user if there are new branches to be added.

Thus, my idea to convert "array" set data to excel sheet and allow user to add new data using Userform command button. I am familiar with that approach but my problem is how to convert array set elements to excel sheet and the VBA code can also be reflected with new data added by user

VBA code will perform the following function,
Opening Workbook
Filtering Criteria
Deleting Worksheet
as described below.


e.g.
Array set elements to be stored in Worksheets("Data") starting from A2.

A2 Battambang
A3 Chbar Ampov
A4 Chroy Changvar
A5 Kampong Cham
A6 .......
A7 .......
A?? ........ new data to be added by user

Sub Test()

strPath = ThisWorkbook.Path & "\"
    ASC = Array("Battambang.xlsx", _
                "Chbar Ampov.xlsx", _
                "Chroy Changvar.xlsx", _
                "Kampong Cham.xlsx")
                  
                  
For i = LBound(ASC) To UBound(ASC)
    Workbooks.Open Filename:=strPath & ASC(i)
    ActiveWindow.WindowState = xlMinimized
Next i
                  
                                  
    If ws.Name = "Battambang" Then
            With rng
                 .AutoFilter
                 .AutoFilter Field:=4, Criteria1:="Battambang"
                 .AutoFilter Field:=28, Criteria1:=">0"
            End With
    
    ElseIf ws.Name = "Chbar Ampov" Then
            With rng
                 .AutoFilter
                 .AutoFilter Field:=4, Criteria1:="Chbar Ampov"
                 .AutoFilter Field:=28, Criteria1:=">0"
            End With
     
    ElseIf ws.Name = "Chroy Changvar" Then
            With rng
                 .AutoFilter
                 .AutoFilter Field:=4, Criteria1:="Chroy Changvar"
                 .AutoFilter Field:=28, Criteria1:=">0"
            End With
                    
    ElseIf ws.Name = "Kampong Cham" Then
            With rng
                 .AutoFilter
                 .AutoFilter Field:=4, Criteria1:="Kampong Cham"
                 .AutoFilter Field:=28, Criteria1:=">0"
            End With
    
Application.DisplayAlerts = False
Worksheets(Array("Battambang", "Chbar Ampov", "Chroy Changvar", "Kampong Cham")).Delete
Application.DisplayAlerts = True
End Sub

Hi Farid,

I'm not sure what you're attempting but maybe:

Sub FaridW(): Dim strPath As String, Asc, i As Integer, ws As Worksheet, rng As Range

                strPath = ThisWorkbook.Path & "\"
Asc = Array("Battambang.xlsx", "Chbar Ampov.xlsx", "Chroy Changvar.xlsx", "Kampong Cham.xlsx")
                  
For i = LBound(Asc) To UBound(Asc)
    Workbooks.Open FileName:=strPath & Asc(i)
    Set ws = ActiveSheet: Set rng = ws.Range("A1").CurrentRegion
    ActiveWindow.WindowState = xlMinimized
    
            With rng
                 .AutoFilter Field:=4, Criteria1:=Asc(i)
                 .AutoFilter Field:=28, Criteria1:=">0"
            End With
            
            Next i

End Sub

*do you want to leave the books open? What do you intend to do with the filtered data?

Okay from the last posted reply..
I have concluded this..( I hope this will help )
ASC= Range(Cells(ws.Range("M2").address),ws.Range("M" & Rows.count).End(xlUp).address)).value

Now ASC has been an array .. which you can refer like this..
ASC(R,1) where R is the row variable starting from 1 and 1 is the column Constant...


Regards,

Hi, Faridwahidi,

using Array for the area will just cover the first and the last elements as they are mentioned.

Maybe try:

ASC = Ws.Range("M2", ws.Range("M" & Rows.Count).End(xlUp)).Value

Ciao,
Holger

Maybe:

Asc = Array(ws.Range("M2:M" & ws.Range("M" & Rows.Count).End(xlUp).Row))

Sorry..
Its mistake..

Formula:

ASC = Range("M2:" & ws.Range("M" & Rows.count).End(xlUp).Address).Value

rest explanations are same..


Edit :- Tried to work upon Adept's Suggestion....

@ Vikas - Why do you keep putting Address in the code? - it's just redundant and, probably a breach of syntax.

@ Holger - I didn't realize that you had posted when I made my post.

Hi xladept,

I have tried to replace both of your array set code and also the one suggested by Holger, but not working. The file unable to be opened.
All elements have been placed to Worksheets("Data") starting from M2 to M5

M2 Battambang - BTM.xlsx
M3 Chbar Ampov - CBA.xlsx
M4 Chroy Changvar - CCV.xlsx
M5 Kampong Cham - KCM.xlsx

ASC = Array(ws.Range("M2:M" & ws.Range("M" & Rows.count).End(xlUp).Row))
ASC = ws.Range("M2", ws.Range("M" & Rows.count).End(xlUp)).Value

But the original Array set working fine.

Sub Test()
Dim strPath As String, ASC
Dim i As Integer
Dim ws As Worksheet

strPath = ThisWorkbook.Path & "\"
Set ws = Worksheets("Data")
ASC = Array("Battambang - BTM.xlsx", "Chbar Ampov - CBA.xlsx", "Chroy Changvar - CCV.xlsx", "Kampong Cham - KCM.xlsx") 
                
    For i = LBound(ASC) To UBound(ASC)
            Workbooks.Open Filename:=strPath & ASC(i)
            ActiveWindow.WindowState = xlMinimized
    Next i
End Sub

Hi, Faridwahidi,

the whole code you posted is worth a severe workover as it´s lacking lots of different things. Any reason for storing the values in a range on a worksheet and adding an Array for getting the values from these cells? None I´d say.

For Each rngCell In Range("M2", Range("M" & Rows.Count).End(xlUp))
  Workbooks.Open Filename:=strPath & rngCell
  ActiveWindow.WindowState = xlMinimized
Next rngCell

What is ws in your sample? You opened a certain number of worksheets but how do the worksheets are being looped as that part is lacking?
Why should the worksheets be deleted? In which workbook would we find the worksheets?

Ciao,
Holger

Hi Farid,

I still don't know what you are attempting - but for a worksheet array for column M from row 2 to the end this code works:

Asc = ws.Range("M2:M" & ws.Range("M" & Rows.Count).End(xlUp).Row)

You're welcome and thanks for the rep!

ASC =

The above construction used in several of the previous posts in this thread should probably be avoided since 'Asc' is a KEYWORD that represents a built in VBA function to convert the first letter in a string to an integer.

Option Explicit

Sub UseBuiltInFunctionAsc()

  Dim i As Integer
  
  i = Asc("C")   'The value of i is 67
  MsgBox "Asc(" & Chr(34) & "C" & Chr(34) & ") = " & i

End Sub

Lewis