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]

Thanks Charles

 

[bertie]

Also, thanks Charles. I have to admit I have never seen that code before.

Stuart,
Below is a find and replace procedure.

Sub FindAndReplace(ByVal ws As Worksheet, _
                   ByVal ColumnHeader As String, _
                   ByVal FindText As String, _
                   ByVal ReplaceText As String)
   
   Dim col As Long      'column number
   Dim rw As Long       'last row
   
   col = WorksheetFunction.Match(ColumnHeader, ws.Range("A1:AC1"), 0)
   rw = ws.Cells(ws.Rows.Count, col).End(xlUp).Row
   
   'find and replace
   With ws.Range(ws.Cells(1, col), ws.Cells(rw, col))
      .Replace What:=FindText, _
               Replacement:=ReplaceText, _
               LookAt:=xlPart, _
               SearchOrder:=xlByRows, _
               MatchCase:=False, _
               SearchFormat:=False, _
               ReplaceFormat:=False
   End With


End Sub

I set up a procedure to test the code, the part you are interested in is between the double line where the values are passed to the FindAndReplace procedure.

Sub test()
   Dim ws As Worksheet
   
   Set ws = Sheet1
   
   '===========================================
   FindAndReplace ws, _
                  ColumnHeader:="PayDate", _
                  FindText:="Bertie", _
                  ReplaceText:="New Text"
   '============================================
   
   Set ws = Nothing
End Sub

If you only have two columns to process, copy and paste the code between the two line,
make the necessary adjustments,
and run the procedure independently of the code we have been working with.


Can we incorporate this into the code we have been working with?
In post #5 you said this would work on column, "PayDate", in a later post you say it would apply to two columns.
PayDate is not one of the column headers in the column array. So I don't think we can incorporate the FindAndReplace into the earlier code.


If, on the other hand you want to FindAndReplace over two of the column headers within the columnArray then
Before the column array loop ends put in a Select Case statement, something like:

         '=================
         'Find and replace
         '================
         Select Case myColumns(i)
            'EXAMPLES
            Case "TransactionID", "order_id"
               FindAndReplace ws, _
                  ColumnHeader:=myColumns(i), _
                  FindText:="Bertie", _
                  ReplaceText:="New Text"
         End Select
      Next i   'loop for myColumns

 

[Mr Denove]

Hi Bertie,

the function I want to perform is Find and Replace on a column called either "PayDate" or "PaidDate" depending on which sheet its contained.
Is there anyway to include this so it completes each sheet before moving through the array or does it have to be a seperate task?

Thanks, Stuart

 

[bertie]

OK, If you it it to process on each worksheet in the sheet array then place the Find&Replace after the start of the outer loop, just after you SET the worksheet.

NB You say to process a column depending on which sheet is being processed. But you don't specify these dependencies. So the code segment below is entirely guesswork on my part, you will have to edit at your end.

Code Segment for Find And Replace

      '================================
      'Find and replace
      '================================
      If wsO.CodeName = "Sheet1" Then
         FindAndReplace wsO, _
            ColumnHeader:="PayDate", _
            FindText:="Bertie", _
            ReplaceText:="New Text sheet1"
            
      ElseIf wsO.CodeName = "Sheet3" Then
         FindAndReplace wsO, _
            ColumnHeader:="PaidDate", _
            FindText:="Bertie", _
            ReplaceText:="New Text sheet3"
      End If
      '================================

The working version of the code we are working with 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
   
   '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"
      
      '================================
      'Find and replace
      '================================
      If wsO.CodeName = "Sheet1" Then
         FindAndReplace wsO, _
            ColumnHeader:="PayDate", _
            FindText:="Bertie", _
            ReplaceText:="New Text sheet1"
            
      ElseIf wsO.CodeName = "Sheet3" Then
         FindAndReplace wsO, _
            ColumnHeader:="PaidDate", _
            FindText:="Bertie", _
            ReplaceText:="New Text sheet3"
      End If
      '================================
         
      '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   'loop for myColumns
    
    Next iws


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




Sub FindAndReplace(ByVal ws As Worksheet, _
                   ByVal ColumnHeader As String, _
                   ByVal FindText As String, _
                   ByVal ReplaceText As String)
   
   Dim col As Long      'column number
   Dim rw As Long       'last row
   
   col = WorksheetFunction.Match(ColumnHeader, ws.Range("A1:AC1"), 0)
   rw = ws.Cells(ws.Rows.Count, col).End(xlUp).Row
   
   'find and replace
   With ws.Range(ws.Cells(1, col), ws.Cells(rw, col))
      .Replace What:=FindText, _
               Replacement:=ReplaceText, _
               LookAt:=xlPart, _
               SearchOrder:=xlByRows, _
               MatchCase:=False, _
               SearchFormat:=False, _
               ReplaceFormat:=False
   End With
End Sub

 

[Mr Denove]

Hi Bertie,

Just to let you know I created a separate module for the date formatting and put this into a Call function.

The Macro is compiling as desired and changes a 30-40 min task into one click and a few seconds later voila....thank you.