Simpler Conditional Row Creation

PaulAsaran

New Member
Joined
Feb 11, 2019
Messages
18
I'm currently working on a project intended to simplify the user interface for a table. This table can run in the hundreds of rows and as-is these need to be entered manually line-by-line. We want to create a system in which the table will fill most of its lines based on the information of a smaller initial table.

The smaller table has a column of ascending values and appropriate known data for those values:

FromIterationData
01003
500500
10001003

<tbody>
</tbody>

What I want is for the new table to have built-in calculations that build the values based on the above table. The key element is to have each table iterate its values based upon what is seen above:

DistanceData
0
3
1006
2009
30012
40015
500
18
55018
60018
65018

<tbody>
</tbody>

Right now I plan to use a simple VLOOKUP function to determine iterations and data. The problem I'm running into is getting the iterations into the first column. Row B needs to look at Row A and know where that value falls in the first table (i.e., 300 is between 0 and 500, so iterate by 100 and raise the data by 3; 600 is between 500 and 1000, so iterate by 50 and don't raise the data). I could use a series of IF statements to make the comparison, but the first table is going to be 20 rows long. That's a lot of IF/VLOOKUP statements and tons of room for error, plus updating it in the future would be a chore. Also bear in mind that the values in the first table will change from project to project, so the conditions in the statement need to be cell-based, not constant.

Is there a simple method to do this without a wall of IF statements?
 

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,327
There's no indication of when the data stops.
In your last line start at 1000, keep adding 100 to the distance, keep adding 3 to the Data.
Until when... ?
There's no finsihing number for the 1000 to stop at.

Or will there only be one occurrence of 1000 as the last row ?

Try this

Code:
Sub k1()
lastrow = Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
m = 1
For i = 1 To lastrow
j = Cells(i, 1)
k = Cells(i, 2)
l = Cells(i, 3)
n = n + l
For o = j To Cells(i + 1, 1) Step k
Cells(m, 5) = o
Cells(m, 6) = n
n = n + l
m = m + 1
Next o
n = n - l
Next i
End Sub
 
