Hi,

I have a worksheet which contains 3 tables, each of the same dimension (i.e. headings). Below is a slab of code which inserts a column and performs a trim and concatonate function of the three adjacent cells (being column C, D and E). Once done, it performs a vlookup function with another worksheet where the vlookup function applies to a number of columns.

My problem is that I'm trying to tidy up this code and make it more universal so that I don't have to constantly edit the code everytime the dimensions of the tables adjust slightly.

Futher, the trim concatenate function is tied together with the search function where it searches for the word currency and offsets to the next column. Is there a more clever way of writing this as the number of tables can change and I don't want to haev to copy and paste the same bit of code depending on the amount of tables for any particular week?

Thank you very much.


Sub GLVlookup()
'
' GLVlookup Macro
' Macro recorded 31/03/2008 by pvo2
'

'

    Range("E2:E60000").Select
    Selection.Insert Shift:=xlToRight
    Range("E2").Select
    ActiveCell.FormulaR1C1 = "ID"
    ActiveCell.Offset(1, 0).Select

    Do
    
        ActiveCell.FormulaR1C1 = "=TRIM(RC[-3]&RC[-2]&RC[-1])"
        ActiveCell.Offset(1, 0).Select
    
    Loop Until IsEmpty(ActiveCell.Offset(0, -1))
    
     Cells.Find(What:="Currency", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
         False, SearchFormat:=False).Activate
    ActiveCell.Offset(0, 1).Select
      
    ActiveCell.FormulaR1C1 = "ID"
    ActiveCell.Offset(1, 0).Select

    Do
    
        ActiveCell.FormulaR1C1 = "=TRIM(RC[-3]&RC[-2]&RC[-1])"
        ActiveCell.Offset(1, 0).Select
    
    Loop Until IsEmpty(ActiveCell.Offset(0, -1))
    
    Cells.Find(What:="Currency", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
         False, SearchFormat:=False).Activate
    ActiveCell.Offset(0, 1).Select
      
    ActiveCell.FormulaR1C1 = "ID"
    ActiveCell.Offset(1, 0).Select

    Do
    
        ActiveCell.FormulaR1C1 = "=TRIM(RC[-3]&RC[-2]&RC[-1])"
        ActiveCell.Offset(1, 0).Select
    
    Loop Until IsEmpty(ActiveCell.Offset(0, -1))
    
    Cells.Find(What:="GL Region", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
         False, SearchFormat:=False).Activate
    ActiveCell.Offset(1, 0).Select
    
    Cells.Find(What:="In Source Only", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
         False, SearchFormat:=False).Activate
    ActiveCell.Offset(1, 0).Select

Do

    ActiveCell.FormulaR1C1 = _
        "=IF(ISNA(VLOOKUP(RC[-7],'Pivot Table'!R2C1:R382C5,2,FALSE)),0,VLOOKUP(RC[-7],'Pivot Table'!R2C1:R382C5,2,FALSE))"
    ActiveCell.Offset(0, 2).Select
    
    ActiveCell.FormulaR1C1 = _
        "=IF(ISNA(VLOOKUP(RC[-9],'Pivot Table'!R2C1:R382C5,3,FALSE)),0,VLOOKUP(RC[-9],'Pivot Table'!R2C1:R382C5,3,FALSE))"
    ActiveCell.Offset(0, 2).Select
    
    ActiveCell.FormulaR1C1 = _
        "=IF(ISNA(VLOOKUP(RC[-11],'Pivot Table'!R2C1:R382C5,4,FALSE)),0,VLOOKUP(RC[-11],'Pivot Table'!R2C1:R382C5,4,FALSE))"
    ActiveCell.Offset(0, 2).Select
    
    ActiveCell.FormulaR1C1 = _
        "=IF(ISNA(VLOOKUP(RC[-13],'Pivot Table'!R2C1:R382C5,5,FALSE)),0,VLOOKUP(RC[-13],'Pivot Table'!R2C1:R382C5,5,FALSE))"
    ActiveCell.Offset(0, 2).Select
    
    ActiveCell.FormulaR1C1 = _
        "=IF(ISNA(VLOOKUP(RC[-15],'Pivot Table'!R2C1:R382C5,6,FALSE)),0,VLOOKUP(RC[-15],'Pivot Table'!R2C1:R382C5,6,FALSE))"
    ActiveCell.Offset(0, 2).Select
    
    ActiveCell.FormulaR1C1 = _
        "=IF(ISNA(VLOOKUP(RC[-17],'Pivot Table'!R2C1:R382C5,7,FALSE)),0,VLOOKUP(RC[-16],'Pivot Table'!R2C1:R382C5,7,FALSE))"
    ActiveCell.Offset(1, -10).Select
    
    
Loop Until IsEmpty(ActiveCell.Offset(0, -6))




