Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Advertisements:










Adding Rows With VBA


We have workook with several worksheets (tabs). The main page has several headings (category), including "Doors and Ceilings", "Walls" etc. There are currently three rows under each heading. When I go to a category (tab) there is a list of issues in column b. I can go to column A of each issue and if I place an "a" that issue will be copied to the main page under that category. The problem is that i only have 3 rows and I need a row added each time an issue is transferred to the cell before it.

Is there some VBA I can use to detect the precence of data anbd add a row?


View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Adding 2 Rows After Every One Row
I need some help fomulating a VSB code which would add two rows after every one row.

View Replies!   View Related
Adding Conditional Rows
I want to be able to have rows/cells generated with a certain text
based on the answer given by a user.

For example, My question in a cell could be "how many groups have you made?".
Now to this if the user answers "3" then I would want three cells below to
ask "what is the name of your first group", "what is the name of your second
group" and so on.

View Replies!   View Related
Adding Two Rows After Every Existing Row
Is there any way to automatically add two new rows after every existing row containing data?

For example

1 A B C
2 D E F
3 G H I

turn into

1 A B C
(empty row)
(empty row)
2 D E F
(empty row)
(empty row)
3 G H I

View Replies!   View Related
Updating By Adding New Rows
i have rows of data in an accounting worksheet (payments in/out) which needs constant updating by adding new rows. within the row a formula exists in column D such as =SUM(E11:J11) and the next row is =SUM(E12:J12). if i select row 12, right click and select insert, a new blank row 12 appears but without formula in column D. is it possible to insert row with retained formula? i would prefer to add a row from a blank row below current row and have new row appear with above rows formula but this is not vital.

View Replies!   View Related
Adding Two Rows Together If They Have The Same Value In Column A
I've got an excel sheet which has names in column A followed by 5 numerical values in columns B-F. I'd like to get a macro that will compare the values in Column A and if they match, add the B values, the C values, the D values, the E values, and the F values and delete one row - effectively combining the row. What's the best way to go about this? Also the sheet is already in ascending alphabetical order by Column A, so the rows that need adding will be next to each other. So I guess compare each A value with the one below it and if they match add the rows, but how?

View Replies!   View Related
Adding Comments To Many Rows In Vba
Is any way to add automatically comments in VBA

Some combination of "ctrl" with something else.

View Replies!   View Related
Adding Rows And Calculation To Macro
I am extending rows on a salary schedule. As you can see below, the rows include: Bargaining Unit (GP) Schedule(2A) Range(5) Step (A) Hourly (S) and the hourly wage(11.52)

GP,2A,5,A,H,11.52
GP,2A,5,B,H,11.82
GP,2A,5,C,H,12.17
GP,2A,5,D,H,12.5
GP,2A,5,E,H,12.88
GP,2A,5,F,H,13.22
GP,2A,5,G,H,13.61
GP,2A,5,J,H,14.18
GP,2A,5,K,H,14.71
GP,2A,5,L,H,15.26
GP,2A,5,M,H,15.83

The Steps currently go from A thru M but I need to add N thru Z and after step M there is a 3.75% increase for each step.

You can see that this is a csv file (comma delimited). All of the other info needs to autofill and only the new steps (N-Z) will change and the wage for each of the new steps. The last row is 2 decimal points.

View Replies!   View Related
Adding A Variable Rows Worksheet
Due DateInvDocument numberDebitCreditBalance
17/09/08L01607610714211201825.44
13/10/08L01637010284345841020
13/10/08L01637131713874501020
13/10/08L016373852202473461020
13/10/08L0163748.5222E+111020
15/10/08L01641811103746832316

The above is a part of a w/sheet. Rather than determining the last row in the debit column and credit column (to know which is greater) and use a loop to put a formula in each of the cells in the balance column (adding the debit and credit), I know I can use the with ... statement to input the formula at a go , but cannot figure it out.

Sub sumtotal()
With Sheets(1)
With .Range("G14", Range("G" & Rows.Count).End(xlUp))
.Clear
.Formula = "=sum(E14+F14)"
On Error Resume Next

