I am currently using the macro below, annotated with my meager understanding of VBA after the #:
Sub InsertRows() # Title of subroutine
For a = 10000 To 2 Step -1 # Start at cell 10000, end at cell 2, move upward one cell at a time
If Cells(a, 1) = "!" Then # Beginning of if-then statement, if cell A1 has an exclamation point ...
Cells(a, 1).Select # ... it gets selected
For b = 1 To 1 # I don't understand the purpose of this for ... next operation, I feel like it is superfluous
Selection.EntireRow.Insert # This adds a new row at the current selection
Next b # ends the For b ...
End If # ends the If ...
Next a # ends the For a ...
End Sub # ends the subroutine
When I run this macro as is, it adds an empty row above every cell in column A that has an exclamation point. My understanding is that the "a" and "b" after the "For" operators are merely variables, and don't affect the actual column selected. However, when I make the following changes to attempt to target column B instead of column A (changes bolded), I get a 400 or other error:
Sub InsertRows()
For a = 10000 To 2 Step -1
If Cells(b, 1) = "!" Then
Cells(b, 1).Select
For b = 1 To 1
Selection.EntireRow.Insert
Next b
End If
Next a
End Sub
Sub InsertRows()
For a = 10000 To 2 Step -1
If Cells(b, 1) = "!" Then
Cells(b, 1).Select
< removed For b = 1 to 1 >
Selection.EntireRow.Insert
End If
Next a
End Sub
Sub InsertRows()
For b = 10000 To 2 Step -1
If Cells(b, 1) = "!" Then
Cells(b, 1).Select
For c = 1 To 1
Selection.EntireRow.Insert
Next c
End If
Next b
End Sub
I can't seem to understand why these changes don't work, and why no matter what I try I cannot target cells in column B. I understand I can move around the columns as a workaround, but I was hoping to understand more about why the VBA changes I'm attempting to make keep failing.
hanks for any help you can provide.
Sub InsertRows() # Title of subroutine
For a = 10000 To 2 Step -1 # Start at cell 10000, end at cell 2, move upward one cell at a time
If Cells(a, 1) = "!" Then # Beginning of if-then statement, if cell A1 has an exclamation point ...
Cells(a, 1).Select # ... it gets selected
For b = 1 To 1 # I don't understand the purpose of this for ... next operation, I feel like it is superfluous
Selection.EntireRow.Insert # This adds a new row at the current selection
Next b # ends the For b ...
End If # ends the If ...
Next a # ends the For a ...
End Sub # ends the subroutine
When I run this macro as is, it adds an empty row above every cell in column A that has an exclamation point. My understanding is that the "a" and "b" after the "For" operators are merely variables, and don't affect the actual column selected. However, when I make the following changes to attempt to target column B instead of column A (changes bolded), I get a 400 or other error:
Sub InsertRows()
For a = 10000 To 2 Step -1
If Cells(b, 1) = "!" Then
Cells(b, 1).Select
For b = 1 To 1
Selection.EntireRow.Insert
Next b
End If
Next a
End Sub
Sub InsertRows()
For a = 10000 To 2 Step -1
If Cells(b, 1) = "!" Then
Cells(b, 1).Select
< removed For b = 1 to 1 >
Selection.EntireRow.Insert
End If
Next a
End Sub
Sub InsertRows()
For b = 10000 To 2 Step -1
If Cells(b, 1) = "!" Then
Cells(b, 1).Select
For c = 1 To 1
Selection.EntireRow.Insert
Next c
End If
Next b
End Sub
I can't seem to understand why these changes don't work, and why no matter what I try I cannot target cells in column B. I understand I can move around the columns as a workaround, but I was hoping to understand more about why the VBA changes I'm attempting to make keep failing.
hanks for any help you can provide.