Cells.Find(What:="Group", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
         False, SearchFormat:=False).Activate

Cells.Find(What:="In Source Only", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
         False, SearchFormat:=False).Activate
    ActiveCell.Offset(1, 0).Select


Do

    ActiveCell.FormulaR1C1 = _
        "=IF(ISNA(VLOOKUP(RC[-7],'Pivot Table'!R2C1:R382C5,2,FALSE)),0,VLOOKUP(RC[-7],'Pivot Table'!R2C1:R382C5,2,FALSE))"
    ActiveCell.Offset(0, 2).Select
    
    ActiveCell.FormulaR1C1 = _
        "=IF(ISNA(VLOOKUP(RC[-9],'Pivot Table'!R2C1:R382C5,3,FALSE)),0,VLOOKUP(RC[-9],'Pivot Table'!R2C1:R382C5,3,FALSE))"
    ActiveCell.Offset(0, 2).Select
    
    ActiveCell.FormulaR1C1 = _
        "=IF(ISNA(VLOOKUP(RC[-11],'Pivot Table'!R2C1:R382C5,4,FALSE)),0,VLOOKUP(RC[-11],'Pivot Table'!R2C1:R382C5,4,FALSE))"
    ActiveCell.Offset(0, 2).Select
    
    ActiveCell.FormulaR1C1 = _
        "=IF(ISNA(VLOOKUP(RC[-13],'Pivot Table'!R2C1:R382C5,5,FALSE)),0,VLOOKUP(RC[-13],'Pivot Table'!R2C1:R382C5,5,FALSE))"
    ActiveCell.Offset(0, 2).Select
    
    ActiveCell.FormulaR1C1 = _
        "=IF(ISNA(VLOOKUP(RC[-15],'Pivot Table'!R2C1:R382C5,6,FALSE)),0,VLOOKUP(RC[-15],'Pivot Table'!R2C1:R382C5,6,FALSE))"
    ActiveCell.Offset(0, 2).Select
    
    ActiveCell.FormulaR1C1 = _
        "=IF(ISNA(VLOOKUP(RC[-17],'Pivot Table'!R2C1:R382C5,7,FALSE)),0,VLOOKUP(RC[-16],'Pivot Table'!R2C1:R382C5,7,FALSE))"
    ActiveCell.Offset(1, -10).Select
    
    
Loop Until IsEmpty(ActiveCell.Offset(0, -6))

    
    

Cells.Find(What:="External", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
         False, SearchFormat:=False).Activate

Cells.Find(What:="In Source Only", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
         False, SearchFormat:=False).Activate
    ActiveCell.Offset(1, 0).Select


Do

    ActiveCell.FormulaR1C1 = _
        "=IF(ISNA(VLOOKUP(RC[-7],'Pivot Table'!R2C1:R382C5,2,FALSE)),0,VLOOKUP(RC[-7],'Pivot Table'!R2C1:R382C5,2,FALSE))"
    ActiveCell.Offset(0, 2).Select
    
    ActiveCell.FormulaR1C1 = _
        "=IF(ISNA(VLOOKUP(RC[-9],'Pivot Table'!R2C1:R382C5,3,FALSE)),0,VLOOKUP(RC[-9],'Pivot Table'!R2C1:R382C5,3,FALSE))"
    ActiveCell.Offset(0, 2).Select
    
    ActiveCell.FormulaR1C1 = _
        "=IF(ISNA(VLOOKUP(RC[-11],'Pivot Table'!R2C1:R382C5,4,FALSE)),0,VLOOKUP(RC[-11],'Pivot Table'!R2C1:R382C5,4,FALSE))"
    ActiveCell.Offset(0, 2).Select
    
    ActiveCell.FormulaR1C1 = _
        "=IF(ISNA(VLOOKUP(RC[-13],'Pivot Table'!R2C1:R382C5,5,FALSE)),0,VLOOKUP(RC[-13],'Pivot Table'!R2C1:R382C5,5,FALSE))"
    ActiveCell.Offset(0, 2).Select
    
    ActiveCell.FormulaR1C1 = _
        "=IF(ISNA(VLOOKUP(RC[-15],'Pivot Table'!R2C1:R382C5,6,FALSE)),0,VLOOKUP(RC[-15],'Pivot Table'!R2C1:R382C5,6,FALSE))"
    ActiveCell.Offset(0, 2).Select
    
    ActiveCell.FormulaR1C1 = _
        "=IF(ISNA(VLOOKUP(RC[-17],'Pivot Table'!R2C1:R382C5,7,FALSE)),0,VLOOKUP(RC[-16],'Pivot Table'!R2C1:R382C5,7,FALSE))"
    ActiveCell.Offset(1, -10).Select
    
    
Loop Until IsEmpty(ActiveCell.Offset(0, -6))

End Sub