Don't understand how to modify code to target different columns/cells

[LCDayne]

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.

 

[Andrew Poulsom]

The arguments for the Cells property are Row, Column. So try Cells(a, "B"). Notice that B is in quotes because it is a string not a variable.

 

[JoeMo]

Cells(Row,Column) provides a cell address so if "a" is a variable then
Cells(a,1) is row "a" and column 1 (that's col A) and
Cells(a,2) or Cells(a,"B") is row "a" and column 2 (that's col B)

 

[RickXL]

Hi,

You should really look at some documentation for Cells. Excel has built-in help, just select the thing of interest and hit F1.
Alternatively, when searching for things to do with Excel and VBA you can start a Google search string with those words. For example:
excel vba cells

Either way, you should have found out that Cells takes a row number and a column number. SO:

Cells(3, 2) = "XXX"

will place the string "XXX" into the cell at row 3 column 2.

As for:

Sub InsertRows()
    For a = 10000 To 2 Step -1
        If Cells(a, 1) = "!" Then
            Cells(a, 1).Select
            For b = 1 To 1
                Selection.EntireRow.Insert
            Next b
        End If
    Next a
End Sub

The first value of the vasiable a is 10000. So the first execution of Cells(a, 1) means Cells(10000, 1). That is the cell at row 10000 and column A. To move to column B you need a 2 where the 1 was. That is: Cells(a, 2).

You are right that:

            For b = 1 To 1
                Selection.EntireRow.Insert
            Next b

can be replaced with:

                Selection.EntireRow.Insert

You now have the following two rows:

            Cells(a, 1).Select
            Selection.EntireRow.Insert

Usually, when you see a row ending in Select and the following row starting with Selection you can combine the lines like this:

            Cells(a, 1).EntireRow.Insert

The reason:

If Cells(b, 1) = "!" Then

does not work is that the value of b is not set prior to being used. So Excel is looking for Cell(b, 1) and it has no idea what b means. b will be set to Empty.

I hope this helps.

 

[LCDayne]

Thank you all for your clarification. When I see my oversights now, I'm shocked I wasn't able to overlook the obvious confusion in the Cells operator. You have all assisted me a great deal in my continuing education on excel VBA.