amandeep08
Board Regular
- Joined
- Mar 20, 2011
- Messages
- 130
- Office Version
- 365
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.
How to change the macro.
VBA Code:
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
Last edited by a moderator: