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.
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

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,913
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,328
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. :)
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,302
Messages
5,836,498
Members
430,436
Latest member
fefenouil

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top