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


Advertisements:










Line Insert After A Change In Column H (VBA)


I currently have a macro that does several thing, i want to add another task.

I need a row inserted everytime there is a change in column H from "0" to "F" only. The infromation will always start in row 8 but the last row is not always the same, it will be different every time.

ABCDEFGHI112345678223456783#N/A#N/A#N/A#N/A#N/A#N/A#N/A45B=Bi-WeeklyID type 2ID Type 308 GAPamount due6HCCompanyS= Semi-MonthlyCO#NAMEFORMER (F) or Current (0)78546WalreBbr55a234amy01009524WalreSn32m56daniel05001041WalreS589653jode0100011945WalreBef343456caryF2001212WalreSg5365M5665mikeF40013Grand Total2,200.00


View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
VBA - Insert New Line At Top Of Predefined Table
I currently have a 200 row table spanning 10 columns. There is one numeric column and the rest are free text. The table charts the different contents on one of the shared drives here at work. The drive is frequently in use with things being added and removed from it.

I have an excel table already which can sort the contents in order of size (numeric row) or name of the folder (the first text column). I have two buttons at the top.
The first inserts a row, which I did by recording a macro and then copying the code into the button.

The second I would like to do the following: When a user selects a cell in a row, the button, then clicked, deletes the entire row. Not just the data in it, but excises the row itself and stitches the surrounding materials together. The code to delete a row I could obtain easily, but I can't work out to say "find the selected cell, and take out the row it belongs to".

View Replies!   View Related
VBA To Insert Row On Data Change
I have rows of data with each row going from column A to column P

They are sorted by column A (account No.)
I need to insert 2 blank rows between each change in account No.


View Replies!   View Related
Insert Rows At A Change In A Column
I'm not really all that familiar with a lot of things in Excel. I do know how to make a macro, which this question is to help with, but I kinda skipped a lot of learning in between.

I am wanting a shortcut that will insert a new row after each change in column A. I don't think Subtotals is the answer because after I enter the row to seperate them I will want to enter another row and seperate again by column F when there is a change that is not a blank row.

View Replies!   View Related
Insert Row At Each Change In Column
I am trying to achieve a row insert based on matching criteria. I need to check a column of text values, and each time the text value changes, copy cells (a1:c1) and insert copied selection to the row before text change.

I am only concerned with the text in the first column. For example, I have a column with sometext in each row, when the row changes to somenewtext, I want to copy the header information and insert into row before the text changed.

Header1|Header2|Header3|
sometext
sometext
sometext
somenewtext

Searching the forums, I found conditional page breaks [url] and tried to adapt the code, but have been unsuccessful in getting it to work for my needs. I have tried the following, but cant figure out how to insert the rows in the correct place. Here is what I tried.

Sub cln()
Dim myRange As Range
Dim rngCell As Range

With Worksheets("pendingRpt")
Set myRange = .Range(.Range("A1"), .Range("A65536").End(xlUp))
For Each rngCell In myRange
If rngCell.Text <> rngCell.Offset(1, 0).Text Then
rngCell.Select
With Selection.Interior
Range("A1:C1").Select
Selection.Copy
Selection.Insert shift:=xlDown
End With
End If
Next
End Sub

View Replies!   View Related
Insert Row After Every Change In Column
I need a macro that will insert a new row after a change then copy the change to the adjacent column in the new row. I found this macro 'InsertAfterChange()' Insert Row After A Change In List which inserts the new row like I need, I just need to copy the changed value to Column "B" (or whichever is to the right of the column with the original value)

883
883
(need a blank row inserted, copy '772' to adjacent column in this new row)
772
772
772
772
(need a blank row inserted, copy '991' to adjacent column in this new row)
991

View Replies!   View Related
Insert New Rows When Column Values Change
I've posted this thread in http://www.excelforum.com/excel-prog...ml#post2038238 but since the thread has been solved, I think nobody would look into my problem, so I'm creating a new thread for the purpose.

My previous thread:

Hi there,

I'm looking for the same idea only that I wanted the macro to select the data in the cells selected. Based on the code given by StephenR above, I tried to do some modification but I don't know how to make it to start from the first cell of my selection, not from row 15 as R_S_6 wanted. The range selection can be in any column. Here are the code that I used:

