Creating fields

[SKO]

Hi! I need some help with following problem. I need to recreate the precreated fields X times bellow the origal field. So if you typed number 5, the precreated field would appear 5 times bellow the original field to make a form wich you could then fill in as you wanted. I allready recorded a macro to make the original fields now I just need repeat the proses. Thanks for any help.:dunno

[Ago]

could you upload what you got now?
it will mak it a lot easier to help you

[SKO]

could you upload what you got now?
it will mak it a lot easier to help you
Yeah sure

Sub Macro10()


Range("A63:L63").Select


With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With

With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With

With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With

Range("K63:L63,A63:I63").Select
Range("I63").Activate

With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With

Range("J64:J65").Select

With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With

With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With

With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With

End Sub
Need to able to repeat this as many times as I need. Basically I just need to be able to increase the row number each time by 3. I thought that I'd use for loop for that. Sorry for a lenghty code.

[Bob Phillips]

I am struggling to relate that code to the words that you used.

What exactly do you want to do? Say it again in different words, and give an example.

[SKO]

I am struggling to relate that code to the words that you used.

What exactly do you want to do? Say it again in different words, and give an example.

The macro you see above, creates a field with lines around it. Now I need create for example 5 similar fields which are below the orinal. I don't want to use copy-paste technique to do that. Instead I thought that I'd use a loop structure. Each time the loop goes around it shoud add 3 to every row number.

[Bob Phillips]

So where will the 5 come from, be foun d at, identified at?

[SKO]

So where will the 5 come from, be foun d at, identified at?

It is just an example. It could be 3 or 8 or 10. I thought that the user could insert the number into a designated cell.

The important thing is to find out the way to add 3 to every row number after each loop.

[Bob Phillips]

Still not totally sure, so another question if I may.

If we start at say row 5, and the user designates 5 rows, is the next set to be added to row 13 or row 8? And how do we know where to stop at?

[SKO]

If we start at row 5, the next set is added to row 8. The information the user gives, only affects the the number of the loops. So if the user gives 5, the loop is repeated 5 times. If the user gives 10, the loop is repeated 10 times.

[mikerickson]

Sub FormatSomeBlocks()
Dim uiRange As Range
Dim uiBlockCount As Long, i As Long

On Error Resume Next
Set uiRange = Application.InputBox("Use the mouse to select the staring row.", Type:=8)
On Error GoTo 0
If uiRange Is Nothing Then Exit Sub: Rem cancel pressed
Set uiRange = uiRange.Cells(1,1)

uiBlockCount = Application.InputBox("How many blocks will be formatted?", Type:=1)
If uiBlockCount < 1 Then Exit Sub: Rem cancel pressed

Application.ScreenUpdating = False
For i = 0 To uiBlockCount - 1
Call FormatOneBlock(uiRange.Offset(3 * i, 0))
Next i
Application.ScreenUpdating = True
End Sub

Sub FormatOneBlock(startingRange As Range)
With startingRange.EntireRow
With .Range("a1:L1")
With .Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
'.TintAndShade = 0
.Weight = xlThin
End With
With .Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
'.TintAndShade = 0
.Weight = xlThin
End With
End With
With .Range("K1:L1,A1:I1")
With .Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
'.TintAndShade = 0
.Weight = xlThin
End With
End With
With .Range("J2:J3")
With .Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
'.TintAndShade = 0
.Weight = xlThin
End With
With .Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
' .TintAndShade = 0
.Weight = xlThin
End With
End With
End With
End Sub

[SKO]

Thank you so much! Testing of the code has to postponed to Monday however. But really thanks alot.

[mikerickson]

I commented out the .TintAndShade, since that property is not supported on my sytem, but I suspect that its not needed (unless you have delibratly set it to a different value elsewhen.) If needed, comment it back in.