Find and Replace Method

SMI// · January 11th, 2016, 03:25 AM

Dear all,
I have deployed a Find method but that has made the code bit lengthier. Instead I want to use the find and replace methods using an array variable. My approach would be to:

1) First find a list of values stored within my srchString variable
2) Select all those columns
3) Cut all those columns
4) Move them to extreme left using Insert Shift:=xlToRight method

Note that srchString and replaceString variables are there just for reference for the time being but I intend to use them with find and replace methods.

Thanks in advance.

SMI//

Code:

Dim xRows, finalRow  As Single
Dim StartDate, EndDate As Date

xRows = Rows.Count
finalRow = Range("A" & xRows).End(xlUp).Row

StartDate = Application.WorksheetFunction.Min(Range(Cells(2, 2), Cells(finalRow, 2)))
EndDate = Application.WorksheetFunction.Max(Range(Cells(2, 2), Cells(finalRow, 2)))

srchString = Array("invoice_number", "invoice_date", "Certificate/Report_No", "Bill To Customer", "original_order_number", "item_description", _
"rev_seg04_activity", "oh_attribute13", "header_dff_value11", "oh_attribute15", "pre_tax_amount_func", "tax_charged_func", "total_amount_func")

replaceString = Array("Invoice #", "Invoice Date", "OGC Job#", "Customer", "Boss Order#", "Item Type", _
"GFM Code", "Vessel", "Product", "Division", "Net Amount", "Tax Amount", "Total Amount (PKR)")

With ActiveSheet.Range("a1:ho1")

    Set C = .Find("Certificate/Report_No", LookIn:=xlValues)
    Range(C.Address).EntireColumn.Cut
    Columns("C:C").Insert Shift:=xlToRight
    '--------------------------------------------
    Set C = .Find("Bill To Customer", LookIn:=xlValues)
    Range(C.Address).EntireColumn.Cut
    Columns("D:D").Insert Shift:=xlToRight
    '--------------------------------------------
    Set C = .Find("original_order_number", LookIn:=xlValues)
    Range(C.Address).EntireColumn.Cut
    Columns("E:E").Insert Shift:=xlToRight
    '--------------------------------------------
    Set C = .Find("item_description", LookIn:=xlValues)
    Range(C.Address).EntireColumn.Cut
    Columns("F:F").Insert Shift:=xlToRight
    '--------------------------------------------
    Set C = .Find("rev_seg04_activity", LookIn:=xlValues)
    Range(C.Address).EntireColumn.Cut
    Columns("G:G").Insert Shift:=xlToRight
    '--------------------------------------------
    Set C = .Find("header_dff_value11", LookIn:=xlValues)
    Range(C.Address).EntireColumn.Cut
    Columns("H:H").Insert Shift:=xlToRight
    '--------------------------------------------
    Set C = .Find("oh_attribute13", LookIn:=xlValues)
    Range(C.Address).EntireColumn.Cut
    Columns("I:I").Insert Shift:=xlToRight
    '--------------------------------------------
    Set C = .Find("oh_attribute15", LookIn:=xlValues)
    Range(C.Address).EntireColumn.Cut
    Columns("J:J").Insert Shift:=xlToRight
    '--------------------------------------------
    Set C = .Find("pre_tax_amount_func", LookIn:=xlValues)
    Range(C.Address).EntireColumn.Cut
    Columns("K:K").Insert Shift:=xlToRight
    '--------------------------------------------
    Set C = .Find("tax_charged_func", LookIn:=xlValues)
    Range(C.Address).EntireColumn.Cut
    Columns("L:L").Insert Shift:=xlToRight
    '--------------------------------------------
    Set C = .Find("total_amount_func", LookIn:=xlValues)
    Range(C.Address).EntireColumn.Cut
    Columns("M:M").Insert Shift:=xlToRight


 End With