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 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 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 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
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 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 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
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
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
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
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
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
Adding Variables To VBA Controls
I have a userform with a large number of textboxes. Say 100. I want to clear them all after the user submits the data to the spreadsheet. Essentially resetting the userform.

Currently I have:

userform1.textbox1.value = ""
userform1.textbox2.value = ""
...
userform1.textbox100.value = ""

Is there a way to change the textbox number to a variable? Something like:

For textboxN = 1 to textboxN =100 Do
textboxN.value = ""
Loop

View Replies!   View Related
Adding Sheets, Sorting With Vba
I have sheet with data which needs to be sorted out.
I already started with some vba code but am stuck.
Please have a look at the attached file..

Here we go..

View Replies!   View Related
Adding Macro To TextBox In VBA
I've got a userform which adds Textboxes automatically depending on a certain value. And the amount of Textboxes varies from 1 - 100. I add them with the line:

Set MyControl = UserForm1.Frame1.Controls.Add("forms.textbox.1", strControl, Visible)

Now my question is:
Can I add a macro to each of these textboxes automatically? A macro for the Change event I think it is.

All textboxes would have the same macro.

I kinda hoped it would be one of the following:
MyControl.OnAction = "test"
MyControl.Change = "test"

View Replies!   View Related
Adding Subtotal Formula With VBA
I am desperately trying to add a simple subtotal formula but receive the same error ("Type mismatch"):

View Replies!   View Related
Adding A Row In Formula Using Vba
My problem is that I want to select a cell with a "Sum" formula ex.

View Replies!   View Related
Adding Formulas To Sheet With Vba
Is there a way I can add formulas dynamically to a sheet using VBA? I need to do cost calculations in the excel sheet for each company defined as an input from the user, so the number of formulas needed will change? Is there a way to write in the formulas to the sheet?

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
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
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
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 A Progress Bar To Vba Code
How would I add a nice progress bar to the following

View Replies!   View Related
Adding Data From Worksheet To Word Through VBA
I'm having an issue with adding data to word from excel through VBA.

I would like to add the Table to the word doc, then copy and paste data below it. The code works fine. However, after the table gets added to the top of the word doc, the rest of the stuff begins pasting from the top of the doc, causing the table to appear at the bottom...

View Replies!   View Related
Adding Selections To Combo Box With VBA
This should be very easy, but I am not getting it to work. I have a userform with a combo box and I just want to hard code the values to it in VBA. I want the value in the combo box to be 1 - 10. Here is what I have so far.

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
VBA For Adding Borders To A Range Based On Condition?
I have a spread sheet I use to track tools. One tool may be checked out / returned several times a week. When I do custom sorting I end up with the Tools listed neatly by date and number. EX: ...

View Replies!   View Related
Excel Crashes When Adding Code To Workbook Via VBA
The code below resides in my personal.XLS Module. When I step through the code everything works till it reaches the point in red then I get the pop up that says excel needs to close and asks to send or not to send.

I noticed that if I choose recover my work and restart excel and I look at the code in THISWORKBOOK of the recovered workbook that the code I wanted placed there is there.

Can anyone explain why this is happening? I have the macro's security set to low and a check mark in the trust access to vb projects.

Any Idea's how to fix this?

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
Assign 2 Macros To 1 Button (or Adding VBA Code To The Existing Macro)
I'm verrry new to editing VBA code and I've been trying to figure out how to send a column of data ("Min Data" AB3:AB21 from "Sheet1") to a worksheet ("F4" - Min) and sending another column of data ("Max Data" AC3:AC21 from "Sheet1") to a different worksheet ("F4" - Max) by using only the one button ("Record Data" from "Sheet1"

I've been trying to figure out ways to edit the VBA in "Module 1" to make this work

Can anyone help me out here? If what i'm asking for isn't clear enough, just let me know and i'll try to explain in more detail.

View Replies!   View Related
VBA - Adding Border Line To Bottom Row Of Set Print Area
I am using the following macro to set print area which I found on the net. I am trying to modify it as I would also like to place a border line on the bottom row but cant find anything to assist me despite searching through several threads.

Dim myrange As String
With ActiveSheet.Range("A:A")
myrange = .Find(What:="*", After:=.Range("A1"), LookIn:=xlValues, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Address
End With
ActiveSheet.PageSetup.PrintArea = "$K$1:" & myrange
End Sub

View Replies!   View Related
Vba To Unhide Rows, Print, And Rehide Rows
I am attempting unhide the same exact rows in multiple worksheets in Excel, Print the entire workbook, and then re-hide the same cells. As I am still learning VB i have been unsuccesfull. I have attached the code that I am using,

Sub Printdoc()
Dim sh As Worksheet

For Each sh In ThisWorkbook.Worksheets

With sh
Rows("105:116").Select
Range("A105").Activate
Selection.EntireRow.Hidden = False
.PrintOut
Rows("105:116").Select
Range("A105").Activate
Selection.EntireRow.Hidden = True
End With

Next sh

End Sub

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 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 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
Copyright 2005-08 www.BigResource.com, All rights reserved