Inserting Row Based On Condition

May 4, 2009

I want to insert a line wherever I encounter a number. I have a range of cells in column "A" with invoices listed and at the end of each customer I have the total. At the total line (the number) I want to insert a line. Right now I can't figure out how get the cell value to recognize a number from a non-number data type (bolded area)

View 5 Replies


For Loop - Inserting Row If Certain Condition Met

Aug 29, 2013

Inserting a row. I have a for loop, and after a certain condition is met, add a row.

VB : Rows(i).Insert Shift:=xlDown

View 1 Replies View Related

Creating A Report - Inserting Text And Row If Condition Is Met

May 16, 2013

What I need it for is an automated report that should be used for several different projects. Each projekt have events on different dates, and I only want the report to show a specific date if there is some text (i.e. event) on that date. As it is now, it's a looong report with several blanks with only a date showing. So it should insert a row (the tricky part) + the date + the text if condition is met, and do nothing if they are not. The script should add and remove rows and text depending on what project the information is taken from.

The data it should grab is in this form (many more data inputs though..):
Date Date Date

And the report:


Date: TEXT


and should be like:

Date: TEXT

View 1 Replies View Related

Looping Through Range And Inserting Formula Where Condition Met

Feb 6, 2014

I am having trouble looping through a range and inserting a formula where a condition is met.

My range is "h9:i"

My current code inserts a formula in column i when there is a value in the adjacent row in column h:

[Code] .....

However, because there are so many rows (12,000+) it is taking a considerable length of time to run.

Some of the cells in column i will already contain the relevant formula, so to speed this up I want to insert the formula in column i only when the cell value (in column i) is not null, as opposed to inserting it for all cells within the range.

I know how to loop through the range, but the problem is the 'H9' cell reference in the formula in the code above will need to change depending on what row it is being inserted into- so if it is going into "i31" this will need to be "h31".

View 7 Replies View Related

How To Count Condition Based On Having Another Condition

Apr 12, 2014

So I have one sheet that needs to pull data through to another sheet (which is a stats summary)

I have a drop down list containing 4 options all of which have to be counted separately on the stats summary sheet. However I only need them counted when a value is input in another cell in that row.

For example: I select option 1 from down down menu, but I only want this to be counted on the stats page when I enter a date in the "date" cell.

View 9 Replies View Related

Inserting Row In Excel Using VBA Based On Y Value

Apr 18, 2013

i would to insert a row in excel using vba based on a y value

if insert.row = y(any value) then those many rows should insert

View 4 Replies View Related

Inserting Lines Based On IF

Aug 13, 2008

I have an amount of data split into various fields. I need to do something similar to the following:

