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

LCDayne

New Member
Joined
Sep 17, 2014
Messages
5
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.
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

MrExcel MVP
Joined
May 26, 2009
Messages
17,226
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

MrExcel MVP
Joined
Sep 9, 2013
Messages
4,314
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:
Rich (BB code):
Cells(3, 2) = "XXX"
will place the string "XXX" into the cell at row 3 column 2.

As for:
Rich (BB code):
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:
Rich (BB code):
            For b = 1 To 1
                Selection.EntireRow.Insert
            Next b
can be replaced with:
Rich (BB code):
                Selection.EntireRow.Insert

You now have the following two rows:
Rich (BB code):
            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:
Rich (BB code):
            Cells(a, 1).EntireRow.Insert

The reason:
Rich (BB code):
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

New Member
Joined
Sep 17, 2014
Messages
5
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. :)
 

Watch MrExcel Video

Forum statistics

Threads
1,109,350
Messages
5,528,191
Members
409,807
Latest member
nicky736

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top