Below mentioned code will insert new column called ISR/CSSM Mapped based on column D and from D2 values. But i dont want to mention the D and D2 insted i want mention column heading then seach for lookup value. Can any one help me?



Sub InsertColumn()

Dim lLastRow As Long
Dim oWorksheet As Worksheet
Dim oLookupSheet As Worksheet
Dim rEmailRange As Range
Dim vCell As Variant
Dim lRowCount As Long

lRowCount = 2
Set oWorksheet = Worksheets("AP_Quotes_20140928-020000")
Set oLookupSheet = Worksheets("ISR CSSMs")

On Error Resume Next
oWorksheet.Range("E:E").Insert
oWorksheet.Cells(1, 5).Value = "ISR/CSSM Mapped"
lLastRow = oWorksheet.Cells(Rows.Count, 4).End(xlUp).Row
Set rEmailRange = oWorksheet.Range("d2:d" & lLastRow)

For Each vCell In rEmailRange
oWorksheet.Cells(lRowCount, 5).Value = Application.WorksheetFunction.VLookup(vCell.Value, oLookupSheet.Range("B2:C719"), 2, 0)
lRowCount = lRowCount + 1
Next vCell

End Sub

Attached Files

Delete Duplicate .zip (143.9 KB, 8 views)