Array for Multiple Sheets??

[Mr Denove]

Sub Macro9()
Dim wsO As Worksheet
Dim LR As Long
Dim i As Integer
Dim iws As Integer
Dim myPosition As Long

Application.ScreenUpdating = False

mySheets = Array(Sheet1, Sheet3)

For iws = 0 To UBound(mySheets) - 1

myColumns = Array("TransactionID", "order_id", "account", "amount", "CurrencyAmount", "SupplierID", "UNSPCLV1", "UNSPCLV2", "UNSPCLV3", "UNSPCLV4")
LR = Range("A" & Rows.Count).End(xlUp).Row

    For i = 0 To UBound(myColumns) - 1
        
        myPosition = WorksheetFunction.Match(myColumns(i), wsO.Range("A1:AC1"), 0)
               
        wsO.Cells(1, myPosition).Range("A2:A" & LR).Select

        Selection.NumberFormat = "0"
For Each xCell In Selection
    xCell.Value = CDec(xCell.Value)
 Next xCell
    
    Next i
    
    Next iws
    
Set wsO = Nothing
Application.ScreenUpdating = True

MsgBox "Complete"
End Sub

Im driving myself mad now, the array for the columns was compiling, but as soon as I add in the Array to use multiple sheets it all goes horribly wrong.
And I am not sure what I am doing that is causing the problem.
This is the first of several modules I need to create for a data tidy up and would appreciate if someone can point out the errors of my way.
Thanks in advance.
Stuart

 

[Mr Denove]

Hi Bertie

Yes I want to use the workbook code names as the names of the sheets will change every month this way it is future proof.

Tested your code amendments and it does compile except it is not formatting them to Number.

Stuart

 

[bertie]

OK, if you are using the sheet code names then, change the array back to:

   mySheets = Array(Sheet1, Sheet3)

Then when you set the sheet it's just:

   'process worksheet array
   For iws = LBound(mySheets) To UBound(mySheets)
      Set wsO = mySheets(iws)

As for the formatting, what are you trying to loop through here? This configuration looks weird.

            For Each xCell In wsO.Cells(1, myPosition).Range("A2:A" & LR)

The full amended code is now:

Option Explicit


Sub Macro9()
   Dim wsO As Worksheet
   Dim LR As Long          'last row in column A of the worksheet
   Dim i As Integer        'loop index
   Dim iws As Integer      'worksheet counter
   Dim myPosition As Long
   Dim mySheets As Variant
   Dim myColumns As Variant
   Dim xCell As Range
   Dim sheetName


   mySheets = Array(Sheet1, Sheet3)
 
   myColumns = Array("TransactionID", "order_id", "account", _
                     "amount", "CurrencyAmount", "SupplierID", _
                     "UNSPCLV1", "UNSPCLV2", "UNSPCLV3", "UNSPCLV4")


   Application.ScreenUpdating = False
   
   'process worksheet array
   For iws = LBound(mySheets) To UBound(mySheets)
      Set wsO = mySheets(iws)
      
      LR = wsO.Range("A" & Rows.Count).End(xlUp).Row
      wsO.Range("A2:A" & LR).NumberFormat = "0"
      
      'process column header array
      For i = LBound(myColumns) To UBound(myColumns)
         
         'set an error trap in case you don't find the header
         On Error Resume Next
         myPosition = WorksheetFunction.Match(myColumns(i), wsO.Range("A1:AC1"), 0)
                
         'check the column header was found
         If myPosition <> 0 Then
            wsO.Cells(1, myPosition).NumberFormat = "0"
            
            For Each xCell In wsO.Cells(1, myPosition).Range("A2:A" & LR)
                xCell.Value = CDec(xCell.Value)
            Next xCell
            myPosition = 0
         End If


      Next i
    
    Next iws


   MsgBox "Complete"


   Set wsO = Nothing
   Application.ScreenUpdating = True
End Sub

 

[Mr Denove]

Problem resolved Bertie, I just moved the line for number formatting to within the

For Each xCell In wsO.Cells(1, myPosition).Range("A2:A" & LR)
                xCell.Value = CDec(xCell.Value)
                xCell.NumberFormat = "0"
            Next xCell