Last edited:

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
Try this for results starting "E1"
Code:
[COLOR="Navy"]Sub[/COLOR] MG11Feb49
[COLOR="Navy"]Dim[/COLOR] Lst [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] St [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] s [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Tot [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Dt [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
Lst = Range("A" & Rows.Count).End(xlUp).Row
Range("E1:F1").Value = Array("Distance", " Data")
c = 1
[COLOR="Navy"]For[/COLOR] n = 2 To Lst - 1
St = Cells(n, 2): Dt = Cells(n, 3)
    [COLOR="Navy"]For[/COLOR] s = Cells(n, 1) To Cells(n + 1, 1) [COLOR="Navy"]Step[/COLOR] St
        c = c + 1
        [COLOR="Navy"]If[/COLOR] c = 2 [COLOR="Navy"]Then[/COLOR]
            Cells(c, "E") = Cells(n, 1)
            Cells(c, "F") = Cells(n, 3)
        [COLOR="Navy"]Else[/COLOR]
            Cells(c, "E") = Cells(c - 1, "E") + St
            Cells(c, "F") = Cells(c - 1, "F") + Dt
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] s
[COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 

PaulAsaran

New Member
Joined
Feb 11, 2019
Messages
18
There's no indication of when the data stops.
In your last line start at 1000, keep adding 100 to the distance, keep adding 3 to the Data.
Until when... ?
There's no finsihing number for the 1000 to stop at.

Or will there only be one occurrence of 1000 as the last row ?

Try this

Rich (BB code):
Sub k1()
lastrow = Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
m = 1
For i = 1 To lastrow
j = Cells(i, 1)
k = Cells(i, 2)
l = Cells(i, 3)
n = n + l
For o = j To Cells(i + 1, 1) Step k
Cells(m, 5) = o
Cells(m, 6) = n
n = n + l
m = m + 1
Next o
n = n - l
Next i
End Sub
Excuse me while I facepalm.

I simplified the original table in hopes of keeping things from getting confusing. I should have known better. Let me offer the actual table:

FromToIntervalData
05001008
50010001005
10001500502
150020001000
200025001005
250030001000

<tbody>
</tbody>

So essentially, the final number listed in the "to" column would be the target of the end of the data. How might this affect your solution?

Bear in mind that the number of rows actually used in the first table is variable. While the table itself is 20 rows long, there may only be 7 rows used, so the location of the final number in the "to" column isn't constant from project to project.

Try this for results starting "E1"
Code:
[COLOR=Navy]Sub[/COLOR] MG11Feb49
[COLOR=Navy]Dim[/COLOR] Lst [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long,[/COLOR] St [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long,[/COLOR] s [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long,[/COLOR] n [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long,[/COLOR] c [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long,[/COLOR] Tot [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long,[/COLOR] Dt [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long[/COLOR]
Lst = Range("A" & Rows.Count).End(xlUp).Row
Range("E1:F1").Value = Array("Distance", " Data")
c = 1
[COLOR=Navy]For[/COLOR] n = 2 To Lst - 1
St = Cells(n, 2): Dt = Cells(n, 3)
    [COLOR=Navy]For[/COLOR] s = Cells(n, 1) To Cells(n + 1, 1) [COLOR=Navy]Step[/COLOR] St
        c = c + 1
        [COLOR=Navy]If[/COLOR] c = 2 [COLOR=Navy]Then[/COLOR]
            Cells(c, "E") = Cells(n, 1)
            Cells(c, "F") = Cells(n, 3)
        [COLOR=Navy]Else[/COLOR]
            Cells(c, "E") = Cells(c - 1, "E") + St
            Cells(c, "F") = Cells(c - 1, "F") + Dt
        [COLOR=Navy]End[/COLOR] If
    [COLOR=Navy]Next[/COLOR] s
[COLOR=Navy]Next[/COLOR] n
[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]
Regards Mick
Would the conditions I mentioned above change your solution in any way?



Please bear in mind that I only have the barest grasp of VBA, so I may have difficulty implementing these solutions.
 

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
Try this for Data in columns "A to D" and for Results starting "E1".

Code:
[COLOR="Navy"]Sub[/COLOR] MG12Feb33
[COLOR="Navy"]Dim[/COLOR] Lst [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] St [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] s [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] nRng [COLOR="Navy"]As[/COLOR] Range, c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Dt [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
Lst = Range("A" & Rows.Count).End(xlUp).Row
Range("E1:F1").Value = Array("Distance", " Data")
c = 1
[COLOR="Navy"]For[/COLOR] n = 2 To Lst
St = Cells(n, 3)
    [COLOR="Navy"]For[/COLOR] s = Cells(n, 1) To (Cells(n, 2) - Cells(n, 3)) [COLOR="Navy"]Step[/COLOR] St
        c = c + 1
        [COLOR="Navy"]If[/COLOR] c = 2 [COLOR="Navy"]Then[/COLOR]
            Cells(c, "E") = Cells(n, 1)
        [COLOR="Navy"]Else[/COLOR]
            Cells(c, "E") = s
        [COLOR="Navy"]End[/COLOR] If
           [COLOR="Navy"]If[/COLOR] nRng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR] [COLOR="Navy"]Set[/COLOR] nRng = Cells(c, "E") Else _
           [COLOR="Navy"]Set[/COLOR] nRng = Union(nRng, Cells(c, "E"))
    [COLOR="Navy"]Next[/COLOR] s
[COLOR="Navy"]Next[/COLOR] n


[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] Rng = Range("A2", Range("A" & Rows.Count).End(xlUp))

[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng: .Item(Dn.Value) = Dn.Offset(, 3).Value: [COLOR="Navy"]Next[/COLOR] Dn
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] nRng
            [COLOR="Navy"]If[/COLOR] Dn.Address = nRng(1).Address [COLOR="Navy"]Then[/COLOR]
                Dn.Offset(, 1).Value = [d2]
            [COLOR="Navy"]Else[/COLOR]
                Dn.Offset(, 1).Value = Dn.Offset(-1, 1).Value + Dt
            [COLOR="Navy"]End[/COLOR] If
                [COLOR="Navy"]If[/COLOR] .exists(Dn.Value) [COLOR="Navy"]Then[/COLOR] Dt = .Item(Dn.Value)
    [COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] With

[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 

PaulAsaran

New Member
Joined
Feb 11, 2019
Messages
18
Had to tweak it for cell location, but it worked. Many thanks!

Now I just have to figure out what all that means so I can replicate it in the future. Time to do some research.
 

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
You're welcome
Below is a shorter version of the code giving the same result.

Code:
[COLOR="Navy"]Sub[/COLOR] MG12Feb06
[COLOR="Navy"]Dim[/COLOR] Lst [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] St [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] s [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] nRng [COLOR="Navy"]As[/COLOR] Range, c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Num [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
Lst = Range("A" & Rows.Count).End(xlUp).Row
Range("E1:F1").Value = Array("Distance", " Data")
c = 1
[COLOR="Navy"]For[/COLOR] n = 2 To Lst
St = Cells(n, 3)
    [COLOR="Navy"]For[/COLOR] s = Cells(n, 1) To (Cells(n, 2) - Cells(n, 3)) [COLOR="Navy"]Step[/COLOR] St
        c = c + 1
        [COLOR="Navy"]If[/COLOR] c = 2 [COLOR="Navy"]Then[/COLOR]
           [COLOR="Navy"]If[/COLOR] Cells(n, 1) = s [COLOR="Navy"]Then[/COLOR] Num = Cells(n, 4)
            Cells(c, "E") = Cells(n, 1)
            Cells(c, "F") = Num
        [COLOR="Navy"]Else[/COLOR]
            Cells(c, "E") = s
            Cells(c, "F") = Cells(c - 1, "F") + Num
        [COLOR="Navy"]End[/COLOR] If
          [COLOR="Navy"]If[/COLOR] Cells(n, 1) = s [COLOR="Navy"]Then[/COLOR] Num = Cells(n, 4)
    [COLOR="Navy"]Next[/COLOR] s
[COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 

Forum statistics

Threads
1,081,969
Messages
5,362,484
Members
400,677
Latest member
champchamp

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top