1

I'm looking for a solution to auto increment decimal value in a column until maximum value has reached for each column.

I have managed to make an formula for column A { =SUM(B3+0,1) }

By dragging down cell 1 to a maximum value that i require, however this is not very effective because each column has different maximum values.

As shown in diagram below:

Column A maximum required value 200
Column B maximum required value 400
Column C maximum required value 800

     |   A   |   B   |   C   | 
---------------------------------
 #1  |  1,1  |  2,1  |  3,1  | -- mimimum value
 #2  |  200  |  400  |  800  | -- maximum value
---------------------------------
 #3  |  1,2  |  2,2  |  3,2  | -- incremented value minimum A$1 + 0,1 | B$1 +0,1 | C$1 + 0,1
 #4  |  1,3  |  2,3  |  3,3  | -- incremented value minimum A$3 + 0,1 | B$3 +0,1 | C$3 + 0,1
 #5  |  1,4  |  2,4  |  3,4  | -- incremented value minimum A$4 + 0,1 | B$4 +0,1 | C$4 + 0,1
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~  split
 #13 |  2,2  |  3,2  |  4,2  | -- incremented value minimum A$12 + 0,1 | B$12 +0,1 | C$12+ 0,1

Some columns have > 600 rows count.

My question how would it be possible to populate each column with 0,1 decimal values with a VBA script or other Excel formula?

Sub AddValue()
    Dim Rng As Range, A3 As Range
    Set Rng = Range("A3:A99999")
    For Each Al In Rng
        A3.Value = A3.Value + 0,1
    Next
End Sub

And how can i stop excel increment values when it has reached a certain value?

If i could populate each column by any means that give higher than the maximum value i could filter out values for each colomn with a custom filter "Show only values between x and xxx"

Changes

Update 1: I have updated the script in my post with a for-loop. However the 0,1 is not accepting and i need to increment cells below A2 untill the Mimimum value in (A1) has reached the Maximum value (A2)

Update 2: I'm now using a formula with a logic that returns FALSE when the maximum value has been triggered. 

=IF(B2+0,1>B$1;FALSE;B2+0,1)

The question remains how do i populate rows with VBA for instance 'B3' to 'B99999' | 'C3' to 'C99999' | 'D4' to 'D99999' etc with this formula in every cell incremented. If thats solved i can trigger a filter to remove all rows which contain 'FALSE'

Improve this question
3
  • you might want to learn about the for-loop Commented Mar 7, 2015 at 16:57
  • thanks, however vba is out of my comfort zone Commented Mar 7, 2015 at 17:15
  • I have updated the script in my post with a for-loop as you suggested however the 0,1 is not accepting and i need to increment cells below A2 untill the Mimimum value in (A1) has reached the Maximum value (A2) Commented Mar 7, 2015 at 17:41

2 Answers 2

Reset to default
0

your best to use row 1 as a row to hold the maximum value for that column

     |   A   |   B   |   C   | 
---------------------------------
 #1  |  200  |  400  |  800  | 
 #2  |  1,1  |  2,1  |  3,1  |
 #3  |formula|formula|formula|

then use the following formula

=IF(A2+0,1>A$1,A2,A2+0,1)
=IF(B2+0,1>B$1,B2,B2+0,1)
=IF(C2+0,1>B$1,C2,C2+0,1)

if you cant use row 1 to store these then you will need to use these 3

=IF(A2+0,1>200,A2,A2+0,1)
=IF(B2+0,1>400,B2,B2+0,1)
=IF(C2+0,1>800,C2,C2+0,1)
Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

Your solution might be the key, however i have adjusted it to =IF(B2+0,1>B$1;FALSE;B2+0,1) -- ... when i drag this formula right down to cell B20000 it would not work properly as i want it to act because after it has reached the maximum incremented value 'FALSE' it would start parsing at beginning with value B2 again and a hunderd rows later would parse FALSE again. Etc.
Why did you change it to false? you said you wanted it to STOP incementing, you didnt say you didnt want no value, please be clear
Yes thats true, i guess i can use your formula without FALSE-statements and apply a "Remove Duplicates" filter at the end in order to remove duplicate values. With FALSE-statements in the column i thought maybe it was easier to apply a "Custom filter".... It's still terrible i need to copy a formula all the way down through 600 columns and 2000 rows down
so you want no value at all? Put the minimum row below the max row and use then use =IF(B3<>"",IF(B2+0,1>B$1,"",B2+0,1),"")
I tried out your nestled IF-statement. And required to change "IF(B2" into "IF(B3". Then i have copied the formula over the whole sheet and this actually worked for me. Thanks! <code>=IF(B2<>"";IF(B2+0,1>B$1;"";B2+0,1);"")</code>
|
0

If I correctly understand what you want, here's a VBA solution...

This sub assumes that cells A1:C1 have the starting number (eg. 1.1,2.1,3.1) and cells A2:C2 have the ending number (eg. 200,400,600). The script will increment each row item by 0.1, until the ending number for that particular column is reached. The Math.Round is to get rid of floating point rounding error.

Sub Test()
    Dim i As Long
    Dim j As Long

    For i = 1 To 3
        Cells(3, i).Value = Cells(1, i).Value + 0.1

        j = 3

        Do
            Cells(j + 1, i).Value = Math.Round((Cells(j, i).Value + 0.1), 1)
            j = j + 1
        Loop While Cells(j, i).Value < Cells(2, i).Value
    Next
End Sub
Improve this answer

4 Comments

I will certainly look into it. Is this script ready to go? I tried it out and an VBA Error type mismatch was displayed in line #6 - Cells(3, i).Value = Cells(1, i).Value + 0.1
Works for me. Do you have numbers in cells A1:C2?
I have checked your script again and the script works kinda. It increments forever :-D not really what i was looking for. Anyway never mind i have found a final solution with a formula. Thanks for checking on this thread
Glad you found a solution.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.