View Replies!   View Related
Insert 2 New Rows When Column A Values Change
Column A of my spreadsheet consists of Subcase 1000 to Subcase 1010 for example, with up to as many as 100 repetitions of each subcase in consecutive order. See Below:

Subcase 1000
Subcase 1000
.
.
.
Subcase 1001
Subcase 1001
.
.
.
Subcase 1002
Subcase 1002
.
.
etc

I would like a macro to find where Subcase 1000 finishes and Subcase 1001 begins and then insert 2 new rows between them. These 2 new rows have to be inserted between any change found in the column A field. The searching of Column A should start at Row 15. I have uploaded an example file. This file contains 2 sheets, 'Before Macro' and 'After Macro'. Hopefully it outlines what the outcome of the macro should look like.

View Replies!   View Related
VBA Macro To Insert Horizontal Page Breaks Based On Criteria Of 1 Column
I want to achieve is a procedure that inserts horizontal page breaks at certain parts of the sheet where there is a cell equal to 2. Here is the code I have so far.

Sub insert_pagebreak()
Dim printbreak_cell As Range
Dim j As Long
Dim i As Long
ActiveSheet.ResetAllPageBreaks
Set printbreak_cell = Range("AD1")
j = 1
For i = 1 To 100
If printbreak_cell.Value = 2 Then
Set ActiveSheet.HPageBreaks(j).Location = printbreak_cell
j = j + 1
End If
Set printbreak_cell = printbreak_cell.Offset(1, 0)
Next i
End Sub

Everything works until the cell value reaches a 2, and then once it goes into the If statement I get a 'Application-defined or object-defined error' at the below line.

Set ActiveSheet.HPageBreaks(j).Location = printbreak_cell.............

View Replies!   View Related
Insert Line Between Months
In sheet colA have dates how to insert lines between months. eg 3/19,4/5

View Replies!   View Related
Insert A Line Of Text
Simple question: Can an Excel Macro insert a line of boilerplate text under the last thing copied to a worksheet? I want to paste something into a worksheet, and add a message under it, and a blank line.

View Replies!   View Related
Insert Formula In Every Line
I have the code below in a userform. I would like to add a line that will insert this formula in column B everytime a new row is inserted....

View Replies!   View Related
Insert Blank Line Between Cell Value Changes
I have a spreadsheet with the following (example) :

Column A :

alabama
alabama
alabama
arizona
arizona
arizona
arkansas
arkansas

I want to insert a blank line (row) wherever the state name changes.....

View Replies!   View Related
Insert Line And Copy 1 Cell From Above
I have a large spreadsheet of demographic data. In column A there is a name, in column B there is Address1 and in column C there is Address2. First I need to insert a new line under each line of data. Then I need the Address2 data (column C) of each line to be copied/pasted to that new inserted line, but in the Address1 column (column B).

View Replies!   View Related
Multiple Line Insert With Macro
I have two sheets that i am working with sheet1 and sheet2.

I need a macro that will insert multiple rows in row 8 of sheet 1.

I also need it to copy the formula from row 8 sheet1 to every new row inserted.

the number of rows inserted will depend on colum A from sheet2.

*column A contains numbers, and the amount of numbers will vary. The data starts in A11 and continues down.

So for example. if sheet 2 column A11 and A12 and A13 are the only cells that contain numbers, then the macro will add 3 lines starting in row 8 of sheet1 and it will also copy the formulas from that row to every new row inserted.

View Replies!   View Related
Insert Line & Update Macro
I have created this macro below however if on the worksheet I insert a line or lines the macro range G25:G107 does not change - it remains static.

How can I get an insert of a line to increase the macro range by the number of inserted lines. For example if I insert 2 lines then the macro range s/b now G25:G109.

View Replies!   View Related
Insert 2 Lines Under Line 6 In The Active Sheet
I have to do a large amount of routine work with a large number of sheets. I have tried to record some macros to do the job.

First of all I would like to insert 2 lines under line 6 in the active sheet.

I then need to drag the formulas found in line 6 down over the two new lines.

