a macro/code to accomplish the following:
I would like to insert a row if:
Difference in values in Column B are more than 2 .... OR ...Difference in values in Column C are more than 2
Specimen
Value 1
Value 2
[Code]....
In the above table, rows would be inserted after specimen A, B, D, and E.
a macro to insert rows based on certain cell values in column A.
I have uto 300 rows of data. Below is an example of column A.
R1
1
2
3
4
5
6
7
[Code]...
If (above the R) is an 8, I need to insert 2 rows above that R and directly below the 8.
If (above the R) is a 9, I need to insert 1 row above that R below, directly below the 9.
(Below the R there is always a minimum of 8 digits with the 9 and 10 being random).
I have excel 2007
The first three columns of a spread sheet we use if function to insert values to these cells based on the next three column values. We have already done it using IF function. However, the same function should happen through macro by referring to the column headers.
Example 1:
If in column E header (E1) ‘Contract_Status’ and E2 cell value is ‘FAIL TO PAY’ then A2 value should be ‘No Owner’, B2 value should be ‘Terminated’ and C2 value should be ‘FTP’
Example 2:
If in column E header (E1) ‘Contract_Status’ and E3 cell value is ‘TERMINATED’ then A3 value should be ‘No Owner’, B3 value should be ‘Terminated’ and C3 value should be ‘TERMINATED’
Example 3:
If in column E header (E1) ‘Contract_Status’ and E4 cell value is ‘EXPIRED’ AND column F header (F1) ‘Contract_Renewal_Status’ and F4 cell value is ‘Cancelled by Customer’ then A4 value should be ‘No Owner’, B4 value should be ‘Renewal Cancellation’ and C4 value should be ‘cancelled’
Column B contains geographical Areas. Column C contains a list of business departments.
North Env
North Env
North Ops
North Sales
North Sales
North Sales
South Env
South Maint
South Ops
South Sales
South Sales
South Sales
Etc.
The values and number of these departments will vary. I want to insert lines to sum the totals at the bottom of each geographic area based on the number of different departments. So, for this example, for the North three lines would be inserted. For the South, 4 lines would be inserted.
I have a list of 130 names in column A. I have a number value between 0-10 in column B (next to the name). I need to insert the number of rows defined by the value in column B, below each row that I already have (if the value is 0, then the row needs to be deleted). The inserted rows have to be filled with the name value from the row above.
For example - before macro:
Joe Bloggs 2
Adam Wilson 10
Peter Andrews 0
Claire Burrows 6
After macro:
Joe Bloggs
Joe Bloggs
Adam Wilson
Adam Wilson
Adam Wilson
Adam Wilson....................................
where i have a products list and respective pictures in a folder. What is should do is to get the relevant pictures appear on the screen when i select the relevant name.
View 1 Replies View RelatedI need to create extra rows of duplicate data. The number of duplicate rows depends on the number of semi-colons in the 15th column (column O).
Sheet 1 is the original working file. Sheet 2 is the result of how the executed macro for the first 2 lines of data. The first line in sheet 1 has 7 semi-colons, so there should be 7 rows of duplicate data created in sheet 2. The second line in sheet 1 has 6 semi-colons, so 6 duplicate lines, etc.
I have a macro that needs a major improvement in performance. I have a Quad Core 2.67GHz single processor computer and with the current logic it calculates 20 values per minute. I have data sets that can be up to 400,000 data points, which means it will take 333 hours. The attached Sample file has over 9000 point. For this data set it takes 7.5 hours.
The core logic of the macro is to extract what I call “Break Point” into column M and “Time Difference” between the Break Points to column O.
I got the code from this forum (thank you DonkeyOte) and made some modifications. The modified logic does the following:
1) The user inputs the starting cell. In the Sample I use G200.
2) From cell defined in button, It moves down one cell in that column and compares that value to the original cell.
If the value is greater than original cell then there are 2 possible outcomes:
a) move down 1 cell in column and if that value is less than original cell, then extract that value and copy that value to the column M (Break Point value) and copy the Time Difference Value to column O. “Time Difference” is calculated as the difference between the 2 point breaks in Column A. In this case I’ve hardcoded 0.003472222 to get 5 minutes which matches up with each incremental time in column A, but I would like the Macro to automatically calculate that by the difference between Column A values.
b) move down 1 cell in column and if that value is greater than original cell, then move down again, until find lesser value than original cell. Once we find value less than original cell, extract value and copy value to column M and copy the Time Difference Value to column O.
Once lesser value has been copied to new cell, logic is now:
a) move down 1 cell in column and if that value is greater than original cell, then extract that value and copy that value one cell to column M and copy the Time Difference Value to column O.
b) move down 1 cell in column and if that value is less than original cell, then move up again, until find greater value than original cell. Once we find value greater than original cell, extract value and copy value one cell to column M and copy the Time Difference Value to column O.
At any point in the process if we find a value equal to the starting cell, we ignore it and the logic continues. The logic continues flip flopping like this to the last value of column G.
OR
Here's the logic now in reverse I need:
If after moving down 1 cell of original cell, the value is less than original cell then logic has the following 2 outcomes:
a) move down 1 cell in column and if that value is greater than original cell, then extract that value and copy that value one cell to column M and copy the Time Difference Value to column O..
b) move down 1 cell in column and if that value less than original cell, then move up again, until find greater value than original cell. Once we find value greater than original cell, extract value and copy value one cell to column M and copy the Time Difference Value to column O.
Once greater value has been copied to new cell, logic is now:
a) move down 1 cell in column and if that value is less than original cell, then extract that value and copy that value one cell to column M and copy the Time Difference Value to column O.
b) move down 1 cell in column and if that value is greater than original cell, then move up again, until find lesser value than original cell. Once we find value lesser than original cell, extract value and copy value one cell to column M and copy the Time Difference Value to column O.
Again, at any point in the process if we find a value equal to the starting cell, we ignore it and the logic continues. The logic continues flip flopping like this to the last value of column.
Once all the Break Points and Time Difference between Break Points are extracted for each value in column G, the AVERAGE, STDEV and MAX values of column O are calculated in column Q, R and S.
There is a loop that controls the execution of the core logic until the last value in column G. In the macro I’ve hardcoded last row being 9171, but I’d like the Macro to figure out what the last row is automatically.
I know this description is a bit long but I’ve worked for many hours on getting it work properly. I just need some help to make it run much faster. I hope someone can help me out on this. I have the right logic, just need the speed now and I really can’t figure it out.
Attached is a sample files demonstrating showing how the logic to flips back and forth as moving down the column starting in cell G200.
The sample shows the results first 4 loops (Rows 200 to 203). The values in M and O are the results of the fourth loop.
Again, assistance is very much appreciated because I've taken this as far as I can with my limited experience.
EDIT - I can embed the Macro, but it'll probably much easier to actually see it in the sample file.
I'm trying to create a macro that takes data from one row and inserts it into a new row. Attached is a workbook with a before and after example of what I'm trying to do.
Each row has a security transaction that includes principal cash and interest. The data needs to be formatted so that each transaction has one row for principal and one row for interest.
Principal is identified by the tran code PAYDOWN in column C. Interest is identified by the tran code INT. However, the raw data generated includes both interest and principal under the tran code PAYDOWN.
There could be 100 or 1000 transactions generated based on the day.
I have data like this,
Member
chr
location
web
1
50000
[Code]....
Now i want to select member column values based on the differences in values between members of location column should be 50000, it means if select web then my next selection should be SEB because next value (i.e web + 50000) is 100000 and it is matching to SEB in members column.
expected results
web
1
50000
[Code]....
I am on office 2010
I am new to VBA programming so i might be making a obvious mistake. I am trying to create a macro that will insert rows depending on a cell value. I also need this to be executed from a command button rather then to occur immediately after the cell value is entered. So as a example
Lets say
Cell A2 has a value 20
Cell A3 has a value 3
Cell A4 has a value 5
when this data is entered and then a Command button poressed there should be
20 rows beneath cell A2
3 rows beneath cell A3
5 rows beneath cell A4
I have different reports, some have fifty transactions, others have thousands. My goal is to: Insert a new row every time the values in the "Account" column meet a certain criteria, AND THEN add the totals for the Debit and Credit Columns.
Let's say I start with a table that looks like this:
A
B
C
D
Dept
E
Account
T
F
F2
G
Debit
Credit
Total
33010
[Code] ....
I want to group the first four rows because Accounts 33010 and 33015 are in the same department. Same with 50050 and 500060. I want to then insert a row below the last row with "33015" as its Account #. And add the values for Debit and Credit. It'd look like this:
A
B
C
D
Dept
E
Account
[Code] .........
Honestly, I have tried everything. Running a Macros with Relative reference does not cut it.
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:
Before:
PPNCPN
4AQ02SLAG
4AQ02SAND
4AQ02CHIPS
4AQ02PORTLAND
4AQ02ADMIX
4AQ02AUTUMN BLEND
4AQ02OVERHEAD
4AQ03SLAG
4AQ03SAND
4AQ03CHIPS
4AQ03PORTLAND
4AQ03ADMIX
4AQ03BETHEL BLEND
4AQ03OVERHEAD
After:
PPN CPN
4AQ02SLAG
4AQ02SAND
4AQ02CHIPS
4AQ02PORTLAND
4AQ02ADMIX
4AQ02AUTUMN BLEND
4AQ02OVERHEAD
4AQ02LABOR -insert row and populate cells here
4AQ03SLAG
4AQ03SAND
4AQ03CHIPS
4AQ03PORTLAND
4AQ03ADMIX
4AQ03BETHEL BLEND
4AQ03OVERHEAD
4AQ03LABOR -insert row and populate cells here
I have data (part numbers) in column B. They are alphanumeric, eg 29 EE.
When I receive parts I enter the quantity of pallets received in column C next to the part number in column B.
Sometimes certain parts are not received therefore the relevant cell in column C would be left blank. There are a total of 30 part numbers in column B.
I also have an A4 landscape sheet (when printed) which is formatted into two rectangular blocks (merged cells) with fonts sized 200. The lower section contains the NOW() function and the upper section contains a part number which is entered manually. If I receive 10 pallets of 29 EE I will then print out 10 copies of the sheet with the part number and current date.
The same applies with the next part number 29 HE, if I receive 3 pallets of this part I then edit the part number for the A4 sheet and then print 3 copies.
I would like to be able to just enter the quantities received into column C and then select a macro button to print out all the sheets automatically for each part.
The reason for this is to enable older stock to be used first which can be easily identified with an A4 sheet attached when it is put away in the warehouse racking.
I have a range "RangeOne" and i want to check the range row by row.
In each row I want to check for value in second cell. If the value is 10 or more then the entire row in the range should be selected and then execute a macro. If the value is 0 to 10(but not 10 exactly) then the row in the range should be selected and then execute macro2.
I might be using something completely wrong here, but can anyone tell me if this code is possible or am I being very naive.
H4 and I4 are cells in which I want to enter dates, and then I want these dates to be used in a custom filter on another page.
Below is what my limited understanding of VB came up with.
Sub Date_Range()
Dim First As Date
Dim Last As Date
First = Range("H4").Value
Last = Range("I4").Value
Sheets("Graph Data").Select
Selection.AutoFilter Field:=1, Criteria1:=">=First", Operator:=xlAnd _
, Criteria2:="
I regularly run a report that comes out in a slightly different format each time depending on whether or not there is data for specific criteria. for instance (sample attached): criteria a, b, and c are in rows, 1, 2, and 3 in columns. a, b, and c are expected to repeat themselves several times. if there is no data in one instance of b then you may see something like abcacabc.
I'd like to loop through each row in column a, check to make sure that it is the correct value (either a, b, or c) and if not, insert a blank row so I can then copy the data to my final project without having to worry about formatting.
the example above would then become abca cabc
I am trying to copy two cells from one worksheet to another in the same work book based on the value in Column B of one of the sheets. This is just a building block to a larger script I am going to create.
Below is my attempt but I keep getting an error at Range(Cells(x, 3)).Select.
I have recorded a macro to sort a range of cell based on two values that is dependent on time in another cell. I now want the macro to run automatically when refreshing the workbook with F9, so as the time changes so will the sorting. Everything works fine except the sorting doesn't refresh when F9 is refreshed.
My recorded macro is:
VB:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim X As Long, FillCT As Long
For X = 1 To 4
If Cells(Rows.count, 1).End(xlUp).Offset(0, X - 1) <> "" Then FillCT = FillCT + 1
[Code] .....
the post 5 for the actual issue. This being my first post could not update it correctly. I have put my views int he 5th post which will be more clear.
View 9 Replies View RelatedI have an Excel sheet that contains a few thousand folder paths in the first column. The first few cells in the column look similar to below.
C:UsersNameDesktopFolder Copy12010360
C:UsersNameDesktopFolder Copy12010361
C:UsersNameDesktopFolder Copy12010362
C:UsersNameDesktopFolder Copy12010363
C:UsersNameDesktopFolder Copy12010364
C:UsersNameDesktopFolder Copy12010365
I am trying to create a macro that will copy any folders that exist in any of the paths listed in the first column. The folders should be copied to the path of the open workbook containing the macro. Below is the macro I have currently, much of which was taken from information I found in this thread [URL]....
VB:
Sub wrapper3()
x = 1
Set fs = CreateObject("Scripting.FileSystemObject")
While Sheets("Air").Cells(x, 1) <> ""
v = InStrRev(Sheets("Air").Cells(x, 1), "")
dest = ActiveWorkbook.Path & Mid(Sheets("Air").Cells(x, 1), v, 99)
[Code]...
This code seems to work fine if all of the folders exist to be copied. My problem is that some do not and it is creating a "Path Not Found" error for which I need a fix. If the folder doesn't exist at the path nothing should be copied and the next path can be evaluated.
This is a logic based macro that needs to compare values in 1 column above a specific cell and take action until the column has no more data. Here's what I'm trying to do (and I've also attached a sample of desired results):
1) I want user to input starting cell. So I need to create a button for that.
2) This is where the logic begins and I'm having difficulty.
From cell defined in button, I move up one cell in that column and compare that value to the original cell.
If the value is greater than original cell then logic needs 2 outcomes:
a) move up 1 cell in column and if that value is less than original cell, then extract that value and copy that value one cell to the right or 1c.
b) move up 1 cell in column and if that value is greater than original cell, then move up again, until find lesser value than original cell. Once we find value less than original cell, extract value and copy value one cell to the right or 1c.
How to generate a list base on the differences of another list. let me explain; I have a list of combination A1:F2200 each row of course the numbers have difference, example:
589162045620273342451261419316121721264271837414551
As you see the difference in the first row for this small example is 317425 and the list base on this difference is :
145121641256131742367141843478151944589162045691017214671011182247811
1219234891213202449101314212550111415222651121516232752131617242853
The maximum number is 53. doing this one time find, but I have to do it with my 2200 difference.
I need to insert blank row/s between the missing dates and fill the missing date & corresponding employees name. Let me explain in detail,
I have a start date in Cell E1 and end date in Cell G1. Need a macro to verify the date range in Col A with these start & end dates and insert blank row for missing date corresponding to employee names in Col B. This should be repeat till last used row of the sheet. Attached sample(Before&After) workbook.
Both missing date and respective names to be filled in the newly inserted rows.
I have a macro that runs in Sheet 1 and enters the word "Complete" in cell P of the active row. The same cell that requires the word "Complete" in Sheet 2 is in cell T of the active row. Is there a way to have the same shortcut that will identify which worksheet it is in and go to the appropriate cell?
Code:
Range("P" & ActiveCell.Row).Select
ActiveCell.FormulaR1C1 = "Completed"
ActiveCell.Offset(0, 1).Range("A1").Select
I have a few similar excel files in a specific folder (for my salesman to report their sales) - let's call those files "working files" I have another file (we'll call it "master file") in a different folder, where I update values in column A - and those values needs to appear in column B in all of the working files. it is necessary that each time the macro is been activate, it will "run over" the existing values in column B in the working files, and insert instead of them the update values from the master file.
View 1 Replies View RelatedI need to insert blank row/s between the missing dates. Let me explain in detail,
I have a start date in Cell B1 and end date in Cell D1. Need a macro to verify the date range in Col B with these start & end dates and insert blank row for missing date corresponding to employee names in Col A. This should be repeat till last used row of the sheet.
Attached sample (Before&After) workbook.
Insert Missing Date.xlsx‎
I'd like to adapt my macro so that it would insert a blank row after it detects the the first 16 characters of text as "'Closing Balance"; or it could even detect "'Closing" as the first 8 characters if it would be simpler
The Data Begins in Row 5 of Column A
My Current Macro is as below which I have adapted from another one I used
It is not working since it is detecting for the exact text "'Closing Balance" whereas the data registry would write "'Closing Balance as at 31/10/2009" , of which the "as at dd/mm/yyyy" portion would change every time a report is exported, but the first part "'Closing Balance" or even just the word "'Closing" will always be the same.
I have to use several dozen pivot tables a day. I already use a couple of macros to do a lot of the tedious formatting, but if I could automate this it would save me a lot more time. I have been trying to both write a code and modify code written by the recorder but have not even come close to anything that works. What I want is to have a macro that searches through the column header for predefined names and if it comes across one of these names it will insert a specific formula, based on its name, in the first cell below the header. Then it will autofill to the last row and move on to searching for the next predefined header name. If that name is not found, it will move to the next predefined header name. One major problem is that the formula to insert under any specific header name is based on other columns in the table that aren't always in the same range. So, not only do I need it to search for columns to place a formula in, that formula has to search for the appropriate column to find the correct value to use in the calculation.
For example, one predefined column name would be Avg Price. The formula for that column would be (Sales/Qty Sold), but these 2 columns could be located anwhere in the table. How can I get it, once it has found a column that needs a formula, to locate the correct column/cell to get the correct value from the sales and Qty Sold columns?
I'm working on some code that's part of a userform. To illustrate what I need, I will give an example. A column letter, 'J' for example, is stored in colNum.Value taken from the userform. I need both a column inserted before column J, and data entered into that new column in row 2 (thus J2, which would now be blank).
View 4 Replies View Related