In each row I have three fields separated by columns: name, sector and specialty. There is only one name and one sector for each record, but possibly several specialties (of which there are fifteen types in total, so they do not appear in a consistent column. specialty 'i' could appear in columns 3, 4, 5 through to 17. ie, sample entries below:

sheet 1
a1 name a2 sector a3 specialty i a4 specialty iv
b2 name b2 sector b3 specialty ii b3 specialty iv b4 specialty x
c3 name c4 sector c5 specialty i c6 specialty ii

The thing is, for every specialty in a row, I need new records created, so the above would become:

sheet 2
a1 name a2 sector a3 specialty i
a1 name a2 sector a3 a4 specialty iv
b2 name b2 sector b3 specialty ii
b2 name b2 sector b3 specialty iv
b2 name b2 sector b4 specialty x
c3 name c4 sector c5 specialty i
c3 name c4 sector c6 specialty ii

View 9 Replies View Related

Inserting Pictures Based On A Cell

Nov 21, 2007

I want to be able to a place a picture on a workbook based on a cell. In column A, I will run a sql query to pull a list of items. In column B, I have the items formated thus they have a the .jpg. next to item of the picture i.e Hammer.jpg. whereas column A lists Hammer and Column B Hammer.jpg. In column C, I would like the pictures to reside in this column.

So far I have this macro but I can only get two pictures to come up even though my list has 4 items on it.

Sub pic()
Dim x, c, d, a As Long
d = 2
c = 40
For a = 2 To 6
b = Cells(a, 2)

View 9 Replies View Related

Inserting Rows With Formulas And Formats Based On Cell Value

Jan 11, 2014

I am attempting to create a template to make time and dollar budgets for various projects easier to create (20 plus projects a year with varying number of employees).

I want to have a template in which you input the number of employees working in a cell (IE. 3 employees), and with that the table expands to have enough rows to have 3 employees data input. However, only insert the number required but not overriding "totals" row below. With the new inserted rows I would like to keep the same format and formula for the row it was based on.

Example :

week 1 hours
week 2 hours


View 2 Replies View Related

Inserting Page Breaks Based On Cell Text?

Feb 13, 2014

I'm working with a large report that includes ~6000 rows of data. Within the report the data is separated into a couple hundred town names - "Town of XXX" - and I need a page break at each town. Is there a way to insert a page break based on text in a cell? Should be an option in conditional formatting but... Need to avoid VBA.

View 1 Replies View Related

Inserting State Names Based On Telephone Numbers

Oct 22, 2007

I have a large data set (excel file), of "Names", "Phone Numbers", and i need to sort this based on the States that correspond with the Phone numbers. The states currently do not exist in the spreadsheet, so my current problem is trying to insert those states into the spreadsheet.

There are over 100 area codes in the data set, so i'll likely have to write a large "If" statement in VB to run through them all, but that shouldnt be a problem.

Bleh, 555-555-5555, =ChkState(B2)

I've been playing around with the VB Stuff in Excel and this is what i've come up with for trying to insert the State field

Function ChkState(pVal As String) As Long

Dim AreaCode As String
Dim StateAbrv As String

AreaCode = Left(pVal, 3)

If AreaCode = "201" Then
StateAbrv = "Test201"
ElseIf AreaCode = "203" Then
StateAbrv = "Test203"
ElseIf AreaCode = "555" Then
StateAbrv = "Test555"
StateAbrv = "0"
End If
MsgBox StateAbrv
End Function

I'm fairly new to this VB stuff, my main problem stems from trying to insert the "StateAbrv" back into the Cell for the spreadsheet.

View 9 Replies View Related

Inserting Rows And Copying Data Based On A Value In A Cell

Aug 14, 2009

On sheet "Create Package" in cell "AA14" I have a value (lets say 2).

I want it to go to sheet "Samples" and insert a number of rows equal to the value on sheet "Create Package" cell "AA14" (so 2 rows)

I have a header row in row 1, so I would like it to insert the designated number of rows beneath that.

Then I would like it to copy a designated number of rows (based off of the "AA14" value, so 2) from the "Create Package" sheet starting at row 66 and then paste special values into the new rows that were inserted on sheet "samples".

View 9 Replies View Related

Inserting Page Breaks On Relative Rows Based On Data

Oct 29, 2008

Each week I format a report that I receive that lists every product a particular employee closed. Its a list of each employee, the product and date. I'll have about 20-30 lines for each employee. I can easily sort by the employee name to have a nice list.

From here what I do is use a forumla to mark where each employee's name changes, then filter by that changed line and insert page breaks for each of these lines. Is there a way to macro this to save me time, having 270 employees takes alot of time still, because thats 270 manually inserted page breaks.

View 4 Replies View Related

Sum Based On Condition

Dec 15, 2009

I want to total column C, but only where column B contains certain data, like add column C where column b contains abd so that i get the answer 7, Hope ive explained it right.

abd 1
abd 2
bmr 3
abd 4
crc 2
crc 3
bmr 2

View 2 Replies View Related

Set A Formula Based On Condition

Mar 22, 2009

I have a column called Average and it have numbers from 0 to 100. I want to set a string on another column based on the value of Average. The condition is

< 50 is negative
= 50 is neutral
> 50 is positive

For example
Column A Column B
45 Negative
50 Neutral
80 Positive

View 4 Replies View Related

Insert Row Based On Condition

Aug 12, 2009

I would like to write a code, that can check the name of cell A1 with A2.

If A1=A2, then insert a blank row between these two rows.

I have started with a loop, but somehow it does not work.

Sub AddRows ()

Dim Row As Long
For Row = 1 To 3800

If Cells(Row, 1).Value = Cells(Row + 1, 1) Then
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

End If

Next Row

End Sub

View 9 Replies View Related

Delete A Row Based On Condition

Dec 14, 2009

I want to delete a row in excel based on a condition.

The condition is as follows:

I have few rows in the excel where the columns 7,12,13,23 are same and column 24 in one row has some number and in some other row the column 24 is blank with orange color filled(the colorindex for orange is 44).

In this case I want the row with orange color filled in column 24 to be deleted.

View 11 Replies View Related

Vlookup Based On 2 Condition

Jan 28, 2009

i looking for function, which will give me a results if its the person good , bad and other (Y). It will look based on column A, but if will be more equal names in this column, it looks in the column surname (B) and return value from column (Y) in this row. But it can be more equal column B.

Here is an example:
Results what i want are in Z1,Z2,Z3

1 sue doe good
2 joe bond very good
3 mark no bad

SOURCE table
1 john doe good
2 joe black bad
3 sue doe good
4 mark black not bad
5 joe bond very good
6 mark no bad
7 kat doe bad

View 4 Replies View Related

Sum Row Based On Condition Refering To A Different Row

Dec 9, 2009

I have a problem with bringing up the data I need to cells in one row.

In row 1 I have dates:
23-Nov 24-Nov 25-Nov 26-Nov etc.

In row 2 Quantities:
2000 1500 250 750 etc.

In row 3 I have dates again (I need to start working towards these qty's): 20-Nov 22-Nov 25-Nov 25/Nov. I would like in column A of 4 row to sum all the qty's (row 2) if the date in row 3 is the same or smaller than in row 1 column A. But in column B and forward I want cells to sum qty's if the date in row 3 is the same as the one in row 1. I have tried : =sumproduct((A3:D3<=A1),(A2:D2)) for cell A4

and: =sumproduct((A3:D3=B1),(A2:D2)) for cell B4 and consequently for others. It comes back as zero value even if I see matching dates/qty's. Does anyone know what shall I change in my formula to make it right?

View 3 Replies View Related

How To Change One Condition Based On Another

Feb 2, 2007

I have a "if" statement that goes something like this:

=if(and(Function1),do this, if(and(Function2), do that, 0)

where Function 1 and Function 2 consist of three conditions each.

Now Function 2 has reference to a particular cell (U23), whose value is dependent on other cell references.

If U23 has some real value, I want the above statement to be executed.

However, if U23 displays a "#N/A", I want only Function1 to be executed. i.e. = if(and(Function1),do this,0)

View 9 Replies View Related

Subtotaling Based On A Condition

Sep 27, 2009

I autofilter a list which contains, among other fields, program numbers in column A, account numbers in column B and dollar amounts in column E. I want to display the filtered/subtotal of column E if I select a certain program. Each program has account numbers ranging from 1000 to 8999.

I used =SUM(IF()) formulas to separately display totals with account numbers below 8000, and >7999 with no problem. But when I got to create formulas to display the subtotal, I encountered difficulty.

I used the following CSE formulas to subtotal sum of column E based on the values in column B, the first formula return 0 (zero), which is incorrect. The second formula appears to ignore the IF statement, and returns the total sum instead of the filtered total.

1. {=IF($B$4:$B$112>7999,SUBTOTAL(9,E$4:E$112),0)}


View 8 Replies View Related

Maximum Based On Condition

Dec 28, 2006

function that can find the maximum value in a range, where the range is defined by the rows containing a specific string, i.e:

In column A:
Alm Rød
Alm Grøn
Alm Blå
Special Rød
Special Grøn
Special Sort

In Column B I have values for each of the above string names.

Now I would like to find the maximum value for the range with names starting with "Alm".

View 6 Replies View Related

Delete Row Based On Condition

Jan 19, 2007

i have an issue i need to delete entire row if a condition is met, i need syntax if possible to delete if a cell word "begins with" a certain letter this case...letter A

column is "O"
must search threw entire row

View 3 Replies View Related

Count Total Based On Condition.

Jan 18, 2010

I need a formula that will return a total for the number of cases that have been closed within 6 months. Also I need a percentage total for number of cases that have been closed within 6 months. So the conditions are that the case has been closed and within 6 months.

I tried to use a IF formula that returned a 1 if within 6 months and a 0 if outside 6 months and then just SUM() the column, however for some reason that just returns 0. I am using Excel2003

View 4 Replies View Related

Filter Based On Multiple Or Condition

Nov 6, 2013

Is there a way to filter data based on multiple OR condition with a like parameter.

I have data which has near about 50000 rows and now i would like to filter on a header called "Activity Type" and would like to fetch result for a text which resembles "from ABC group" or "From PQR group" or "From XYZ group".

View 5 Replies View Related

How To Have One Cell Copy To Another Based On Condition

Mar 10, 2014

I'm trying to set it up so that if a particular cell has any data in it, the contents of a different cell get sent to another. I want the no data/data to serve as a trigger.

As an example, if I put anything into cell A3, I want the contents of cell A1 to copy to H1. I'd also like it to be dynamic so that if I put something into cell B3, the contents of B1 get copied to H1, etc.

View 4 Replies View Related

Insert Row And Values Based Upon Condition

Apr 4, 2014

I need a macro I can run that will insert a row whenever Column A changes (from PPN A to PPN B to PPN C, etc.) and that will insert string values into the Cells as follows:

In all cases, the insert cell values into column A (PPN) will be the value from the previous cell and column B (CPN) will be 'LABOR'

Here's image of before and desired after:



4AQ02LABOR -insert row and populate cells here
4AQ03LABOR -insert row and populate cells here

View 1 Replies View Related

Autofill Based On Cell Condition

May 20, 2014

I have a workbook sheet change condition that, when triggered runs the following code (amongst others).

[Code] ......

What I would like is to amend the above (possibly in a loop?) so that it skips the rows in which the value "New" features in range F17:F190.

So basically Autofill as per the code but leave the values in the cells of the range in which New features in range F17:F190

View 4 Replies View Related

VBA To Delete Entire Row Based On Condition Met?

Jun 16, 2014

I have table in which against the Column A data values are added in Column B, C and D.

If Column B,C and D cell values is 0, then I want entire row including A to be deleted. How can I do this in VBA?

Note: Column A data begins from Row 15 and below.

Just to avoid confusion. I want row deletion, only when all the three cell on B,C and D is = 0

View 7 Replies View Related

Highlighting Range From A:I Based On Condition

Mar 14, 2014

I have a file in which I have data from A9:I50.

In the column C i have the type mentioned either MTC or SELF.

If the value in column C is SELF then I want to highlight the corresponding rows from A:I.

[Code] ......

This code is colouring the entire row.

I want it to be highlighted from A to I.

View 2 Replies View Related

Copyrights 2005-15, All rights reserved