End With
End With
End Sub

View Replies!   View Related
Adding Up Differnt Rows Within A Table?
My table is a much larger version of this:

A B
X1283.786
Y1684.379
Z1719.013
X10745.013
Y10860.668

i want excel to work out the total for x y and z. however i have about 100 different things x y and z could be and its a long list so an automatic way would be great.

View Replies!   View Related
Adding Multiple Rows With A Macro
Ive made a simple macro in order to make easier to add new rows in a table, by filling the cells with the new data.

The problem is that according to one of the fields, Id like that macro to add multiple rows, with exactly the same information but numbering all these rows on this specific field from 1to the number I filled on the respective cell.

For example, I have beyond another fields that Ive already filled, the field "Parcel" and "Total of Parcels". I filled parcel with number 1 and total of parcels with 5. Then Id like the macro to add 5 rows with the "total of parcels" cells containing 5 on every row and the field parcel filled with number 1, 2, 3, 4 and 5, for each row created.

View Replies!   View Related
Form Development - Adding Rows
I'm creating a spreadsheet to generate a standardized form that I use as a paralegal.

I have 2 things that I don't know how to create....

1 - I want to have y/n questions on the data entry tab. When boxes are clicked Y, it adds a row and language to the form which exists on another tab. I know how to do links and to do if then statements, but I don't know how to make it not take up space when the answer is n. Some of these clauses are pretty long so I don't want to have big blank spaces when the answer is no.

2 - I have certain sections where I need to control the number of rows. I want to have a cell on the data entry tab where I put in the number of rows that I want. The spread sheet would create those rows and I would then enter the data on the data entry tab. On the form tab, the spreadsheet would refer to the cell telling it the number of rows, add those rows, and link them to the data that I entered on the data entry tab.

View Replies!   View Related
Adding A Blank Row In Between Excisting Rows
This is what I have:

A-------------B
gfdfd------sjfsdnjsnd
gfdfd------sjfsdnjsnd

This is what I need:

A-------------B
gfdfd------sjfsdnjsnd
....

I just need a black row between every row that I have now. I'm sure its simple but I can't seem to figure it out. I have tried a macro but don't really know how to work them, it just made a new line in the same spot.

View Replies!   View Related
Extending Sum Function When Adding Rows
If I have a sum function that adds up:

= SUM(U41:U45)

but I add a row at 41 I now get sum function

=SUM(S42:S46) when I really want it to incorporate the just add row to look like this:

=SUM(U41:U46)

View Replies!   View Related
Automatic Adding And Subtraction Entire Rows
What I would like to do is do a summary sheet that does a little math. I can make it add but only one row at a time. Here is the general idea...

The fields of the summary sheet are 'PromoCode', 'AdvertisingCosts', 'Labor' for example. And I want to add the currency fields (add, as in math addition) of 'AdvertisingCosts' and 'Labor'.

The fields are on another sheet. What I am hoping can be done is that all those fields for every row can be made to work without having to hard code each one.

Also I would like to have it update with any NEW rows that arise. Like if the existing rows were 'row1' and 'row2' and then some other time 'row3' is added that 'row3' automatically gets included on the summary sheet.

View Replies!   View Related
Adding Numbers To Rows Using A Command Button ()
I have a user form with combo boxes. The user selects items from the combo box and when they click the command button the selection is written to the spreadsheet. Each time the button is clicked it adds an item to the sheet making a list of items. I am trying to assign a number to each item when the command button is clicked so I have numbered list. Here is my code so far:

Private Sub cmdbtnAdd_Click()

Dim RowCount As String

'Writes user inputs to Active Worksheet when Add Item Button is clicked.

RowCount = Worksheets("Sheet1").Range

("A1").CurrentRegion.Rows.Count

With Worksheets("Sheet1").Range("A1")

.Offset(RowCount, 0).Value = ' This is where I have been trying to code in the numbering

.Offset(RowCount, 1).Value = Me.Combobox1

.Offset(RowCount, 2).Value = Me.ComboBox2

End With

