PDA

View Full Version : [SOLVED] Make one loop with dinamic rang (Find Last Cell)



marreco
07-30-2014, 06:46 AM
HI. i have code that work, but whan i try insert dinamic range, display erro

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