MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

VBA Excel

  Asked By: Lucina    Date: Oct 17    Category: MS Office    Views: 2174

I have


20 1 20
50 1 50
100 1 100

In column A denomination column
B no of instruments
C Eq Value

I want to write code whereby if in text box value say
120 is inserted by runing the macro it should pick 1
piece of 20 then skip 50 as it will make 70 and then
go to 100 and add it to first denomination.

I tried the same by using moveprevious in VB but it
didn't work, Is there any body who can help me out.



1 Answer Found

Answer #1    Answered By: Zoar Mizrachi     Answered On: Oct 17

Actually, I think you will need to start at the top end, with the
largest number to make this work. An approach might be:

1. Find the largest number in the range which is still lower than the
target figure (100)
2. Record the result
3. Sutract the largest number at step 1 from the target figure (120-100
= 20)
4. Repeat from step 1 using the result of step 3 as the new target

For step 1, the MAX worksheet function would provide the basis for

Didn't find what you were looking for? Find more on VBA Excel Or get search suggestion and latest updates.