View Full Version : [SOLVED:] Make one loop with dynamic range (Find Last Cell)
marreco
07-30-2014, 06:46 AM
HI. i have code that work, but when i try insert dynamic range, display error
i try make only one loop, not 4 loop too.
Sub PosicionarRepositores()
Dim n As Integer
For n = 2 To 433 Step 4
ActiveSheet.Cells(n, 4) = "Cleiton"
Next n
For n = 3 To 433 Step 4
ActiveSheet.Cells(n, 4) = "Julierica"
Next n
For n = 4 To 433 Step 4
ActiveSheet.Cells(n, 4) = "Sheila"
Next n
For n = 5 To 433 Step 4
ActiveSheet.Cells(n, 4) = "M.helena"
Next n
End Sub
JKwan
07-30-2014, 07:04 AM
try this:
Sub PosicionarRepositores()
Dim n As Long
Dim StartLoop As Long
Dim Names As Variant
Names = Array("Cleiton", "Julierica", "Sheila", "M.helena")
For StartLoop = 2 To 5
For n = StartLoop To 433 Step 4
ActiveSheet.Cells(n, 4) = Names(StartLoop - 2)
Next n
Next StartLoop
End Sub
marreco
07-30-2014, 07:26 AM
is possible find last cell in column
For n = StartLoop To 433 Step 4
Using something...?
lastcell = Range("A" & Cells.Rows.Count).End(xlUp).Row
Thank you!!
westconn1
07-31-2014, 05:41 AM
yes,
cells(1, columns.count).end(xltoleft).column
marreco
07-31-2014, 07:13 AM
Hi
i change, but result is wrong
Sub WorngResul()
Dim n As Long
Dim StartLoop As Long
Dim Names As Variant
Names = Array("Cleiton", "Julierica", "Sheila", "M.helena")
For StartLoop = 2 To 5
For n = StartLoop To Cells(1, Columns.Count).End(xlToLeft).Column Step 4
ActiveSheet.Cells(n, 4) = Names(StartLoop - 2)
Next n
Next StartLoop
End Sub
Wrong
Head1
Head2
Head3
Head4
data1
data1
data1
Cleiton
data2
data2
data2
Julierica
data3
data3
data3
Sheila
data4
data4
data4
data5
data5
data5
data6
data6
data6
data7
data7
data7
data8
data8
data8
data9
data9
data9
data10
data10
data10
data11
data11
data11
data12
data12
data12
data13
data13
data13
data14
data14
data14
data15
data15
data15
Correct
Head1
Head2
Head3
Head4
data1
data1
data1
Cleiton
data2
data2
data2
Julierica
data3
data3
data3
Sheila
data4
data4
data4
M.helena
data5
data5
data5
Cleiton
data6
data6
data6
Julierica
data7
data7
data7
Sheila
data8
data8
data8
M.helena
data9
data9
data9
Cleiton
data10
data10
data10
Julierica
data11
data11
data11
Sheila
data12
data12
data12
M.helena
data13
data13
data13
Cleiton
data14
data14
data14
Julierica
data15
data15
data15
Sheila
data15
data15
data15
M.helena
JKwan
07-31-2014, 08:24 AM
try this
Sub CorrectResults()
Dim n As Long
Dim StartLoop As Long
Dim Names As Variant
Dim LastColumn As Long
Dim LastRow As Long
Names = Array("Cleiton", "Julierica", "Sheila", "M.helena")
LastColumn = Cells(1, Columns.Count).End(xlToLeft).Column
LastRow = Range("A" & Rows.Count).End(xlUp).Row
For StartLoop = 2 To 5
For n = StartLoop To LastRow Step 4
ActiveSheet.Cells(n, LastColumn) = Names(StartLoop - 2)
Next n
Next StartLoop
End Sub
marreco
07-31-2014, 08:56 AM
Hi. JKwan (http://www.vbaexpress.com/forum/member.php?526-JKwan)
Very good, thank you very much!!
marreco
08-01-2014, 09:50 AM
Hi i have more one question.
I need start loop in 434 row and finish 486 row.
Exemple:
Rows
434 = Cleiton
435 = M.helena
436 = Cleiton
434 = M.helena
.......
485 = Cleiton
486 = M.helena
Sub Startloop_SpecifRow() Dim n As Long
Dim StartLoop As Long
Dim Names2 As Variant
Names2 = Array("Cleiton", "M.helena")
'Insert two names stating 434 row to 486 row
For StartLoop = 434 To 486
For n = StartLoop To 486 Step 2
ActiveSheet.Cells(n, 4) = Names2(StartLoop)
Next n
Next StartLoop
End Sub
JKwan
08-01-2014, 10:26 AM
Not too sure what you mean, but try this:
Sub Startloop_SpecifRow()
Dim n As Long
Dim StartLoop As Long
Dim Names2 As Variant
Names2 = Array("Cleiton", "M.helena")
'Insert two names stating 434 row to 486 row
For StartLoop = 434 To 486
For n = StartLoop To 486 Step 2
ActiveSheet.Cells(n, 4) = Names2(StartLoop Mod 2)
Next n
Next StartLoop
End Sub
marreco
08-01-2014, 10:47 AM
Hi JKwan (http://www.vbaexpress.com/forum/member.php?526-JKwan) , very good, work very well!!
Thank you very much!!
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.