changes required in Macro

[amandeep08]

I have created below macro where the discounts will be calculated as per account numbers mentioned in another file but it is giving error if the account number is not found.

How to change the macro.

Sub Create_Workbook()
  Dim a As Variant, b As Variant, c As Variant
  Dim sh1 As Worksheet, sh2 As Worksheet, sh3 As Worksheet
  Dim wb3 As Workbook, dic As Object
  Dim i As Long, j As Long, k As Long
 
  Set sh1 = ThisWorkbook.Sheets("Data")
  Set sh2 = Workbooks("Master.xlsx").Sheets("Sheet1")
  Set dic = CreateObject("Scripting.Dictionary")
  Set wb3 = Workbooks.Add
  Set sh3 = wb3.Sheets(1)
 
  a = sh1.Range("A2:U" & sh1.Range("A" & Rows.Count).End(3).Row).Value2
  b = sh2.Range("A2:B" & sh2.Range("A" & Rows.Count).End(3).Row).Value2
  ReDim c(1 To UBound(a), 1 To 21)
 
  For i = 1 To UBound(b, 1)
    dic(b(i, 1)) = b(i, 2)
  Next
 
  For i = 1 To UBound(a, 1)
    j = j + 1
    c(j, 1) = a(i, 1)
    c(j, 2) = a(i, 2) & "-Disc"
    c(j, 3) = a(i, 3)
    c(j, 4) = a(i, 4)
    c(j, 5) = a(i, 5)
    c(j, 6) = a(i, 6)
    c(j, 7) = a(i, 7)
    c(j, 8) = a(i, 8)
    c(j, 9) = a(i, 9)
    c(j, 10) = a(i, 10)
    c(j, 11) = a(i, 11)
    c(j, 12) = a(i, 12) / dic(a(i, 9)) - a(i, 12)
    c(j, 13) = a(i, 13)
    c(j, 14) = a(i, 14)
    c(j, 15) = a(i, 15)
    c(j, 16) = a(i, 16)
    c(j, 17) = a(i, 17)
    c(j, 18) = a(i, 18)
    c(j, 19) = a(i, 19)
    c(j, 20) = a(i, 20)
    c(j, 21) = a(i, 21)
  Next
 
  sh3.Range("A1:U1").Value = sh1.Range("A1:U1").Value
  sh3.Range("A2").Resize(UBound(a), 21).Value = c
End Sub

 

[Norie]

Where are you storing the account numbers?

What do you want to happen when the account number isn't found?

 

[Peter_SSs]

First a few requests.

1. Please update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
   
   
2. When posting code please use code tags - see my signature block below. I have fixed this in your post.
   
   
3. When reporting a vba error, please give the full error message and state what line of code is causing the error

At some point, depending on your answer to Norie's second question, you need to check if the account number is in the dictionary. It might be something like this

If dic.exists(a(i, 9)) Then
  'Do something
Else
  'Do something else
End If

 

[amandeep08]

Where are you storing the account numbers?

What do you want to happen when the account number isn't found?

The account number is in another file.
If the account number is found, it will calculate the discount amount nd if not found macro will ignore that line item nd proceed further

 

[QuestionBaker]

if not account_Number is nothing then
    'account_Number is the range variable that you get from based on your search
    'Do something
end if

If you want to do something when it is not found then, add an else statement as shown by @Peter_SSs

if not account_Number is nothing then
    'account_Number is the range variable that you get from based on your search
    'Do something
else 
    'Do something else
end if