PDA

View Full Version : Need this to in VBA please the formula used is limited. Thank you



estatefinds
03-14-2016, 03:58 PM
Need this to be in VBA please!
The values in the columns GHIJK are the values smallest to greatest. the values in the columns MNOPQ are replaced with numbers that found in column W. so for example the the numbers in columns G11:K11 are
1 2 3 4 5. The number 1,on G11 , the first number in the combination is replaced with the number 1 and placed in column M11 because based on the key chart to the right in columns W11 to AB100 shows that the value from the pool of values which are the numbers used to make up the combinations GHIJK are lined up with numbers in column W.

so in GHIJK you have 1 2 3 4 5 in the columns MNOPQ you have the numbers 1 1 10 6 3 reason being the 1 in the pool of numbers is on the same row as the number 1 in column W which in column W is labeled 1, the 2 in the pool is on the same row in column W labeled 1 the 3 in the pool is on the same row as the 10 in column W the 4 in the pool is on the same row as the row labeled 6 and the 5 in the pool is on the same row labeled 3.
I need a VBA macro to do this as the pool always changes and the formula function is limited as you will see if you look on the attachment in columns MNOPQ at row 2604; there are zeros.
A VBA macro is best for this worksheet.
Thank you!

Bob Phillips
03-14-2016, 04:46 PM
That is because there is nothing in G2604, so it cannot match it. Even though not stated originally, if a blank value in the source values is possible, just tweak the formula to cater for it


=IF(G11="","",MIN(IF(G11=$X$11:$AB$46,$W$11:$W$46)))

Still array-entered.

estatefinds
03-14-2016, 05:00 PM
there was more data there that is why all the zeros showed up I had to reduce the amount of data so I could upload it. I just happend to delete the data up to that point. i did run up to 40 thousand rows of data and it didnt pick it up because of lay out in the key chart in columns W:AB of the data. I need the VBA to run what ever number combination is in the columns GHIJK the numbers will be substituted into the columns MNOPQ as in the original post.
Thank you

Bob Phillips
03-15-2016, 12:42 AM
If you don't show all of the data where it supposedly doesn't work, it is very difficult to find solutions to those instances.

estatefinds
03-15-2016, 04:48 AM
Ok you were correct the data in GHIJK ; the formula didn't extend all the way down so I found where it didn't extend continued it down and now it works! But the problem is that it takes up too much memory. Can this be done in vba as my experience tells me that a macro will be more efficient and save memory. When I add the formulas I get the message not enough avail resources. So with data and formulas entered takes up too much memory cause not only is it data meaning values it is all the formulas that's taking up space. Let me know if your able to do in a macro, please.
Thank you