End Sub

View Replies!   View Related
Adding Unique Identifier To Rows Of Data
I am trying to use a formula that will populate A3 with the value of A2, all the way down the column. (as per the area highlighted in yellow) As you can see on the example sheet, the account code changes periodically, this has been causing me many problems!

As the list i am working on has around 100 differfent codes, it is too time consuming to copy paste manually

Reason for this is so that each invoice number in column B, will have a unique identifer in column A (account code)

View Replies!   View Related
Process Of Adding More Rows Has Slowed Down Considerably
I am building an inventory list for an online store with Excel 2002. Everything was fine until I reached 10,000 rows and 50 columns.

I use formulas to convert wholesale price to retail price on about ten columns.

Overnight the process of adding more rows has slowed down considerably.

I had my computer checked out and they say it is working just fine.

View Replies!   View Related
Adding Multiple Rows Of Text In A Msgbox.!
I believe this would be a very simple query for some but I am a complete novice in VBa and am using a msgbox in one of my file but I need to split the message text into multiple lines but I do not know how to do the same.

View Replies!   View Related
Run Time Error 1004 When Adding Rows
Run time error 1004 when adding rows. I have the following code, called from a Userform:

View Replies!   View Related
Restrict The Adding The Rows And Columns And Also Deletion
I want to restrict the adding the rows and columns and also deletion of rows and columns in excel template, the same template is circulated to all the program owners to capture there project metrics. With that template through macro values are extracting to consolidated report. Some times program owners are adding extra columns/rows with that we are getting errors/incorrect reports.

View Replies!   View Related
Conditional Formating - Adding & Deleting Rows
I am using several formulas to conditionally format and fill in rows from column A to column BS. These are the formulas and application areas:

=$BS4<>"" Applied to =$A$3:BS$350
=$G3<>"" Applied to =$A$3:BS$350
=OR($J3="Regional Manager",$J3="Assistant Manager",$J3="Manager") Applied to =$A$3:BS$350
=OR($J3="Recruiting Manager",$J3="Owner",$J3="District Manager",$J3="Office Manager") Applied to =$A$3:BS$350

If I do not add or delete any rows, everything works just fine. The problem I am running into is that this list is constantly updated and rows are being added and deleted. When I do this, it duplicates my rules and changes my applied to ranges. Here are some examples of the changes:

=$N$3:$BS$3
=$A$3:$M$63
=$N$4:$BS$64

This creates much excess work, when I have to go back and change everything back to the way it should be. how I can either modify my formulas or application areas to correct this situation?

View Replies!   View Related
Deleting And Adding Rows Based On Date Criteria
ColumnA in my data base contains dates. How can I delete the entire row if the date is before today's date and add a new date at the end of the range to replace the deleted row.


View Replies!   View Related
Adding A Constant To Sets Of Rows Of Variable Size
I have data from 100 trials. Each trial has a variable number of data points (rows), but each row is labeled in the first column with the trial number. I would like a macro to identify the first row of each trial, calculate the difference of the value of the cell in column G of that row from a constant (718), and then add that difference to that cell and all other cells in that column of that trial.

So based on the attached sample, I would like all values in column G for trial 2 to be +1, for trial 3 to be -5, and for trial 4 to be +2.

In the file, I've cleared all the data from the impertinent columns. Eventually, I will also want to perform a similar process on column H but with a different constant.

View Replies!   View Related
Adding Up Rows Of Text/numbers That Are Exact Matchs
Need code for adding up rows of cells of text mixed with numbers, each row would look like

b1230 10 7/8 Gables 3/4 Pref Birch
b2230 10 7/8 Gables 3/4 Pref Birch
b3230 10 7/8 Gables 3/4 Pref Birch
b4230 10 7/8 Gables 3/4 Pref Birch
b1138 1/2 10 7/8 Base 3/4 Pref Birch
b2138 1/2 10 7/8 Base 3/4 Pref Birch
b3138 1/2 10 7/8 Base 3/4 Pref Birch
b4128 1/2 10 7/8 Base 3/4 Pref Birch

