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.
[TABLE="width: 256"]
<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64, align: right"]i
940897[/TD]
[TD="width: 64, align: right"]j
940897[/TD]
[TD="width: 64"][/TD]
[TD="width: 64, align: right"]L
940897[/TD]
[/TR]
[TR]
[TD="align: right"]940902[/TD]
[TD="align: right"]940921[/TD]
[TD][/TD]
[TD="align: right"]940903[/TD]
[/TR]
[TR]
[TD="align: right"]940921[/TD]
[TD="align: right"]940921[/TD]
[TD][/TD]
[TD="align: right"]940921
[/TD]
[/TR]
</tbody>[/TABLE]

but i need something like this:

i j L
[TABLE="width: 256"]
<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64, align: right"]940897[/TD]
[TD="width: 64, align: right"]940897[/TD]
[TD="width: 64"][/TD]
[TD="width: 64, align: right"]940897[/TD]
[/TR]
[TR]
[TD="align: right"]940902[/TD]
[TD="align: right"]940921[/TD]
[TD][/TD]
[TD="align: right"]940903[/TD]
[/TR]
[TR]
[TD="align: right"]940921[/TD]
[TD="align: right"]940921[/TD]
[TD][/TD]
[TD="align: right"]940904
[/TD]
[/TR]
</tbody>[/TABLE]
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
[TABLE="class: cms_table, width: 256"]
<tbody>[TR]
[TD="width: 64, align: right"]940897[/TD]
[TD="width: 64, align: right"]940897[/TD]
[TD="width: 64"][/TD]
[TD="width: 64, align: right"]940897[/TD]
[/TR]
[TR]
[TD="align: right"]940902[/TD]
[TD="align: right"]940921[/TD]
[TD][/TD]
[TD="align: right"]940902[/TD]
[/TR]
[TR]
[TD="align: right"]940921[/TD]
[TD="align: right"]940921[/TD]
[TD][/TD]
[TD="align: right"]940903

940904

940905

940906

until 940921

[/TD]
[/TR]
</tbody>[/TABLE]


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)
[TABLE="class: cms_table_cms_table, width: 256"]
<tbody>[TR]
[TD="width: 64, align: right"]940897[/TD]
[TD="width: 64, align: right"]940897[/TD]
[TD="width: 64"][/TD]
[TD="width: 64, align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]940902[/TD]
[TD="align: right"]940921[/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]940921[/TD]
[TD="align: right"]940921[/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]


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

from(I) to(J) total(L)
[TABLE="class: cms_table_cms_table, width: 256"]
<tbody>[TR]
[TD="width: 64, align: right"]940897[/TD]
[TD="width: 64, align: right"]940897[/TD]
[TD="width: 64"][/TD]
[TD="width: 64, align: right"]940897[/TD]
[/TR]
[TR]
[TD="align: right"]940902[/TD]
[TD="align: right"]940921[/TD]
[TD][/TD]
[TD="align: right"]940902[/TD]
[/TR]
[TR]
[TD="align: right"]940921[/TD]
[TD="align: right"]940921[/TD]
[TD][/TD]
[TD="align: right"]940903

940904

940905

940906

until 940921
[/TD]
[/TR]
</tbody>[/TABLE]

 

[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!