Hello I would Like some help with my vba macro

[AhmedYasser]

I am trying to find a script that find the minimum Number in a certain column (H) and then select another column (M) and adds a value equal to another cell (Q)

EX:
pressing my button to find lowest value in column H and its H3 then it selects M3 and adds what I calculated on Q3 and so on till the sum in column M is equal to a certain value(cell F3)


I am not sure if I should be using solver or vba and I am still learning as much as I can any help would be appreciated


Image for my sheet

 

[mse330]

Hello Ahmed & welcome to Mr. Excel !

It is a bit difficult to make the code you need based on a picture provided specially that we don't know what are the underlaying formulas. I suggest that you upload a sample file online (Dropbox or any other sharing websites) & share the link with us here or use download the XL2BB excel add-in HERE

 

[AhmedYasser]

Hello Ahmed & welcome to Mr. Excel !

It is a bit difficult to make the code you need based on a picture provided specially that we don't know what are the underlaying formulas. I suggest that you upload a sample file online (Dropbox or any other sharing websites) & share the link with us here or use download the XL2BB excel add-in HERE

thanks for the information ! I am uploading it and re-editing my post

 

[AhmedYasser]

Turns out you can't edit after 10 min of posting sorry for the troublesome here is a direct link to my file!
Certified invoices update a(2).xlsx

 

[mse330]

Thanks for the file. So I am looking at your file trying to understand your requirement. My understanding is the following

1. Find minimum value in column H - in the sample file it is cell H7
2. Copy the amount in cell Q in the same row - cell Q7 (1.390 Million)
3. Place the amount in cell M of the same row - cell M7 - ensuring that total amount in column H (cell H32) doesn't exceed the value of cell F3

Is my understanding correct ? The problem is when I did these steps for the first line, nothing changes in column H so when I search again cell H7 still has the lowest value

 

[AhmedYasser]

Thanks for the file. So I am looking at your file trying to understand your requirement. My understanding is the following

1. Find minimum value in column H - in the sample file it is cell H7
2. Copy the amount in cell Q in the same row - cell Q7 (1.390 Million)
3. Place the amount in cell M of the same row - cell M7 - ensuring that total amount in column H (cell H32) doesn't exceed the value of cell F3

Is my understanding correct ? The problem is when I did these steps for the first line, nothing changes in column H so when I search again cell H7 still has the lowest value

I am sorry if I couldn't explain everything so I will try to explain the sheet in brief!
I am tried to know which contractor has the lowest percentage to pay and how far the contract is from the ceiling (D3), after getting each percentage I tried ranking them up so the person with lowest priority can get the highest amount possible from the number I enter in cell (F3)
1.1. yes
2.2.yes
3.3 correct and if I still have some amount left it should give the 2nd in place cell(H8) the rest of the amount and column N has the new percentage after adding the amount.

it's alill messy hope you are able to read through!

 

[mse330]

I can barely read your post as it’s almost 4 AM here in Kuwait. I’ll get back to you once I wake up

 

[AhmedYasser]

I can barely read your post as it’s almost 4 AM here in Kuwait. I’ll get back to you once I wake up

thanks for your time I wish you a good night! looking forward for your reply

 

[mse330]

Hi Ahmed,

Try the below macro ... If the total amount in cell M32 is already equal or greater than cell F3 the macro will not do anything so in order to test it, I suggest that you clear all amounts in column M. Also, the macro will start adding up the amounts then will stop once cell M32 reaches the amount as per cell F3. I see some negative amounts in subsequent rows & I don't know what's suppose to happen there. Anyway, give it a try with multiple scenarios & let me know how it goes

Sub AddPayments()

Dim lRow&
lRow = Range("M" & Rows.Count).End(xlUp).Row

With Sheets("Certified invoices")
    For x = 7 To lRow - 1
        If .Cells(lRow, "M") + .Cells(x, "Q") <= .Cells(3, "F") Then
            .Cells(x, "M") = .Cells(x, "Q")
        Else
            .Cells(x, "M") = .Cells(3, "F") - .Cells(lRow, "M")
            Exit For
        End If
    Next
End With

End Sub