and i would like to make them add up to look like this

b1, b2, b3, b4 8 30 10 7/8 Gables 3/4 Pref Birch
b1, b2, b3 3 38 1/2 10 7/8 Base 3/4 Pref Birch
b4 1 38 1/2 10 7/8 Base 3/4 Pref Birch

View Replies!   View Related
Macro Code Hangs/Freezes Adding Rows
I've managed to use some code I found to add a new row below the selected row, and duplicate all the forumlas of the source row. It worked fine dozens of times yesterday, but today it's decided not to work. It gets as far as creating the new row(s), but then just hangs & excel crashes before duplicating the formulas. I've even tried reverting to an earlier version, which also worked fine, but this crashes also!

Sub Add_New_Row()
' Unlock Worksheet
Worksheets("Sheet1").Unprotect Password:="*****"
Dim x As Long
ActiveCell.EntireRow.Select 'So you do not have to preselect entire row
If vRows = 0 Then
vRows = Application.InputBox(prompt:= _
"How many rows do you want to add?", Title:="Add Rows", _
Default:=1, Type:=1) 'Default for 1 row, type 1 is number
If vRows = False Then Exit Sub
End If
'if you just want to add cells and not entire rows
'then delete ".EntireRow" in the following line
Dim sht As Worksheet, shts() As String, i As Integer
Redim shts(1 To Worksheets.Application. ActiveWorkbook. _
Windows(1).SelectedSheets.Count)..................................

View Replies!   View Related
Fixed Range Name When Adding Rows/Columns/Cells
How do I define a " Range Name" that is fixed? So if I insert a row above the range it has no effect on the orginal range.

IE: Range Name "MyRange" = A10:A100

I insert a new row at A4 then "MyRange" = A11:A101

I want it to stay at A10:A100.

View Replies!   View Related
Adding Rows With Control Number That Only Repeat 3 Times: Formula
I'll try to explain this the best I can. What I have here is a time study. Teachers (which are the control numbers) fill out bubble sheets, then I run them through a scanner. After dissecting the data from the scanner and formatting it to my liking i get this below.

Each teacher/staff member fills out three sheets per quarter. Each letter (bubble) counts as a 0:15 min period of time. Only K thru Q counts as billable time, which I've created a formula to count those letters (column 3). But to be countable each control number has to have three cycles 201,202,203.

So I need something that can take each control number that has three cycles and add their # of 0:15 together.

The italic row below only has one cycle for that control number, so that needs to be deleted or ignored.

Ultimately I would like the results on a separate sheet.

This is only a part of the file, there are over 1000 different control numbers.

View Replies!   View Related
Lock Cell From Moving When Adding/Deleting Rows Or Columns
Will excel allow text to be permanently be positioned in a specific cell even if rows or columns are added?

View Replies!   View Related
Prevent Custom Function Running When Inserting/Adding Rows
I have a piece of code that hides unneeded rows, it does work but it is very slow.
This is in the worksheet part of the project. My problem is as it runs it "jumps" in to a function I have in a module that counts continuous rows. I would like to know why it is doing this and what I need to do to stop it? I have another piece of code that is structered exactly the same that hides unneeded columns and I do not have any problems with that. I know both pieces of code are dealing with rows and I think this is part of the problem but I can not see any reason why the first bit would call the second bit?

Dim C
With Worksheets("CEN OAS"). Range("D5:D378")
.EntireRow.Hidden = False
End With
For Each C In Worksheets("CEN OAS").Range("D5:D378")
If C.Value = "" Then
C.EntireRow.Hidden = True
End If
Next C

Function to count used rows....................

View Replies!   View Related
Conditional Formatting: Reference Changes Adding/Deleting Rows/Columns Or Cells
I'm using Conditional Formatting for an entire column to check the value of each cell and compare it to the value of a cell in the same row but a different column. (Cell Value equals =$D2) It works great until I insert or delete a cell in the formatted column. The reference does not change as one would expect. I've played around with formulae such as =$D2<>$P2 but the reference only changes for the P2, not the D2. I've also tried using =CELL("contents", ADDRESS(Row(),4)) but this causes excel to complain.