Thirdly i would like to change the text string "xxx" in the formulas found in line 7 to "yyy"


View Replies!   View Related
Insert A Line On New Worksheet With Copied Data
I need to add a line on the top of a new worksheet with the data copied from another worksheets cells. Using a macro. The line has to be created everytime on the top of the new excel sheet with the previous data moving one down.

View Replies!   View Related
Insert Line Feed At Intervals In A Phrase
Each cell in Range("A1:A2000") contains a remark, each phrase or remark is
Between 5 & 70 characters all written without line feeds (carriage return,i mean Alt Enter) Just spaces between words. What I would like to do in every cell is to force a line feed (Alt Enter) every 10 characters, and if the 10th character happens to be in the middle of a word I want the line feed to be inserted at the end of this word. Note that the phrases are not necessarily multiples of 10.

View Replies!   View Related
Macro To Insert A Line And Duplicate Data
Worksheet excel with multiple lines and colums. I need a macro to automatically insert a new Line where the cursor is located, and duplicate data from previous cells: let say cursor is located L10, macro will insert L11, and duplicate L10 -C1 data to L11 -C1. I have a macro that does this function, but the Line insertion is always at the same line.May be the macro should "read" first the cursor position, Lx and then move down +1 line prior to duplicate the data.

View Replies!   View Related
Insert Excel Formula Using VBA......MATCH Function Is What I Want To Insert
I can use the MATCH function when i am in excel but i am having trouble getting it to work when i am trying to insert the formula using VBA.

Here is the code i have but it wont even compile........

View Replies!   View Related
Change Colour To A Whole Line
I have this code but is not working, i need to make an ajustment to this as well as at present it will only change colour to 1 cell but i need it to format the whole line, this is what i have so far

View Replies!   View Related
Random Generator, Change A Line Or Two
I have a random Generator but it only generates values that are different, is it possible to change a line of code to allow it to duplicate the same number? Here is the code I have and it works great but not for duplication:

