PDA

View Full Version : Solved: Method to instruct how many times to do something?



YellowLabPro
09-18-2007, 11:45 AM
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?

For i = lrwSource To 3
Range(lrwSource).Insert

mdmackillop
09-18-2007, 11:54 AM
Hi Doug, try
Sub Insert3()
Dim i As Long
For i = lrwSource To 3 Step -1
Cells(i, 1).EntireRow.Resize(3).Insert
Next
End Sub

YellowLabPro
09-18-2007, 12:05 PM
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/


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



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
09-18-2007, 12:16 PM
Is this what you want?



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

mdmackillop
09-18-2007, 12:26 PM
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

YellowLabPro
09-18-2007, 05:34 PM
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.



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

Norie
09-18-2007, 08:41 PM
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
09-19-2007, 01:25 AM
Thanks Norie,
Good tip.

Bob Phillips
09-19-2007, 02:37 AM
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
09-19-2007, 03:51 AM
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
09-19-2007, 04:58 AM
I wouldn't have thought so because each row gets a different combination of extra leading/trailing slashes.

mdmackillop
09-19-2007, 10:25 AM
A bit contrived!
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