View Replies!   View Related
Adding Time: Adding Individual Cells Works, But SUM Doesn't
I have a column of times: e.g. 10:03:00 and I would like to add them all up.

=A1+A2 works fine.

=sum(A1:A10) does not.

View Replies!   View Related
Adding Dim'd Variables: Ignoring Strings & Adding Number
I've got a problem involving several Dim'd Variables needing to be added up, they're all Dim'd as Variants though as they can be either strings or numbers at any time.

I need a formula (VBA) to add them up (to add their actual numeric values - not just a "1" if they contain a number) and ignore them all together if they contain string values.

View Replies!   View Related
Adding Data With Userform: Check For Duplicate Before Adding
I have a userform that I'm using to add data to a worksheet, with the following

Private Sub CommandButton1_Click()
Dim OutSH As Worksheet
Set OutSH = Sheets("Sheet1")

OutSH.Cells(nextrow2, 1).Value = Surname.Value
OutSH.cells(nextrow2,2).value = ID.value
OutSH.cells(nextrow2,3).value = Date.value
......
I need to ensure that duplicate entries are not made for the same person on the same date. The ID is unique to each person.

IF statement that can check for a duplicate and then come up with a dialouge box with some custom text, and then exiting the sub?

View Replies!   View Related
Adding Column Minus Rows That Have Data In Another Column
I want a cell on one spreadsheet (SP-A) to add up a column (M) in another spreadsheet (SP-B) rows 11 through 10000.

Here's the thing, I want that cell to actually display not the sum that it gets but that sum minus all numbers (in M column again) which column E is filled out with any data for their given row...

i'm not even sure if this makes sense lol... let me use example

on SP-B there's a column M.
in row 11, value = 3
in row 12, value = 5, in this same row column E is filled with whatever
in row 13, value = 2, in this same row column E is filled with whatever
in row 14, value = 6

in my SP-A I need the cell to display 9, since rows 12 and 13 have values in E and I don't want to add those to the sum.

View Replies!   View Related
Adding Values In Cells (stops Adding After Row 14)
I am trying to add numbers from cells if it IsNumeric and for some reason in column K the macro doesnt recognize numbers after row 14?

The range column is "E4:E"
Search criteria is the letter "R" in column "E" Then using OffSet, I go thru other columns and process data. Most of the macro works except for column K after row 14?

View Replies!   View Related
Adding A 1 At The End
I have a 9 digit number in excel. I'd like to write a formula that adds the number 1 to the end of each number.


View Replies!   View Related
Adding Every Other Row
I want to add Columns B through R, starting on row 4, add row 4, then 6, then 8, then 10 and so on, till about 30. Does anyone know the formula?

View Replies!   View Related
Adding A New Row Every Other Row
I have imported a spreadsheet from our district's grade-keeping site. I want to insert a new row between every student and would like to find a way to do it without having to do it manually for each of every 700+ students. This row can be blank, but if there is a way to enter a copied row of information, that would be better.

I tried to write a Macro, but it only adds the new row at the same place each time.

View Replies!   View Related
Adding Value In The Last Row ...
How to add values in the last row under the latest data that already existed ... is a loop required in the problem?

View Replies!   View Related
Adding A Day
I have excel 2007. I am trying to do the EASIEST thing in the world and it just not working for me. Basically all I want to do is add days to an inputted date in a cell.

Works great if there's a date already in the referenced cell (A1), however, if I input a new date it doesn't re-calculate.

I have tried:

=B1+34

and =DATE(YEAR(B1),MONTH(B1),DAY(B1)+34)

Not recalculating when in enter in a different date into B1, why? How do I fix this so it will work correctly?

btw....basically I want to have a whole column (B) of the date formula so when I enter a date into cells in column A, it calculates.

View Replies!   View Related
Adding A New Row Automatically
I have a work book of 3 sheets . the date in the first sheet are linked with the other 2 sheets , sometimes I have to add a row in the first sheet , within the the the table it contains ,

