Solved: Method to instruct how many times to do something?

[YellowLabPro]

I have to insert 3 cells between every value.
Is there a method for telling it how many times to repeat a line without repeating the line of code 3 times?
[VBA]
For i = lrwSource To 3
Range(lrwSource).Insert
[/VBA]

[mdmackillop]

Hi Doug, try
[vba]Sub Insert3()
Dim i As Long
For i = lrwSource To 3 Step -1
Cells(i, 1).EntireRow.Resize(3).Insert
Next
End Sub
[/vba]

[YellowLabPro]

Hello Malcolm,
Don't think I can make that work....
I need to copy the value in the active cell into the three new inserted cells.
Posting what I have so far, also I will have to add characters to the newly created color values, I will lay it out here, but dont worry about that for now.
Just trying to get it started.
Brown is the last value, create 3 more instances of Brown.
Orignial Brown is left as is then the next three to follow would be
Brown
/Brown
Brown/
/Brown/

[VBA]
Sub AddColor()
Dim wsSource As Worksheet
Dim i As Long, lrwSource As Long
Set wsSource = ActiveSheet
lrwSource = LR(wsSource, 20)

'For Each c In rng
For i = lrwSource To 3 Step -1
Range("T" & lrwSource).Insert Shift:=xlDown
Range("T" & lrwSource).Value = Range("T" & lrwSource + 1)
Next i

[/VBA]

Really don't worry about the slashes right now, I want to step through this and solve if I can. But I think your idea of resizing has some possibility... just cannot see it w/ what I have so far.

[Bob Phillips]

Is this what you want?

[vba]

Range("T3").Resize(3).Insert Shift:=xlDown
Range("T3").Resize(3).Value = Range("T3").Offset(3, 0).Value
[/vba]

[mdmackillop]

[VBA]Option Explicit

Sub Insert3()
Dim i As Long, tmp As String
For i = 15 To 3 Step -1
tmp = Cells(i, 1).Offset(-1)
Cells(i, 1).EntireRow.Resize(3).Insert
Cells(i, 1).Offset(0) = "/" & tmp
Cells(i, 1).Offset(1) = tmp & "/"
Cells(i, 1).Offset(2) = "/" & tmp & "/"
Next
End Sub
[/VBA]

[YellowLabPro]

Bob,
I cannot get yours to do what I need, it is problably lacking on my end. But I tried, I tried reversing the loop, I tried changing the range reference.... so here is what I came up with.

This takes the original value and inserts 3 cells, makes a copy of the orginal value and places the additional character, a slash in the appropriate position per instance. It also adds an index value adjacent one cell to the right. I will use this to sort on.

I think this is probably clumsy and may be able to handled better.... but I did it to show for an example to show the results I am after.


[vba]
Sub AddColor()
Dim wsSource As Worksheet
Dim rng As Range, c As Range
Dim i As Long, lrwSource As Long
Dim vl As String, chr As String

Set wsSource = ActiveSheet
lrwSource = LR(wsSource, 20)
chr = "/"
For Each c In Range("T3:T" & lrwSource).Offset(, 1)
c.Value = 1
Next c
For i = lrwSource To 3 Step -1
vl = Range("T" & lrwSource).Value
Range("T" & lrwSource).Resize(1, 2).Insert Shift:=xlShiftDown
Range("T" & lrwSource).Value = chr & vl & chr
Range("T" & lrwSource).Offset(, 1).Value = 4
Range("T" & lrwSource).Resize(1, 2).Insert Shift:=xlShiftDown
Range("T" & lrwSource).Value = vl & chr
Range("T" & lrwSource).Offset(, 1).Value = 3
Range("T" & lrwSource).Resize(1, 2).Insert Shift:=xlShiftDown
Range("T" & lrwSource).Value = chr & vl
Range("T" & lrwSource).Offset(, 1).Value = 2

lrwSource = lrwSource - 1
Next i

End Sub
[/vba]

[Norie]

Doug

I don't have the time right now to look fully at your code but I would suggest you don't use chr as a name for a variable.

Chr is a VBA function.

[YellowLabPro]

Thanks Norie,
Good tip.

[Bob Phillips]

Doug,

This code changes

/Brown
Brown/
/Brown/
Brown

to

//Brown
/Brown/
//Brown/
/Brown
/Brown/
Brown//
/Brown//
Brown/
//Brown/
/Brown//
//Brown//
/Brown/
/Brown
Brown/
/Brown/
Brown

I can't say that I understand what the rules are here. And is what you want, or is it not qute working?

[YellowLabPro]

Bob,
I gave you the final results to show that this code worked.
If you replace the values w/ the basic colors beginning in T3 and re-run; it will work fine.
Brown Green Orange Red Yellow
I was just curious if there was a way to use the .Resize in my code to save the redundancy of multiple lines of code?
Looking for ways to improve my code, only reason for asking....

[Bob Phillips]

I wouldn't have thought so because each row gets a different combination of extra leading/trailing slashes.

[mdmackillop]

A bit contrived!
[vba]Sub Insert3()
Dim i As Long, tmp As String
Dim app, rng As Range
app = Array("/x", "x/", "/x/")
For i = 15 To 3 Step -1
Set rng = Cells(i, 1)
rng.Offset(1).EntireRow.Resize(3).Insert
rng.Offset(1).Resize(3) = Application.Transpose(app)
rng.Offset(1).Resize(3).Replace "x", rng
Next
End Sub

[/vba]