i wan to apportion discount between value

sandip265

New Member
Joined
Sep 2, 2020
Messages
1
Office Version
  1. 2019
Platform
  1. Windows
image.jpg

I have value in column "k" and discount in column "a"

i want formula in vba such that discount get apportion and then deducted from value example 33184-(1073/10334133184) or 12444-(1073/10334112444)

the result should come in column b result should be blank where their is date

i have problem in last if code please help

Sub MODULE1()



Dim i As Long
Dim lastrow As Long



lastrow = Cells(Rows.Count, "G").End(xlUp).Row

Range("B2:B" & lastrow).Value = Range("P2:R" & lastrow).Value

For i = 2 To lastrow

With Rows(i)

If Not IsEmpty(.Columns("F")) Then
Cells(i, "A").Value = Cells(i, "K").Value

End If


If Not IsEmpty(.Columns("F")) Then
If IsEmpty(.Columns("J")) Then .Columns("J").Value = "unregister"
Else
.Columns("I").Value = .Columns("I").Offset(-1, 0).Value
.Columns("J").Value = .Columns("J").Offset(-1, 0).Value
.Columns("L").Value = .Columns("L").Offset(-1, 0).Value
.Columns("Q").Value = .Columns("Q").Offset(-1, 0).Value
.Columns("R").Value = .Columns("R").Offset(-1, 0).Value
.Columns("P").Value = .Columns("P").Offset(-1, 0).Value
.Columns("B").Value = .Columns("B").Offset(-1, 0).Value
.Columns("A").Value = .Columns("A").Offset(-1, 0).Value
End If

If IsEmpty(.Columns("F")) Then
Cells(i, "A").Value = Cells(i, "k").Value - (Cells(i, "b").Value /
Cells(i, "a").Value) * Cells(i, "k").Value

End If

End With


Next i

With Range("I2:S" & lastrow)
.Font.Name = "ARIAL"
.Font.Size = "9"
End With
Range("I2:S" & lastrow).NumberFormat = "$#,##0.00" End Sub
i have problem in last if code
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

onlyadrafter

Well-known Member
Joined
Aug 19, 2003
Messages
5,703
Platform
  1. Windows
Hello,

can't work out the rest of your code, maybe I don't have all the data. However, replace

Code:
If IsEmpty(.Columns("F")) Then
Cells(i, "A").Value = Cells(i, "k").Value - (Cells(i, "b").Value /Cells(i, "a").Value) * Cells(i, "k").Value

End If

with

VBA Code:
If IsEmpty(.Columns("F")) Then
    If Range("A" & i) > 0 Then
        Range("B" & i).Value = Range("K" & i).Value - (Range("A" & i).Value / MY_TOTAL) * Range("K" & i).Value
    Else
        Range("B" & i).Value = Range("K" & i).Value
    End If
Else
    MY_TOTAL = Range("K" & i).Value
End If

I assume you are sharing out the 1073.8 over the 103341.3 then multiplying by 33184 etc

is this working as expected?
 

Watch MrExcel Video

Forum statistics

Threads
1,114,485
Messages
5,548,333
Members
410,828
Latest member
A9Bosv3
Top