VBA code for inserting text in all column B-cells of multiple selected rows

araber

New Member
Joined
Jul 11, 2012
Messages
6
Software: Excel 2010, Windows 7

Hi,

I'll try to save you some time and start with my question before I explain my problem - in case the problem turns out to be clear enough. As the title suggests:

What is the VBA code for inserting text in all column B-cells of multiple selected rows?

I am creating a worksheet with a table containing various data related to orders of various materials (this is more or less data gathering from an older, paper-based 'system'). This table spans, columnwise, from A to D and expands downwards as more orders are added. The information in each column is: A=order number, B=type of material and C=material specs. and D=additional comments.

I've set it up so that the only thing I really have to do is to insert the type of material in the cells of column B, and the rest will sort itself out. Instead of having to insert a new row for each new entry and manually typing in the name of the material (these entries are often done in the midst of already existing data), I created several similar, macroed buttons for the different types of materials we use. These macros work by selecting the row of the currently active cell, inserting a new row and then add the name of the material in the column B-cell of this new row. What I am having trouble doing though, is to get the text-entry to work for a selection of multiple cells.

As an example, lets say that I would like to add 5 orders of "Grade A Steel" in the middle of the table - in the row above row 8. With the macro I currently have I can select cell B5, click the macro, and a new row will be inserted with "Grade A Steel" in column B of this new row. This action could be performed 5 times over, but would be easier if I could just mark a range of 5 cells, say B8:B12, click the macro and get the text/data inserted the column B-cells of all 5 of the new rows. So far I've been able to create a macro that inserts multiple new rows, but I've only been successful in filling the column B-cell in the first row leaving the 4 below empty.

As a first time poster and non-native english speaker I apologize if I failed to make my objective clear. I will try to clarify the problem by adding my current code as soon as I get access to the worksheet again.

Thanks in advance!

-a
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

mvptomlinson

Well-known Member
Joined
Mar 10, 2008
Messages
2,638
Hi Araber, try the following code:
Code:
With Selection
    .EntireRow.Insert
    .Offset(.Rows.Count * -1, 0).Value = "Grade A Steel"
End With
If you select B8:B12 and run this code, five new rows will be inserted and cells B8:B12 will then be filled with "Grade A Steel". You can even select multiple cells, like B8, B12 and B14, and it will insert one row above each and put "Grade A Steel" in column B of the newly inserted rows.
 

araber

New Member
Joined
Jul 11, 2012
Messages
6
Thank you so much for your speedy reply. I will implement the code as soon as possible.
 

araber

New Member
Joined
Jul 11, 2012
Messages
6
It just crossed my mind: That code requires you to always select the cells in B column, right? So if you were to select C8:C12 you would get the text inserted in the C column of the newly created rows? If so, is there any way for it to get the same end result as now (always insert the text in B column of newly created row(s)) regardless of which column the initial selection is actually made in; that is, the choice between selecting A8:B12, B8:B12, C8:C12, D8:D12 would be indifferent?
 
Last edited:

mvptomlinson

Well-known Member
Joined
Mar 10, 2008
Messages
2,638

ADVERTISEMENT

Try changing
Code:
.Offset(.Rows.Count * -1, [COLOR=#ff0000]0[/COLOR]).Value = "Grade A Steel"
to
Code:
.Offset(.Rows.Count * -1, [COLOR=#ff0000](.Column - 2) * -1[/COLOR]).Value = "Grade A Steel"
 

araber

New Member
Joined
Jul 11, 2012
Messages
6
Works like a charm! Although, the mentioned function
You can even select multiple cells, like B8, B12 and B14, and it will insert one row above each and put "Grade A Steel" in column B of the newly inserted rows.
does not appear to be working.
 

mvptomlinson

Well-known Member
Joined
Mar 10, 2008
Messages
2,638

ADVERTISEMENT

It works for me if the selected ranges are the same size (e.g. all one cell, or all two cells, etc). Adjustments would be needed if you wanted to select multiple ranges of varying sizes.
 

araber

New Member
Joined
Jul 11, 2012
Messages
6
I keep getting Run-time error '1004': Insert method of Range class failed using the following code:
Code:
Sub Inserttest()
With Selection
.EntireRow.Insert
.Offset(.Rows.Count * -1, (.Column - 2) * -1).Value = "Grade A Steel"
End With
End Sub
 

mvptomlinson

Well-known Member
Joined
Mar 10, 2008
Messages
2,638
Which cells are selected when you run this? Is this code in a worksheet module, workbook module or standard module? Can you post a copy of the workbook (see my signature, remove any sensitive data first)?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,521
Messages
5,596,651
Members
414,083
Latest member
Mrsash

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