Can't figure out how to change referenced cell inside a loop

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top