Sub Unique_Numbers()
Dim x As Long, y As Long, z As Long, tempnum As Long
Dim flag As Boolean
Dim i As Integer
Dim foundCell As Range
Application.ScreenUpdating = False
x = Application.InputBox("Enter starting Random Number" _
, "Random Number Generation", 1, , , , , 1)
y = Application.InputBox("Enter ending Random Number" _
, "Random Number Generation", 1000, , , , , 1)
z = Application.InputBox("How many random numbers would" _
& "you like to generate (

View Replies!   View Related
Having A Scatter Line Change Colour When Below A Certain Value
i found the appropriate chart example in the chart section which was meant to show how to incorporate a feature which allows a line graphs line to change colour every time it hits a minimum/maxm value-however the example does not exist? re:conditional line

View Replies!   View Related
Change Line Border Color Of Shape
I have this simple code which sets the objects format as default.

Private Sub FormatTextBox()
ActiveSheet.Shapes("FormatBox").Select
Selection.ShapeRange.SetShapesDefaultProperties
End Sub

Is there a way to set an objects shape to the default format...not as default?

ie: if the default line color is blue. what code could i use to set an object with a black line color, to the default blue?

View Replies!   View Related
If Line Ends In Assigned Value Then Change Item In COMMENT.
I have an Excel file with text records, 1000s of lines long Trancriptions of Customer sales reps and Customers, or Distributors. All data is in Collum A.

Each Record has 7-10 Entries
______________________
BILLNUM : 060501
ORIG : 12345678909090
REP : 45672222222222
AREA : LK787878000000
SD : 060401
ED : 062025
COMMENT : CUSOMTER CONVERVERSATION WITH REP
C:HELLO
R:HELLO
C:MAY I HELP YOU
R:BALANCE PLEASE
etc......................

View Replies!   View Related
If Column Contains Data Then Insert A Blank Column And Shift Specific Column To The Right
What I'd like to do is; If column C contains data then insert a blank column and shift column C to the right.

View Replies!   View Related
Link Cells From One File To Another And Insert A New Line On The File
when I link cells from one file to another and insert a new line on the file I am linking to. the file that is linking goes down as follows:

=Safety.xls!$A$5
=Safety.xls!$A$6

How do I get Cell A5, to link to Cell A5 even if I add a new line in the Safety.xls spreadsheet. Taking the $ away does not work I have tried that.

View Replies!   View Related
Insert Row At Each Change Of Value
I have a set of data
TimeABC
110000
3802010
5802010
9602010
11603030
13404030
15204050

I want to run a macro that add a row if the values in A,B, or C change. Example, from time 1 to time 3, the value in both A,B, and C Change. I want to add a row between those 2 rows.
TimeABC
110000
310000
3802010

I also want to add a row if only one of them is changed. Example is between time 5 and 9.
TimeABC
5802010
9802010
9602010


View Replies!   View Related
Insert Row At Every Change
Insert Row At Every Change In Month. I have another doubt in that. My Problem is that thee are 2 columns containing Dates. What the Macro does is, it considers the Column A where the date is present. But my requirement is, that the Column E should be considered & Not Column A.

View Replies!   View Related
Drawing A Line With VBA
How would i have VBA draw a line between a two ranges? say i have a named range of "start" and another name range "stop". i want to draw a red line between them. how can i do that with code. this is beggining my education for a much larger project I want to do later in the future.

View Replies!   View Related
Insert Rows At Change In Cells
I have a large file that has the following setup
2003 Zipper X
2004 Zipper X
2005 Zipper X
2003 Zipper Z
2004 Zipper Z
2005 Zipper Z
How can I insert rows at changes in the product description

View Replies!   View Related
Insert Row At Every Change In Month
I would like to have rows inserted before the beginning of each Month

Eg:

12/21/2006
12/21/2006
Insert Row
1/11/2007
1/11/2007
1/18/2007
1/18/2007
Insert Row
2/8/2007
Insert Row
3/1/2007
3/22/2007
Insert Row
4/5/2007
4/26/2007
4/26/2007
Insert Row
5/17/2007

View Replies!   View Related
Insert Row After A Change In List
I have several column of numbers with like-numbers grouped together. I need to insert a blank row after every group of numbers. For example:

883
883
(need a blank row inserted)
772
772
772
772
(need a blank row inserted)
991
(need a blank row inserted)

The ranges are all named but I don't know how to write a macro to put in the blank rows.

View Replies!   View Related
Date Column (insert In The First Column Dates Starting From 01/01/2000 All The Way Up To Today)
I'm trying to create an excel worksheet as follows:

Column1 Column2 Column3 Column4 Column5
Date Time In Time Out Hours Worked Pay
01/03/2000
01/04/2000
01/05/2000
.
.
11/11/2008

I want to insert in the first column (Column1) dates starting from 01/01/2000 all the way up to today, 11/11/2008. So I would end up with a unique date on each row.
To accomplish this manually would be a daunting task. I'm a newbie to excel and don't know how to use any in-built functions.

View Replies!   View Related
Code Too Long For One VBA Line
I have a large formula written in my VBA code (a sumproduct with numerous variables) which exceeds the line length in VBA and therefore a proportion of the code is shown on the next line.

However, the VBA treats this second line as an error (since it sees it as a new line of code which on its own doesnt work) What do I need to do so that it treats the two lines as a single line of code?

View Replies!   View Related
Add Row If Value Value In Column Change And Paste Specific Value Depending On The Change
I have used the below code to insert a new row when the value in coulmn A change. I now need to evolve it so that the new row will contain a specific value depending on the changing value:

Before:
Column A Column B
one test
one test
two test
two test
three test
three test

After:

Column A Column B
one test
one test
Coz two............................

View Replies!   View Related
Static Date Insert - Change When Modified
I have a spreadsheet that has the date in one cell. I want the date to remain static unless I make a change. ie, when I first create the sheet I want a static date to go into this cell. Then I only want this date to change if any changes are made to the workbook (several sheets), I dont want the date to change everytime I open the spreadsheet. It must however update if I make any changes to particular cells on various sheets.

View Replies!   View Related
Insert Rows Before & After Each Change In Data
I need to loop down a column of data containing branch numbers and whenever there is a new branch, I need to insert a row both before and after the last instance of the previous brance. For example:

If the first three rows in the column contain branch 0001 and the forth row starts with branch 0002, I need to insert a row both before and after the 0001 in row three...


View Replies!   View Related
Insert Time On Change In Value Within Row - 2003
Im searching for a script to help me insert the current time value when a value within the row is changed. For example, if a value changes within the specified row, i.e. B1 changes, then within B12, the current date is inserted. However if B1 does not change then the old B12 value (old last change date) stays the same.

View Replies!   View Related
Worksheet Function: Change C2 To Change To Activecell Column + Row 2
How would I add this formula as a worksheet function with VBA. I can't see INT, MOD or Year in VBA. Also want to change C2 to change to activecell column + row 2.

=INT(((C2-1461)- SUM(MOD( DATE(YEAR(C2-MOD(C2,7)+3),1,2)-1461,{1E+99,7})*{1,-1})+5)/7)

View Replies!   View Related
Read A Date Rather Than A Line VBA Macro
I have the following code which reads line 54 (row 54) in a spreadsheet and calculates the following using data in subsequent columns. Sometimes the data I need isn't always in row 54, I would rather have the macro read the data in Column A[AB] that is on line 54...sometimes on 53.

The date is Sep-09. How can I make it read this rather than AB54. I think I just need changes @ the top (ie Dim rng as Range, Set rng = [AB54]).

Sub Profile_info2()
Dim rng As Range
Set rng = [AB54]
rng.FormulaR1C1 = "=RC[-12]+R[-1]C[-12]+R[-2]C[-12]"
With rng.Offset(0, 2)
.FormulaR1C1 = "=((RC[-28]+R[-1]C[-28]+R[-2]C[-28])*4)/((RC[-25]+R[-1]C[-25]+R[-2]C[-25])/3)"
.NumberFormat = "0.0"
End With
With rng.Offset(0, 3)
.FormulaR1C1 = _
"=((RC[-15]-RC[-29])+(R[-1]C[-15]-R[-1]C[-29])+(R[-2]C[-15]-R[-2]C[-29]))/(RC[-15]+R[-1]C[-15]+R[-2]C[-15])"
.Style = "Percent"
.NumberFormat = "0.0%"
End With
rng.Offset(0, 4).FormulaR1C1 = "=RC[-8]"
With rng.Offset(0, 5)
.FormulaR1C1 = "=(((RC[-17]-RC[-31])+(R[-1]C[-17]-R[-1]C[-31])+(R[-2]C[-17]-R[-2]C[-31]))*4)/((RC[-28]+R[-1]C[-28]+R[-2]C[-28])/3)"
.Style = "Percent"
End With
End Sub

View Replies!   View Related
Multiple Vba Commands On A Single Line
Is it possible to have multiple VBA commands on a single line with a separator ( ; or , ) of some kind?

I am trying to keep my code as short as possible (from a scrolling perspective) and would like to have something like the following:

Dim MyArray(1 to 20, 1 to 3)
MyArray(1,1) = "Sheet1" ; MyArray(1,2) = "D8" ; MyArray(1,3) = "D9"
MyArray(2,1) = "Sheet1" ; MyArray(2,2) = "C49" ; MyArray(2,3) = "w3"
MyArray(3,1) = "Sheet1" ; MyArray(3,2) = "A23" ; MyArray(3,3) = "AB12"
MyArray(4,1) = "Sheet1" ; MyArray(4,2) = "Z19" ; MyArray(4,3) = "W12"
MyArray(5,1) = "Sheet1" ; MyArray(5,2) = "Q32" ; MyArray(5,3) = "Q23"


View Replies!   View Related
VBA To Pass Command Line Parameter
I found the following link that got me started [url], so now I have my code for reading/analyzing comand line parameters. Here is an example of the parm I'm passing in ....

View Replies!   View Related
Write A Line Into A HTML File With VBA
Say there is a HTML file at C:DataFile.html

I'd like to use Excel VBA to write this meta tag into that HTML file:

****** http-equiv="refresh" content="600">
That's to make it refresh every 600 seconds.

The most elegant way would be to locate the "head" tag and write it right after it, but the refreshing meta tag seems to work where ever it is, even at the very end of the file after the "/html" tag! So locating the "head" tag is not that important, if it's difficult.

So, how do I use VBA to write lines into a HTML (or any text) file?

View Replies!   View Related
Line Numbers In VBA Macro Code
If I number my lines of code and an error occurs, the use of Erl in the VBA will then return the exact VBA line number that the error occurred on. Is it possible to automatically capture or record that same effect when the macro process leaves its current sub and goes to another?

Reason: I'm trying to create a Call Stack that can be reported through an Error Handler that will include the exact location and process that the error occurred to better troubleshoot and understand where and why the error occurred.

The best I can come up with so far is manually putting in bookmarks along way so I know how far along the macro went before the error. From all of my searching I believe retrieving the Excel Call Stack is not possible and so one must be manually created.

Enclosed is an example of what I have so far. It goes through several macros and logs the Call Stack. Itís a work in progress so it is a little sloppy looking but it is functional. If a Sub finishes it is then taken out of the Call stack.

Several "BookMarks" are placed to give an idea of how far along the Macro has gone within that Sub. The Code for the Erl example is:

Sub SampleErrorWithLineNumbers()
Dim dblNum As Double
10 On Error Goto PROC_ERR

' Errors if table doesn't exist
20 Select Case Rnd()
Case Is < 0.2
30 dblNum = 5 / 0
40 Case Is < 0.4
50 dblNum = 5 / 0
60 Case Is < 0.6
70 dblNum = 5 / 0
80 Case Is < 0.8
90 dblNum = 5 / 0
100 Case Else
End Select
110 Exit Sub

PROC_ERR:
120 MsgBox "Error Line: " & Erl & vbCrLf & vbCrLf & _
"Error: (" & Err.Number & ") " & Err.Description, vbCritical
End Sub

View Replies!   View Related
Vba Find Pattern Matching Whole Line
Using the Find Function in VBA with pattern matching, I want to find the End statements in my code. How do I stop it finding the End If statements, of which there are hundreds maybe thousands. I tried End[ ][ ][ ] and End[!I] etc but they exclude the End statements as well.

View Replies!   View Related
Insert Row & Description On Each Color Change
I have a list of data in column A. This is grouped by a set of key information and then subsets. The beginning of the key data is signified by the cell colour blue and then the subsets signified by the colour yellow. I want to insert a row just below the colour blue and enter the value " Header" and then when each colour is yellow, insert a row and enter "Subset 1" for the first and then "Subset 2" for the second etc.. The number of subsets can vary.

ABC Customer (Colour Blue)
Info
Info
Product 1 (Yellow)
Info
Product 2 (Yellow)
Info
Info
Product 3 (Yellow)
Info
XYZ Customer (Colour Blue)
Info
Product 1 (Yellow)
Info
Info
Product 2 (Yellow)
Info

Should be:..................

View Replies!   View Related
Transpose Formulas From A Line To A Column
How do I transpose formulas from a line to a colllumn?

View Replies!   View Related
Run The Macro For Each Line In Column
I got to the point where the macro is doing (more or less) what I want.
I now need to run the macro for each line in column C.
Basically my macro is creating 9 lines in column E getting as input C1 only (for the time being).
How do I say to my Macro, please run in E, but the first time you run get as
input value C1, the second time C2 and so on until I've got values in C?
I've looked into the loop documentation (for and foreach) and overall it makes sense, but how to refer each time to the next line in C?


View Replies!   View Related
VBA - Filter On Next Line In Autofilter Dropdown List
if anyone knows some VBA code to filter on the next line down in an autofilter list.

My spreadsheet has a column with the names of people, which can appear multiple times in no particular order.

I have a macro that I run several times -- once for each individual in the column. The way that I currently do this is by manually using the autofilter on that column to select a person from the autofilter dropdown list. After I've manually filtered on a person, I click a button which runs the assigned macro. I then manually select the next person in the autofilter list, and click the macro button -- over and over until I've done this for everyone in the autofilter list.

I'd like to add some code at the end of my macro so that the macro ends by automatically filtering on the next person (line) in the autofilter list.

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