Excel 2010 :: VBA Code For Inserting Text In All Column B-cells Of Multiple Selected Rows
Jul 11, 2012
Software: Excel 2010, Windows 7
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.
View 8 Replies
ADVERTISEMENT
Mar 3, 2010
I have problems inserting new rows in selected files. The files with the problems have a macro. I don't know if the problem can lie within the macro. There is no error message, it just won't insert the new row(s).
View 4 Replies
View Related
Jun 12, 2014
My Excel program (Excel 2010) currently has several columns and each column looks for and pulls data from a specific file on my computer. Then I need to delete any duplicate data entries, count the number of unique entries and track the changes through a chart. I have everything done except I cannot figure out (or find on the internet) a way to search in multiple columns (more than 2) and delete just the duplicate cells. I want to delete the cells in a way where there is one left. For example if the code 12gf is duplicated three time, I want to be left with one 12gf (it doesnt matter what column the original one is left in). Additionally, column length changes and they are not sorted. I have attempted to attach an image of an example file below.
View 14 Replies
View Related
Nov 20, 2013
For some reason I don't seem to be able to insert any columns/rows/cells in to my excel spreadsheet. This is a problem in both basis sheets and more advanced ones.
View 1 Replies
View Related
Mar 13, 2014
I want to to copy selected columns of sales data into rows organized by salesperson. I have just started out with VBA and find that I cannot do it myself.
My original data are in the form of the following:
invoice_no
product
sales
qty
total
[Code] .....
I want to display the data in another sheet in the following format:
sales_a
sales_b
sales_c
sales_d
[Code] ...........
View 2 Replies
View Related
Sep 7, 2012
how to shift data in a column down multiple rows while in an IF statement. I am assuming you cannot just simply repeat the insert cell formula.
Here is my code - it's ugly but it was working when I just needed it to move down one cell:
lastrow = Range("A2").End(xlDown).Row
For i = 2 To lastrow
Range("B" & i).Select
If Range("B" & i).Value = Range("B" & i).Offset(-1, 1).Value Then
[Code].....
View 1 Replies
View Related
Dec 18, 2006
This may sound trivial, but how do you insert a column to the right of the current selection?
If I do Selection.EntireColumn.Insert that inserts a column to the left. Anyone know how to do this?
View 9 Replies
View Related
Dec 14, 2012
I need a code that when i place a date in a cell D10 (Example:25-January-2013) it will then add 40 days of dates daily to AP10.In D9 can it also add the weeknumber (every 7 days the weeknumber increases by 1) corresponding to the day date in D10 (iso).Can this be attached to a button.Enter the date in D10 then press the button and the dates auto insert across the sheet daily to AP10.
Can the button say ADD DATE or REMOVE DATES.First date in D10.When the button says REMOVE dates all dates deleted when button pressed and cell D10 then says "add date here".If no date is placed in D10 and ADD DATE button is pressed a warning messagebox appears and says NEED TO ADD DATE .Will not work until date entered.When date entered in D10 "Add Date Here" disappears until REMOVE Date button is selected and again "Add Date Here" is displayed....not sure if this is possible but would be good if achieved. Excel 2010
View 3 Replies
View Related
Sep 30, 2011
Whenever I insert a column in Excel 2010 it takes the formatting from the Column to the left. I want to insert a Column without formatting. Just a blank column.
View 4 Replies
View Related
Mar 27, 2014
Every day I receive a csv file of NAMES, PLACES, ADDRESSES, IDS, SPECIAL ID (ETID), ITEMS, QUANTITY, AND OTHER INANE INFORMATION. which is a list of people from places that are ordering item(s) for each ETID)
I CONVERT TO EXCEL BECAUSE I HAVE TO CLEAN THE DATA FORMATTING, AND SPLIT A COLUMN INTO 2 (LAST NAME AND ETID ARE TOGETHER).
I need to put the items into a form, one order per ETID.
I tend to receive on NAME, PLACE, ADDRESS, sending orders for multiple ETIDS.
I don't know the easiest or best way to get the info into the order form. I have designed the form in word and excel.
View 1 Replies
View Related
Feb 10, 2014
I'm using Excel 2010 and would like to know if it's possible to convert selected ranges in multiple sheets into one PDF file? For example, I want to select range("A1:O10) in Sheet1 and range("A1:N25") in Sheet2, then convert both Excel sheets into PDF file with two pages.
View 2 Replies
View Related
Mar 27, 2012
The concept is to have a user open a sheet that breaks down that current day into 15 minute blocks of time. Later, I will work to append that to an Access DB or to a master Excel sheet as I will have multiple engineers inputing times for multiple days. There are multiple paths for this....We don't have Access for everyone yet or I would look at that path now.
Right now I am trying to make the initial input screen easy to use so that they actually use it.
I want to allow the user to select multiple 15 minute blocks and then click a button that adds a specific value to each of those cells. For instance, they might choose 8 15 min blocks that are not necessarily blocked together (C5, C7:C10, C15, C20:C22) They would hit the "Project 1" button and "Project 1" would be inserted into each of those cells.
I have about (5) categories so I would simply have (5) buttons with the different inptu strings.
But how can I have a macro set the value for multiple selected cells? Ideally, they would not have to be blocked together but, if there is no other way, if I could have blocks of cells filled in all at once.
I am using Excel 2010.
only a few of us have Access but I will be the only one accessing the collected data. Besides, I would make Excel query the Access DB for more general use. I
View 2 Replies
View Related
May 2, 2014
macro for inserting rows and column in Excel.
View 1 Replies
View Related
Nov 9, 2013
I have attached link to a resource planner I have cobbled together and am stuck on the last piece of code. The "Assign Project" button needs to take the project and resource from the drop-down lists and populate the column of that resource with the project name between the dates that have been selected.
Resource Planner: [URL] ....
I'm using Excel 2010 and Windows 7
View 6 Replies
View Related
Jan 26, 2013
We have arrival dates and departure dates for guests and would like to figure out the number of meals needed on a particular date.
Named ranges: NumGuests _ArrivalDate _DepartureDate _CheckInTime _CheckOutTime
Need number of breakfasts
Number of Lunches
Number of Dinners for a given day of the week.
So if a guest is here say Jan 25 arriving after 9am, staying 2 nights they will have Lunch and Dinner on the 25th, BLD on the 26th and BL on the 27th. Since by default most arrive after 9 (miss breakfast), we can eliminate the checkin time for the breakfast calc and make the first one for the next day.
There might be other guests staying through that time period. The ******* would like to know how many meals they will need to prepare for the week.
Windows XP, Excel 2010
View 5 Replies
View Related
Jul 18, 2014
Excel 2010: I'm working with a sheet where I would like to get accurate results on the make and model of cars sold in a given week. The workbook contains several worksheets each representing weekly sales numbers for different vehicles.
The Make-Models tab has the lists used to create the drop-down selections.
The Sales tab allows one to create an ad hoc report on the total number of vehicles sold, by Make and Model. Cell B2 is named 'SelectedModel' and used in the formula found in column E, Total Sold.
Problem: When I select Honda, for example in B2 of the Sales worksheet then select Accord in cell B3 of the same. The results returned in E2, E3 and E4 are 2, 3 and 4, respectively, instead of 8, 3 and 7.
2 Honda Accords were sold on Monday and 6 on Tuesday of the same week. My formula is only returning the first matched value of 2 whereas I would like it to return 2+6, 8 for week 1. Same goes for other weeks.
Select any other make and model and notice the problem follows. I believe the error is due to incorrect usage of the formula or incorrect formula altogether.
IF(ISNA(INDEX(Week1!E:E,MATCH(SelectedModel,Week1!B:B,0))),0,
INDEX(Week1!E:E,MATCH(SelectedModel,Week1!B:B,0)))
View 9 Replies
View Related
Jul 17, 2014
I have a log that is used to keep when items are due. I do not want my coworkers to delete items once they have been entered and saved. So the excel sheet I am trying to make has certain cells in a range that I want to autolock after saving. For example: the cell range is G3:J402. I enter Customer Name in Cell G3 and the Date the job was received in H3. Once i enter that information I want it locked once i save it. Then later on, my coworker completes the job and enters the date in I2. And again once she saves this she shouldn't be able to edit this information. Then again another day she comes and enters the date on I3 when the product has been shipped out.
While all of the above activity is going on, new customers are continuously being in added in G4, G5....
Also, if possible the date in the H range should be the date items are entered in G (this would be a nice added feature). I've included the file for review. I'm running Microsoft Excel 2010
[URL]
View 1 Replies
View Related
Jul 17, 2014
I have a log that is used to keep when items are due. I do not want my coworkers to delete items once they have been entered and saved. So the excel sheet I am trying to make has certain cells in a range that I want to autolock after saving. For example: the cell range is G3:J402. I enter Customer Name in Cell G3 and the Date the job was received in H3. Once i enter that information I want it locked once i save it. Then later on, my coworker completes the job and enters the date in I2. And again once she saves this she shouldn't be able to edit this information. Then again another day she comes and enters the date on I3 when the product has been shipped out.
While all of the above activity is going on, new customers are continuously being in added in G4, G5....
Also, if possible the date in the H range should be the date items are entered in G (this would be a nice added feature). I've included the file for review. I'm running Microsoft Excel 2010.
I have tried hard to find other solutions with links at the following places but not exactly what I'm desiring:Auto lock cells after data entry when file saved...
Auto lock cells after data entered and SAVED.
Auto Lock Selected Range of Cells After Saving
I should also mention that most solutions either provide for locking/unlocking of all cells but not a selected range. Or they provide for locking/unlocking of a selected range but not for a specific sheet or not after you have saved the workbook.
This is unique in that I'd like it to autolock after i press the save button for a SPECIFIC number of cells. I just wanted to clarify as to not make others think that I haven't used the search function.
View 1 Replies
View Related
Oct 13, 2006
I am working on formatting a spreadsheet report where the values will change in column A. Here is what I would like to do via a Macro. Compare the cells in column A (e.g., compare A2 to A3, compare A3 to A4, and so on). If the values between the two cells in column A are different, insert three blank rows and set the active cell to the next cell following the blank lines. Example:
if cell A5 is different from A6, insert three blank rows below row 5 and new active cell is now A9 and the comparison would start again. I have been trying to code the macro for this but with no success. Here is the macro I have been working on.
Sub Macro1()
Const NumRow As Integer = 3
Dim StartCell As Range
Dim RowNR, NewCnt As Long
Dim RowCount As Long
Dim Count As Long
Dim intRow As Integer
Dim bFmtComplete As Boolean
RowCount = Application.WorksheetFunction.CountA _
(Range("A1", Range("A" & Rows.Count).End(xlUp)))
bFmtComplete = False
RowNR = 2
Range("A1:J1").Select
' Rows("1:1").Select
Selection.Copy................
View 4 Replies
View Related
Sep 22, 2006
I’ve got a little problem with the text to column function.
I’ve got in column A critarias and if it is for example L it should run a surtain text to column code.
With Range("A1:A65536")
. AutoFilter Field:=1, Criteria1:="L"
Range("B1").Select
Selection.End(xlDown).Select
Range(Selection, Selection.End(xlDown)).TextToColumnS Destination:=Range("B2"), DataType:=xlFixedWidth, FieldInfo:= Array(Array(0, 1), Array(21, 1), Array(60, 1))
End With
With the Auto Filter some rows between 2 until 7624 are shown. But unfortunately the text to column code is used for every row between 2 and 7624. Does anyone know how to avoid it?
View 3 Replies
View Related
Mar 5, 2013
I have a worksheet with four data columns A,B,C,D starting in row 2. I want to add between every row, exactly 11 blank rows. So if we have:
Code:
A B C D
1 5 3 4
2 7 6 3
3 9 1 3
the end result should be:
Code:
A B C D
1 5 3 4
2 7 6 3
3 9 1 3
Do I necessarily need a macro for this? Or is there another quick and creative way to achieve this result?
View 3 Replies
View Related
Aug 13, 2012
Using macro's on Excel 2010. What I'm trying to do is create a macro that will copy a range of cells from one sheet to another depending on a option being selected from a drop down box. I've tried to use formulas but without success.
sheet 1 contains a list of approx 20 people with rows containing sales figures per week.
Is it possible to have a macro that will copy the rows to sheet2 depending on the dropdown? The drop down has already been setup with people's names
E.g.
if sheet 1, cell a1 (with data validation setup) dropdown contains "mr smith", copy sheet2 row A1:A9 to sheet 1 cell a2
or
if sheet 1, cell a1 dropdown contains "mr cooper", copy sheet2 row B1:B9 to sheet1 cell a2
etc... for each name in the dropdown
The idea is so that i select a dropdown and it copies the sales figures that match the dropdown name, if i then select another dropdown, the corresponding figures are copied to the same place.
View 3 Replies
View Related
Jun 5, 2014
I am operating Excel for Mac 2011 version 14.4.2
I have a list of 1500 addresses in a column, something like, 5VistaTerrace, and I need to insert a space between the number and each of the words so that it reads 5 Vista Terrace in the cell.
All of the addresses are different, some with 4 or 5 words like 5ABrownBayCourt... that particular one I would want to read 5A Brown Bay Court in the end.
Is there a formula or excel trick I can perform to save me the time of manually adding the spaces?
View 1 Replies
View Related
Sep 25, 2008
I have a sub that will join the text together from multiple columns into one cell but it only works for one row. I would like it to work through the rows selected. How do I do this?
Sub JoinText()
myCol = Selection.Columns.Count
For i = 1 To myCol
ActiveCell = ActiveCell.Offset(0, 0) & ActiveCell.Offset(0, i)
ActiveCell.Offset(0, i) = ""
Next i
End Sub
View 9 Replies
View Related
Jan 18, 2014
Excel 2010. I need to place picture into one cell or one big merged cell, as a background fill. picture must resize to size of cell. must be fixed in, not in front. i still need write into that cell, so it needs to be really background.
View 2 Replies
View Related
Jun 17, 2014
I need to convert data from column IDS into separate rows, all other columns need to stay in tact. There are several distinct patterns for the IDS column, main identifiers are always starting with FILER or TEAL and the trailing numbers behind it have no more than 6 digits.
BEFORE MACRO
ID
AREA
TYPE
CLASS
QTY
IDS
1
COAL
TYPE9917312
CLASS881345
2
FILER756911**/**FILER123188 ^** FILER877119*118
[Code] ........
AFTER MACRO
ID
AREA
TYPE
CLASS
QTY
IDS
1
COAL
TYPE9917312
CLASS881345
2
FILER756911
[Code] ......
What the MACRO would look like? This is for Excel 2010.
View 3 Replies
View Related
Dec 1, 2013
ID Name QTY Price
1 John 5 15
2 Jim 6 20
3 Sue 10 200
4 Fred 12 125
1 Tim 7 26
4 Sue 10 100
I need macro to loop through all data and generate this table in another worksheet
1 John 5 15 Tim 7 26
2 Jim 6 20
3 Sue 10 200
4 Fred 12 125 Sue 10 100
Each data in a row in the new table will occupy a different cell.
View 3 Replies
View Related
Mar 5, 2014
I am trying to count the distinct times a value shows up in a column, if another column has a 2 in it.
For example:
Columns
A, B
2,P25
2,P25
3,P5
3,P6
2,P5
The results shoud be: (2) Because I only want to count the P25 one time.
I'm using Excel 2010
View 2 Replies
View Related
Aug 5, 2012
I am using MS Office 2010. I want to count---on multiple sheets---the number of times that a given cell is greater than another cell if and only if a third cell is equal to a given value. I want to do this for 4 sets of data on each sheet. I thought I had it figured out with this formula---
=SUMPRODUCT(COUNTIF(INDIRECT("'"&$H$1:$H$43&"'!$R1"),2*(AND("'"&$H$1:$H$43&"'!$E1">"'"&$H$1:$H$43&"'!$F1"))))+SUMPRODUCT(COUNTIF(INDIRECT("'"&$H$1:$H$43&"'!$S1"),2*(AND("'"&$H$1:$H$43&"'!$G1">"'"&$H$1:$H$43&"'!$H1"))))+SUMPRODUCT(COUNTIF(INDIRECT("'"&$H$1:$H$43&"'!$T1"),2*(AND("'"&$H$1:$H$43&"'!$I1">"'"&$H$1:$H$43&"'!$J1"))))+SUMPRODUCT(COUNTIF(INDIRECT("'"&$H$1:$H$43&"'!$U1"),2*(AND("'"&$H$1:$H$43&"'!$K1">"'"&$H$1:$H$43&"'!$L1"))))
but it returns a value of zero each time. Clearly there is an error in the formula.
Here is some background:
-- $H$1:$H$43 is a block of cells that has the names of the sheets in the workbook
-- E1 and F1, G1 and H1, I1 and J1, K1 and L1 are the four groups of cells that I am comparing.
In the entire workbook, I want to add 1 (counting function) only when:
R1=2 AND E1>F1 or
S1=2 AND G1>H1 or
T1=2 AND I1>J1
U1=2 and K1>L1
on each appropriate sheet in the workbook.
View 1 Replies
View Related
Jul 2, 2014
I have attached a test workbook excel 2010 (ignore ref# errors, I've cut the workbook down for uploading purposes) What I would like to do is have a 'Button' on my 'information Sheet' which when clicked would clear certain cells. I have searched the forum but can't find a solution, everyone seems to want to delete rows or columns but I just want to clear certain cells. The workbook will have 11 sheets each named 'caravan 1' through to 'caravan 11' The uploaded test workbook only only has 3 sheets.
On 'caravan 1' (which is slightly different to the other 10) I want to clear the content of cells
B4 & B5
C4, C22 & C41
D4 & D5
E4,E5, E22,E23,E41 &E 42
On all other 'Caravan sheets' I want to clear the contents of cells
D4 & D5
E4, E5, E22, E23, E41 & E42
It would be icing on the cake if it could give a warning such as " are you sure you want to clear these cells" but that isn't really necessary. The worksheets will be password protected, but the cells mentioned above will not be. If it proves too difficult to clear all the cells on all the sheets with one click, then perhaps a simpler solution might be to have a button on each sheet instead
View 8 Replies
View Related
