Hi all, Please see the macro below which is sent to me by one of
online friend

Sub MultAmt()
Const x = 12
Dim i As Integer
Dim LRow As Long
Dim rng As Range, c As Range
Dim rng2 As Range, c2 As Range

LRow = Sheets("FORM").Cells(Rows.Count, "A").End(xlUp).Row
Set rng = Sheets("FORM").Range("D2:D" & LRow)
LRow = Sheets("RESULT BY MACRO").Cells(Rows.Count, "A").End(xlUp).Row
If LRow = 1 Then
'do nothing
Else
Sheets("RESULT BY MACRO").Rows("2:" & LRow).Delete
End If
i = 1
For Each c In rng
LRow = Sheets("RESULT BY MACRO").Cells(Rows.Count,
"A").End(xlUp).Row
c.EntireRow.Copy Sheets("RESULT BY MACRO").Range("A" & LRow +
i & ":A" & LRow + x)
Next
LRow = Sheets("RESULT BY MACRO").Cells(Rows.Count, "A").End(xlUp).Row
Set rng = Sheets("RESULT BY MACRO").Range("D2:D" & LRow)
LRow = Sheets("PERCENTAGES DATA").Cells(Rows.Count, "A").End(xlUp).Row
Set rng2 = Sheets("PERCENTAGES DATA").Range("A3:M" & LRow)
i = 2
For Each c In rng
c.Offset(, 1).Value = c.Offset(, 1).Value * _
(Application.WorksheetFunction.VLookup(c.Value, rng2, i,
False) / 100)
c.Offset(, 3).Value = Sheet1.Cells(2, i).Value
If i = 13 Then
i = 2
Else
i = i + 1
End If
Next
End Sub

my question is that in macro above where it say

For Each c In rng
c.Offset(, 1).Value = c.Offset(, 1).Value * _
(Application.WorksheetFunction.VLookup(c.Value, rng2, i,
False) / 100)

What code should i add which should do the job

if c.Offset(, 1).Value * _
(Application.WorksheetFunction.VLookup(c.Value, rng2, i,
False) / 100) = #N/A Then
Msgbox "not found" & (code number which is not found)
Exit Sub
Else
c.Offset(, 1).Value = c.Offset(, 1).Value * _
(Application.WorksheetFunction.VLookup(c.Value, rng2, i,
False) / 100)

to more understand my question you can check the file which i have
uploaded ( http://www.savefile.com/files/1535694 ) and you can put
the macro above in Module of that sheet. The macro above workes
perfectly fine i just need to know how can i add Msgbox line which i
mention above. Please if any friend can help

To answer your immediate question modify this to suit. Look for the error on
JUST the VLookup and use ISERROR. However, the whole thing could be better
by using vba FIND

Sub ifna()
Set myrng = Range("a1:b3")
If IsError(Application.VLookup(1, myrng, 2,0)) Then
MsgBox "hI"
Else
MsgBox Application.VLookup(1, myrng, 2,0)
End If
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"K" wrote in message
...
Hi all, Please see the macro below which is sent to me by one of
online friend

Sub MultAmt()
Const x = 12
Dim i As Integer
Dim LRow As Long
Dim rng As Range, c As Range
Dim rng2 As Range, c2 As Range

LRow = Sheets("FORM").Cells(Rows.Count, "A").End(xlUp).Row
Set rng = Sheets("FORM").Range("D2:D" & LRow)
LRow = Sheets("RESULT BY MACRO").Cells(Rows.Count, "A").End(xlUp).Row
If LRow = 1 Then
'do nothing
Else
Sheets("RESULT BY MACRO").Rows("2:" & LRow).Delete
End If
i = 1
For Each c In rng
LRow = Sheets("RESULT BY MACRO").Cells(Rows.Count,
"A").End(xlUp).Row
c.EntireRow.Copy Sheets("RESULT BY MACRO").Range("A" & LRow +
i & ":A" & LRow + x)
Next
LRow = Sheets("RESULT BY MACRO").Cells(Rows.Count, "A").End(xlUp).Row
Set rng = Sheets("RESULT BY MACRO").Range("D2:D" & LRow)
LRow = Sheets("PERCENTAGES DATA").Cells(Rows.Count, "A").End(xlUp).Row
Set rng2 = Sheets("PERCENTAGES DATA").Range("A3:M" & LRow)
i = 2
For Each c In rng
c.Offset(, 1).Value = c.Offset(, 1).Value * _
(Application.WorksheetFunction.VLookup(c.Value, rng2, i,
False) / 100)
c.Offset(, 3).Value = Sheet1.Cells(2, i).Value
If i = 13 Then
i = 2
Else
i = i + 1
End If
Next
End Sub

my question is that in macro above where it say

For Each c In rng
c.Offset(, 1).Value = c.Offset(, 1).Value * _
(Application.WorksheetFunction.VLookup(c.Value, rng2, i,
False) / 100)

What code should i add which should do the job

if c.Offset(, 1).Value * _
(Application.WorksheetFunction.VLookup(c.Value, rng2, i,
False) / 100) = #N/A Then
Msgbox "not found" & (code number which is not found)
Exit Sub
Else
c.Offset(, 1).Value = c.Offset(, 1).Value * _
(Application.WorksheetFunction.VLookup(c.Value, rng2, i,
False) / 100)

to more understand my question you can check the file which i have
uploaded ( http://www.savefile.com/files/1535694 ) and you can put
the macro above in Module of that sheet. The macro above workes
perfectly fine i just need to know how can i add Msgbox line which i
mention above. Please if any friend can help