what I need is : a new raw is being inserted (created) in the other 2 sheets with tha same date I entered in the new row I added in the first sheet . beside the possibility to let me difine where that new row is being inserted in each of those other 2 sheets ( separately).

View Replies!   View Related
Adding Sheet Name
I am looking to Cut an entire row from one sheet(Sheet3 E2):
to a new inserted sheet
Name new sheet the contents of Cell E2 a Date type variable
Then insert cut row
Delete place it came from

I have variables DataArray(300) and Var being Variant

Does anyone know how to add the sheets name

Have the following code ...

View Replies!   View Related
Adding In A Cell
I would like to know if it's possible to add a number to a number in a cell ie.. cell a1 has a 6 in it and i want to add a 3 to that cell to make 9, at the moment i hilight the cell and mentally add the two numbers together 6+3 and then type 9 in the cell


View Replies!   View Related
Adding Year Plus Day
I have a formula =if(AA2="","",edate(AA2,12)) which is working well for the Year

This is a delivery run thats requires that the pick up will aways be on a Monday.
I would like the exact day for the following years.

e.g.
Monday 2009 is the 9th March
Monday 2010 is the 8th March
Monday 2011 is the 8th March
Monday 2012 is the 6th March
Monday 2013 is the 4th March

I would appreciate it if it is possible or a alternative way of doing it.

View Replies!   View Related
Adding A Colon
I am a basic excel user and what I want is to be able to add in a time and have the colon automatically entered for me. For example, I want to add 130 and have it come out to 1:30, or 1215 and have it come out to 12:15. Seems easy to me but I cannot figure it out.


View Replies!   View Related
VBA For Adding Numbers
I have a sheet that auto-populates with code from a userform that also provides the cell formatting. What I need is that in Column A it keeps a running count of the errors that are populated on the sheet.

Code for the formatting

With ActiveSheet
lastrow = .Cells(.Rows.Count, "B").End(xlUp).Row

For i = 4 To lastrow

If Cells(i, "B").Value "" Then
With .Cells(i, "A")

With .Offset(0, 0)
.Value = "1"
End With

.Offset(0, 1).Resize(1, 9).Borders.LineStyle = xlContinuous
.Offset(0, 5).Resize(1, 3).Interior.ColorIndex = 35
End With
End If
Next i
End With
What I need is instead of Value of "1" with the offset(0, 0), I need it to take the previous rows value and Add 1.

So the Range("A4").Value = "1"
Range("A5").Value = "2"

As the new errors are added to the sheet I need the value in "A" to be 1 more than the previous "A" value.

View Replies!   View Related
Adding A Number To Deg Min.dec
I have a column of Latitudes (12 32.467'S) to which I wish to add 0.09'.
The result being 12 32.557'S

View Replies!   View Related
Adding HH:MM:SS Not Working
I have a column with hours in the following format HH:MM:SS, that looks like this:

12:16:00
1:35:00
1:35:00
12:47:00
12:46:00
12:51:00
4:33:00
4:05:00
12:54:00
3:19:00
3:19:00


I am trying to add the sum total of all hours, but its not working. I have my column formatted as time -> 37:30:55

View Replies!   View Related
Adding Cells
I m adding cell with alphabets. I attach a pic of what i want it to look like. Is there a command to do it for you instead of me outting one by one myself? Ex. 'A' must first followed by 'B' and so on, futhermore, if there a CApital 'A' and a lower case 'a' the capital 'A' goes first.

View Replies!   View Related
Adding Hours
I have two employees and need to be able to add the hours each works so I have a daily running total of their hours together (the two employees can't work more than 30 hours together each week).

What do I put in F5 so I can easily read that, together, they have so far worked 7 hours and 24 minutes? How do I format the cell: time? which time? numbers?

ABCDEF1DAYDATEINOUTHOURS2
MON
02/15/102:12 PM5:37 PM3.423
MON
02/15/102:12 PM5:37 PM3.424

5

Total Hours:

View Replies!   View Related
Copyright 2005-08 www.BigResource.com, All rights reserved