and now works like a dream.

Just need to work through the rest of the sheets now.
Thanks for all your efforts in resolving this for me and hopefully for others to follow.

 

[Mr Denove]

Can I just ask what difference the 'Option Explicit' makes to the code?

 

[bertie]

Option Explicit means you have to declare all the variable in your procedures. It is of considerable help when it comes to debugging your code.

For example, if you didn't declare your variables then:
myfile and MyFile (note capital letters) would be treated as two different variables. Searching your code for typos such as this is extremely frustrating.

Example 2
If you type in mFile by mistake, "y" is missing, then Option Explicit will inform you that this variable has not been declared. i.e., you spot the typo.

Declaring all your variable is definitely good practice.

 

[Mr Denove]

Thanks for that Bertie.

One last question, which is related to this code, is how and where do I insert code to format the column named 'PayDate' in Sheet1 using

Selection.Replace What:=" **:**:****", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

from row 2 and still using the LR for last row populated earlier in the code?

 

[CharlesChuckieCharles]

How does this even work?

For Each xCell In wsO.Cells(1, myPosition).Range("A2:A" & LR)

you're stuck on Range("A2:A" & LR) ???

 

[Mr Denove]

This is the working version for reference

Option Explicit


Sub Macro9()
   Dim wsO As Worksheet
   Dim LR As Long          'last row in column A of the worksheet
   Dim i As Integer        'loop index
   Dim iws As Integer      'worksheet counter
   Dim myPosition As Long
   Dim mySheets As Variant
   Dim myColumns As Variant
   Dim xCell As Range
   
   'mySheets = Array("Sheet1", "Sheet3")
   mySheets = Array(1, 3)
   Set wsO = Worksheets(mySheets(iws))
   myColumns = Array("TransactionID", "order_id", "account", _
                     "amount", "CurrencyAmount", "SupplierID", _
                     "UNSPCLV1", "UNSPCLV2", "UNSPCLV3", "UNSPCLV4")

   Application.ScreenUpdating = False
   
   'process worksheet array
   For iws = LBound(mySheets) To UBound(mySheets)
      Set wsO = Sheets(mySheets(iws))
      LR = wsO.Range("A" & Rows.Count).End(xlUp).Row
      wsO.Range("A2:A" & LR).NumberFormat = "0"
      
      'process column header array
      For i = LBound(myColumns) To UBound(myColumns)
         
         'set an error trap in case you don't find the header
         On Error Resume Next
         myPosition = WorksheetFunction.Match(myColumns(i), wsO.Range("A1:AC1"), 0)
                
         'check the column header was found
         If myPosition <> 0 Then
                        
            For Each xCell In wsO.Cells(1, myPosition).Range("A2:A" & LR)
                xCell.Value = CDec(xCell.Value)
                xCell.NumberFormat = "0"
            Next xCell
            myPosition = 0
         End If

      Next i
    
    Next iws

  


   Set wsO = Nothing
   Application.ScreenUpdating = True
   
   MsgBox "Complete"
End Sub

 

[Mr Denove]

The next problem I have though is trying to include additional code that does something else i.e. its a Find and Replace function on one column (two different names) which appears in all sheets.

I have tried inserting the recorded code for this but its an epic fail.

Its the last piece I need to resolve a work issue and I hope someone can assist.

Many thanks in advance.
Stuart

 

[CharlesChuckieCharles]

Curiously

wsO.Cells(1, myPosition).Range("A2:A" & LR) this works

where Range("A2:A" & LR) acts as an Offset statement to Cells(1, myPosition)


I set MyPosition =9 ("I")

see below where i changed the range to B it moves from I to J

?wsO.Cells(1, myPosition).Range("A2:A" & LR).address
$I$2:$I$1027

?wsO.Cells(1, myPosition).Range("B2:B" & LR).address
$J$2:$J$1027


I would still recomend using the following

?wsO.Range(wsO.Cells(1,myPosition), wsO.cells(LR,myPosition)).Address
$I$1:$I$1027