Excel Code Help, Loop related

[tzexu1610]

So... I've got 3 columns in my excel spreadsheet, I,J and L. Looks something like this:

I J L
1 1 1
3 3 3
4 7 4
5
6
7

The column L is supposedly empty but I wanna make those numbers appear there.L would be based on I and J. The below is my code. Some help would be much appreciated .

Sub trial2()


Dim cell As Range
Dim i As Integer
Dim j As Integer
Dim c As Range




i = 0
j = 0


For Each c In Columns("L")
i = i + 1
j = j + 1
c = i

If Cells(i, "I").Value = Cells(i, "J").Value Then
Cells(j, "L").Value = Cells(i, "J").Value
ElseIf Cells(i, "I").Value < Cells(i, "J").Value Then
Cells(j, "L").Value = Cells(i, "I").Value + 1



End If


Next c


End Sub

 

[Michael M]

MAybe this

Sub trial2()
Dim cell As Range, i As Integer, j As Integer
Dim lr As Long
lr = Cells(Rows.Count, "I").End(xlUp).Row
i = 0
j = 0
For Each cell In Range("I1:I" & lr)
    i = i + 1
    j = j + 1
    If Cells(i, "I").Value = Cells(i, "J").Value Then
        Cells(j, "L").Value = Cells(i, "J").Value
        ElseIf Cells(i, "I").Value < Cells(i, "J").Value Then
        Cells(j, "L").Value = Cells(i, "I").Value + 1
    End If
Next cell
End Sub

 

[tzexu1610]

Hi thanks a lot for replying Michael. Not exactly what I need though. This is what comes out.

i 940897	j 940897		L 940897
940902	940921		940903
940921	940921		940921

<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>
</tbody>

but i need something like this:

i j L

940897	940897		940897
940902	940921		940903
940921	940921		940904

<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>
</tbody>

940905
940906
all the way until 940921

so basically i need a list of the numbers. sorta in between i and j .

 

[tzexu1610]

oops the 940905, 940906...... 940921 are supposed to be under column l . the formatting is really bad sorry im like a first timer

 

[Michael M]

How do you get 940904 in the last row of the example ???
Your condition is, if I = J then use J !
In the last line of your example you are using a number that has no relevance to either I or J ???

 

[tzexu1610]

How do you get 940904 in the last row of the example ???
Your condition is, if I = J then use J !
In the last line of your example you are using a number that has no relevance to either I or J ???

ah sorry i made a mistake, didnt see that.



but i need something like this:

i j L

940897	940897		940897
940902	940921		940902
940921	940921		940903 940904 940905 940906 until 940921

<tbody>
</tbody>

the heading for i and j are sorta like sample start and sample end. while L heading is like total samples. Hope that helps you understand. Sorry for the inconvenience.

 

[Michael M]

Hope that helps you understand

Sorry, the data you are posting makes no logical sense to me

Maybe instead of posting "sorta" data, you post real time data we can work with..

 

[tzexu1610]

Sorry, the data you are posting makes no logical sense to me

Maybe instead of posting "sorta" data, you post real time data we can work with..

Ah sorry that actually is real time data. i just changed the headers hoping it'll help u understand better but i'll post original as u requested.



from(I) to(J) total(L)

940897	940897
940902	940921
940921	940921

<tbody>
</tbody>

so above is original. below is what i need to generate using macro.

from(I) to(J) total(L)

940897	940897		940897
940902	940921		940902
940921	940921		940903 940904 940905 940906 until 940921

<tbody>
</tbody>

 

[Michael M]

Maybe this, but it assumes there will be data in cols I & J

Sub trial2()
Dim cell As Range, i As Integer, j As Integer
Dim lr As Long
lr = Cells(Rows.Count, "I").End(xlUp).Row
i = 1
j = 1
For Each cell In Range("I2:I" & lr)
    i = i + 1
    j = j + 1
    If Cells(i, "I").Value < Cells(i, "J").Value Then
        Cells(j, "L").Value = Cells(i, "I").Value + 1
        ElseIf Cells(i, "I").Value = Cells(i, "J").Value Then
        Cells(j, "L").Value = Cells(i - 1, "L").Value + 1
    End If
Next cell
End Sub

 

[tzexu1610]

Maybe this, but it assumes there will be data in cols I & J

Sub trial2()
Dim cell As Range, i As Integer, j As Integer
Dim lr As Long
lr = Cells(Rows.Count, "I").End(xlUp).Row
i = 1
j = 1
For Each cell In Range("I2:I" & lr)
    i = i + 1
    j = j + 1
    If Cells(i, "I").Value < Cells(i, "J").Value Then
        Cells(j, "L").Value = Cells(i, "I").Value + 1
        ElseIf Cells(i, "I").Value = Cells(i, "J").Value Then
        Cells(j, "L").Value = Cells(i - 1, "L").Value + 1
    End If
Next cell
End Sub

Solved! Thanks for your help[ Michael!