0

I'm trying to automate the actions required to count the leads that come from an exported csv from Unbounce. I managed to create a macro to do that to all forms that have the same set of columns. However, some clients have different forms and that generates different sets of columns in the spreadsheets, so that when I apply the macro to these clients' spreadsheets the wrong columns are selected in the functions.

I need to be able to select the columns by the header name, so that i can apply the functions on the right columns even if they are not in the same position.

In my mind (not with the VBA syntax) it would be something like

if column header = "email" then execute function else search next column header

I have found these possible solutions Find Column Header By Name And Select All Data Below Column Header (Excel-VBA)

https://www.extendoffice.com/documents/excel/4879-excel-select-column-by-header-name.html

but I could not separate the part of the code that applies only to selecting the columns and then making the following functions understand that it should work with the result of the previous selection.

The functions I need to give the right selections of columns to are below. The goal is to remove duplicates form columns with header email, remove tests from columns with header name and email, and then count the leads with utm_source = facebook and utm_source = instagram.

 Range("G6").Select
    ActiveSheet.Range("$A$1:$AA$2130").RemoveDuplicates Columns:=13, Header:= _
        xlYes
    Cells.Select
    Selection.AutoFilter
    Range("M5").Select
    ActiveSheet.Range("$A$1:$AA$2130").AutoFilter Field:=13, Criteria1:= _
        "=*test*", Operator:=xlAnd
    Rows("2:2159").Select
    Selection.Delete Shift:=xlUp
    Range("M2127").Select
    ActiveSheet.Range("$A$1:$AA$2125").AutoFilter Field:=13
    ActiveSheet.Range("$A$1:$AA$2125").AutoFilter Field:=12, Criteria1:= _
        "=*test*", Operator:=xlAnd
    Rows("2:2155").Select
    Selection.Delete Shift:=xlUp
    ActiveSheet.Range("$A$1:$AA$2123").AutoFilter Field:=12
    Range("N8").Select
    Sheets.Add After:=ActiveSheet
    ActiveCell.FormulaR1C1 = "facebook"
    Range("B1").Select
    ActiveCell.FormulaR1C1 = _
        "=COUNTIF('unbounce??'!C[20],""facebook"")+COUNTIF('unbounce??'!C[20],""instagram"")"
BigBen
  • 46,229
  • 7
  • 24
  • 40
Felipecs
  • 1
  • 1
  • 5
    You can use `Range.Find` to find the header, and you normally don't need to `Select`. See [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) – BigBen May 16 '19 at 17:59
  • Possible duplicate of [Search for exact match of string in excel row using VBA Macro](https://stackoverflow.com/questions/14212967/search-for-exact-match-of-string-in-excel-row-using-vba-macro) – Cyril May 16 '19 at 18:07
  • 1
    If you want to pull columns by name, you can convert data to a table and access it through the listobjects properties in VBA – nutsch May 16 '19 at 18:19

0 Answers0