• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA code for Minimum Function

Shailender

Shailender

Member
Hello All, Can someone help me out on this minimum function VBA Code. As given in the above example like every month I need to insert a new column like with dates 7/31/2016,8/31/2016,etc. As soon as I insert a column minimum function should take the values from A2 to new column every month, i need it dynamically. To specify clearly i inserted a new column in between F and H.

It would be appreciated if some one helps me out with the best answer.

Thank you in advance.
 

Attachments

  • Min.xlsx
    9.4 KB · Views: 3
Hi ,

You can do the same using a formula , and without using VBA.

Instead of using a formula such as :

=MIN(A2:F2)

use the following one :

=MIN(A2:OFFSET(H2,,-1))

Now , when you insert a column anywhere to the left of H , the formula will automatically take the newly inserted column into account.

Narayan
 
Hi Narayank,

Thank you for your reply. But I am working on some work with Vba code were i need to use minimum function dynamically when ever I insert a column.

Can anyone help me on this...???
 
Hi ,

I am not able to understand what the connection between VBA and this minimum function is.

Probably if you can explain what your exact requirement is , others will be in a better position to help.

Changing the formula as I have posted makes it dynamic.

Why you want code to do this same is not clear to me.

Narayan
 
Hi,
I am writing a vba code on some project to automate it, since in that sheet every month need to insert a new column exactly beside minimum column there I want to this function hope it is clear now. The code which u provided it is working in normal excel but not in Vba.
 
Hi ,

I am still not able to understand why you want the code for getting the minimum value of a row range , when the same can be achieved by using a simple formula.

You have a column where you want the minimum value of the range in that row ; for example , in one month , you want the minimum value of the range A2 : F2 in column G2.

As and whn you insert a column anywhere in the range of columns A through G , you want that the minimum function formula should change so as to take the newly inserted column into account. Thus , if a new column inserted immediately to the left of column G , the formula in column G , which was =MIN(A2:F2) will now be in column H , and it should change to =MIN(A2:G2)

Is this correct ?

If it is , what I said earlier is that instead of using the formula :

=MIN(A2:F2)

use the formula :

=MIN(A2:OFFSET(G2,,-1))

Now , if you insert a column anywhere to the left of column G , this formula will automatically change to include the newly inserted column.

Is this not what you want VBA code to do ?

If it is , then what is the problem with using this formula ?

How does doing the same thing in VBA help ?

Or are you saying that you want the minimum value to be calculated within the code , and you do not want it inserted in a worksheet cell ?

Please clarify.

Narayan
 
Hi,

I got the logic, thank you so much for your help.

I Appreciate your patience and for taking your time. Sorry for the confusion.
 
You must log in or register to reply here.
Back
Top