Djblois
07-03-2006, 06:48 AM
First, I would like to thank everyone for all the help I have gotten. It is really coming along and I am learning alot along the way. However, there is more I want to do to this macro. I think if I show a piece of my code, then explain what I want to do, it will make more sense.
Detail.Columns("E:E").Insert Shift:=xlToRight
Detail.Range("E1").FormulaR1C1 = "Customer"
Detail.Range("E2:E" & Detail.Cells(Detail.Rows.Count, 1).End(xlUp).Row).FormulaR1C1 = _
"=IF(ISNA(VLOOKUP(RC[-1],'[Atalanta Codes.xls]Cust'!C1:C9,2,FALSE)), VLOOKUP(RC[-1],Cust!C[-3]:C[-2],2,FALSE), VLOOKUP(Detail!RC[-1],'[Atalanta Codes.xls]Cust'!C1:C9,2,FALSE))"
Detail.Range("E1:E" & Detail.Cells(Detail.Rows.Count, 1).End(xlUp).Row).Value = _
Detail.Range("E1:E" & Detail.Cells(Detail.Rows.Count, 1).End(xlUp).Row).Value
As you can see in my formula I do a vlookup in Atalanta codes first then I do a vlookup in Cust tab. After that I want it to look at the cust numbers in the cust tab and add it to the atalanta codes if they are not in there (along with there customer names as well.
Detail.Columns("E:E").Insert Shift:=xlToRight
Detail.Range("E1").FormulaR1C1 = "Customer"
Detail.Range("E2:E" & Detail.Cells(Detail.Rows.Count, 1).End(xlUp).Row).FormulaR1C1 = _
"=IF(ISNA(VLOOKUP(RC[-1],'[Atalanta Codes.xls]Cust'!C1:C9,2,FALSE)), VLOOKUP(RC[-1],Cust!C[-3]:C[-2],2,FALSE), VLOOKUP(Detail!RC[-1],'[Atalanta Codes.xls]Cust'!C1:C9,2,FALSE))"
Detail.Range("E1:E" & Detail.Cells(Detail.Rows.Count, 1).End(xlUp).Row).Value = _
Detail.Range("E1:E" & Detail.Cells(Detail.Rows.Count, 1).End(xlUp).Row).Value
As you can see in my formula I do a vlookup in Atalanta codes first then I do a vlookup in Cust tab. After that I want it to look at the cust numbers in the cust tab and add it to the atalanta codes if they are not in there (along with there customer names as well.