vba loop repeat numbers

[ramt0001]

KInldy support me get the loop as mentioned below using for next loop
Thank you very much for support

1
1
1
2
2
2
3
3
3

 

[hiker95]

ramt0001,

Welcome to the MrExcel forum.

What version of Excel and Windows are you using?


You have not given us a good idea of what your want to accomplish.

With your raw data in column A, beginning in cell A1, grouped/sorted per your text example.

We can sum the numbers in each group, and, place the answer in column B, next to the last number in each group?

Sample raw data:

Excel 2007
	A	B
1	1
2	1
3	1
4	2
5	2
6	2
7	3
8	3
9	3
10
Sheet1

After the macro:

Excel 2007
	A	B
1	1
2	1
3	1	3
4	2
5	2
6	2	6
7	3
8	3
9	3	9
10
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub SumGroups()
' hiker95, 02/19/2014, ME758867
Dim r As Long, lr As Long, n As Long
Application.ScreenUpdating = False
lr = Cells(Rows.Count, 1).End(xlUp).Row
For r = 1 To lr
  n = Application.CountIf(Columns(1), Cells(r, 1).Value)
  If n = 1 Then
    Cells(r, 2) = Cells(r, 1)
  ElseIf n > 1 Then
    Cells(r + n - 1, 2).Value = Evaluate("=Sum(A" & r & ":A" & r + n - 1 & ")")
  End If
  r = r + n - 1
Next r
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the SumGroups macro.

 

[ramt0001]

Many Thanks you for your reply

I may did not expalin well?
the required output needs as below
i use the loop statement but cannot get required output

Sub LoopA()
Dim i, j As Integer
j = 1
For i = 1 To 15
Cells(i, 1).Value = j
j = j + 1
Next i
End Sub


[TABLE="width: 96"]
<COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY>[TR]
[TD="class: xl63, width: 64, bgcolor: transparent"]No.[/TD]
[TD="class: xl65, width: 64, bgcolor: yellow"]Result[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]1[/TD]
[TD="class: xl64, bgcolor: transparent"]1[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]2[/TD]
[TD="class: xl64, bgcolor: transparent"]1[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]3[/TD]
[TD="class: xl64, bgcolor: transparent"]1[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]4[/TD]
[TD="class: xl63, bgcolor: transparent"]2[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]5[/TD]
[TD="class: xl63, bgcolor: transparent"]2[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]6[/TD]
[TD="class: xl63, bgcolor: transparent"]2[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]7[/TD]
[TD="class: xl64, bgcolor: transparent"]3[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]8[/TD]
[TD="class: xl64, bgcolor: transparent"]3[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]9[/TD]
[TD="class: xl64, bgcolor: transparent"]3[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]10[/TD]
[TD="class: xl63, bgcolor: transparent"]4[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]11[/TD]
[TD="class: xl63, bgcolor: transparent"]4[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]12[/TD]
[TD="class: xl63, bgcolor: transparent"]4[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]13[/TD]
[TD="class: xl64, bgcolor: transparent"]5[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]14[/TD]
[TD="class: xl64, bgcolor: transparent"]5[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]15[/TD]
[TD="class: xl64, bgcolor: transparent"]5[/TD]
[/TR]
</TBODY>[/TABLE]

 

[hiker95]

ramt0001,

Thanks for the screenshot - now I understand what your results should look like.

Sample raw data:

Excel 2007
	A	B
1	No.
2	1
3	2
4	3
5	4
6	5
7	6
8	7
9	8
10	9
11	10
12	11
13	12
14	13
15	14
16	15
17
18
19
20
21
22
23
24
25
26
27
28
Sheet1

After the new macro (not all rows are shown for brevity):

Excel 2007
	A	B
1	No.	Result
2	1	1
3	2	1
4	3	1
5	4	2
6	5	2
7	6	2
8	7	3
9	8	3
10	9	3
11	10	4
12	11	4
13	12	4
14	13	5
15	14	5
16	15	5
17		6
18		6
19		6
20		7
21		7
22		7
23		8
24		8
25		8
26		9
27		9
28		9
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Option Explicit
Sub ExpandNumbers()
' hiker95, 02/20/2014, ME758867
Dim c As Range, nr As Long, n As Long
Application.ScreenUpdating = False
Columns(2).ClearContents
With Cells(1, 2)
  .Value = "Result"
  .Font.Bold = True
End With
nr = 2: n = 0
For Each c In Range("A2", Range("A" & Rows.Count).End(xlUp))
  If n = 0 Then
    With Cells(nr, 2).Resize(3)
      .Value = c.Value
      .Font.Bold = True
    End With
    n = 1
  ElseIf n = 1 Then
    Cells(nr, 2).Resize(3).Value = c.Value
    n = 0
  End If
  nr = nr + 3
Next c
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the ExpandNumbers macro.

 

[jamcall]

Many Thanks you for your reply

I may did not expalin well?
the required output needs as below
i use the loop statement but cannot get required output

Sub LoopA()
Dim i, j As Integer
j = 1
For i = 1 To 15
Cells(i, 1).Value = j
j = j + 1
Next i
End Sub

You were almost there, just add a second loop nested in the first:

Sub LoopA()
Dim i, j, k As Long
k = 1
For j = 1 To 5
    For i = 1 To 3
        Cells(k, 1).Value = j
        k = k + 1
    Next i
Next j
End Sub

HTH,
~ Jim