J
jimmy_oh
I want to be able to create input cells for a variable number of children of
a variable number of parents. I have already written the macro for the
parents. I input the number of parents I am analyzing and the macro creates
an input cell to indicate the number of children each parent has. (If I am
studying 5 parents, The first cell will say "Parent 1-# of Children, Parent
2-# of Children, etc.)
I want to write a macro that will create input cells for data about the
children. (Parent1-Child1, Parent1-Child2, Parent2-Child1, etc). I can get
the macro to reference the correct number of parents but I can only get it to
reference the # of children of Parent #1.
Sub Parent_Creation()
'
' Parent_Creation Macro
' Creates n# of Parent Inputs
'
' Keyboard Shortcut: Ctrl+p
'
Dim N As Integer
Dim i As Integer
N = Range("B27")
For i = 1 To N Step 1
ActiveCell.FormulaR1C1 = "P" & i & " # of Children?"
ActiveCell.Offset(0, 1).Select
With Selection.Interior
.ColorIndex = 44
.Pattern = xlSolid
End With
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
ActiveCell.Offset(1, -1).Select
Next i
ActiveCell.FormulaR1C1 = "Create Children?"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "Ctrl+q"
End Sub
Sub Create_Children()
'
' Children_Creation Macro
' Creates n# of Children Inputs
'
' Keyboard Shortcut: Ctrl+q
'
Dim N As Integer
Dim S As Integer
Dim i As Integer
Dim j As Integer
N = Range("B27")
S = Range("B30")
For i = 1 To N Step 1
For j = 1 To S Step 1
ActiveCell.FormulaR1C1 = "P" & i & "-" & "C" & j
ActiveCell.Offset(0, 1).Select
With Selection.Interior
.ColorIndex = 44
.Pattern = xlSolid
End With
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
ActiveCell.Offset(1, -1).Select
Next j
Next i
End Sub
Please help. Thanks
a variable number of parents. I have already written the macro for the
parents. I input the number of parents I am analyzing and the macro creates
an input cell to indicate the number of children each parent has. (If I am
studying 5 parents, The first cell will say "Parent 1-# of Children, Parent
2-# of Children, etc.)
I want to write a macro that will create input cells for data about the
children. (Parent1-Child1, Parent1-Child2, Parent2-Child1, etc). I can get
the macro to reference the correct number of parents but I can only get it to
reference the # of children of Parent #1.
Sub Parent_Creation()
'
' Parent_Creation Macro
' Creates n# of Parent Inputs
'
' Keyboard Shortcut: Ctrl+p
'
Dim N As Integer
Dim i As Integer
N = Range("B27")
For i = 1 To N Step 1
ActiveCell.FormulaR1C1 = "P" & i & " # of Children?"
ActiveCell.Offset(0, 1).Select
With Selection.Interior
.ColorIndex = 44
.Pattern = xlSolid
End With
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
ActiveCell.Offset(1, -1).Select
Next i
ActiveCell.FormulaR1C1 = "Create Children?"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "Ctrl+q"
End Sub
Sub Create_Children()
'
' Children_Creation Macro
' Creates n# of Children Inputs
'
' Keyboard Shortcut: Ctrl+q
'
Dim N As Integer
Dim S As Integer
Dim i As Integer
Dim j As Integer
N = Range("B27")
S = Range("B30")
For i = 1 To N Step 1
For j = 1 To S Step 1
ActiveCell.FormulaR1C1 = "P" & i & "-" & "C" & j
ActiveCell.Offset(0, 1).Select
With Selection.Interior
.ColorIndex = 44
.Pattern = xlSolid
End With
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
ActiveCell.Offset(1, -1).Select
Next j
Next i
End Sub
Please help. Thanks