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
adding an second range (J1089:AI1147) to the follwing code.
Code:
Sub Clear_Range()
Application.EnableEvents = False
With Range("H3:AL1058")
[Code]....
I want to add a number to existing set of numbers.
E.g. 10035-TSZ651-MT0A01-42004314-F01-023
and i want to add 0 to 023 so that the number will be
10035-TSZ651-MT0A01-42004314-F01-0023.
I would like to add a pause to the following macro so that it inserts the word CANCEL 1 second after triggering. I know I need to use the Application.Ontime function but cannot work out how to integrate it.
Code:
Sub Cancel() Application.ScreenUpdating = False
Sheets("Sheet1").Range("T5") = "CANCEL"
Application.ScreenUpdating = True
End Sub
What I missed. I already added the MS VBA Extensibility 5.3 on a VBA Reference. but still got an error.
Line1: Dim objForm as object
Line2: Set objForm = ThisWorkbook.VBProject.VBComponents("UserForm1").Designer
Line3: Set Butn = objForm.Controls.Add("Forms.CommandButton.1")
The error is on Line3, "Runtime Error '91': Object Variable or With block variable not set".
I'm preparing Quarterly Evaluations, and am having an issue because an Analyst has superseded an overall percentage goal. In the attached example, it is the overall evaluation with the 'problem data' in red D19 and highlighted.
I'd like to add a condition that limits the points in D19 for 'responsibility 3' to 150.
=IF(ISERROR(C19/B19)*(B10),"-",(C19/B19)*(B10))
*just a note for clarification, the other two responsibilities are a 'per hour' weekly goal, whereas the 3rd is an overall personal effort divided from the overall team effort.
I have a spreadsheet of over 8000 addresses. I have the street, state and zip but not the cities.
Is there a way I can do a search and mass import of cities from a website, like USPS? I'd like to know if there is something I can do quickly rather than manually look each zip code up and enter it in one by one.
In the attached workbook I have 3 sheets. The first 2 with the names starting with WO are individual sheets based on a single scaffold rental. The "Standing Equipment" worksheet is adding the components from the other two worksheets. Easy enough, but I am looking for either a formula or some VBA guidance on how to have the "Standing Equipment" worksheet automatically change it's formulas to incorporate new worksheets as they are either added or deleted, thus avoiding constant formula editing as scaffolds are erected or dismantled.
View 3 Replies View RelatedI'm currently doing a new worksheet for work
Cell's A1: Commission Per Sale Cell B1 Running total of commission
I would like the running total to continuously incorporate all of the above commissions and this has to be in the column next to the new sales commission.
E.g.
Commission $5 $7 $9
Running Total $5 $12 $21
I run the following formula down a column of records in row AR
=IF(COUNTIF(lookup!A:A,N2)>0, "Exclude", "Not In Lookup")
I want to add to this formula an additional function that if any cells in column A1 are blank, then do not perform the remainder of the equation.
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.
I'm currently using the following formula to return values rounded down to the nearest 2.5: =FLOOR(X8*1.25,2.5)
This works fine but I would like to put in place a system where if the "X8" value is 1 the formula produces a 2.5, and if the "X8" value is 10 or greater the formula produces a 10.
In a workbook made in Excel 2003, I have the following for a UserForm:
Private Sub UserForm_Activate
Me.Calendar1.Value = Date
End Sub
I copied this workbook to a computer with Excel 2007 and it bombs out at "Date"
It comes up with a compile error, "Can't find project or library"
In the references window (Tools, References) it has the "Missing: Ref Edit Control" checked and the location at the bottom of this window states "C:Program FilesMicrosoft OfficeOffice11REFEDIT.DLL".
The reference to Office11 is from the computer with Office 2003 as the computer with Office 2007 has Office12.
There is another "Ref Edit Control" in the References window and when I check it and browse to the Office12 folder, highlight REFEDIT.Dll and click on open and in the references window on OK it comes up with "Name conflicts with existing module, project or object library". I have tried to delete the "Missing: Ref Edit Control", change the priority and change the reference in the missing control to Office12 but all to no avail.
How can I change the reference to the Ref Edit Control from Office11 to Office12?
Everyone always want to get rid of duplicate rows.
Is it possible to create duplicate rows of all the existing rows?
I have created a population pyramid and want to add another chart on the secondary axis which I can do. The part which I seem not to be able to complete is getting the line chart secondary axis to read from the primary y axis. It seems to add a new axis, even when I delete this the line chart does not read correctly.
View 1 Replies View Relatedneed to create a macro that inserts 3 rows below each existing row of data and simply copies and pastes that data into each of the empty rows before moving on to the next unique row and doing the same thing again.
This is what I have so far, but I can't seem to get the loop right.
Rows("2:2").Select
Do
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
activecell.Offset(-1, 0).Select
Range(activecell, activecell.Offset(0, 5)).Copy
activecell.Offset(1, 0).PasteSpecial
activecell.Offset(1, 0).PasteSpecial
activecell.Offset(1, 0).PasteSpecial
Selection.Offset(1, 0).EntireRow.Select
Loop
I have a table with 2 columns and I want to create another table with the same values in these 2 columns but add a new column with values that are different. So for example, I have col A, Col B with values 256 and 14. I want to create a table that has Col A, Col B and Col C with 256 in Col A appearing 60 times, 14 in column B appearing 60 times and new Col C has values ranging from -4 to 55 for each new row. So new table now has 60 rows with same values for Columns A,B but different C value
I can drag or copy paste but when I have 200 unique records in first table, I want to create a second one with 200x60 rows and drag/copy paste is tedious.
Input
ID
SqnID
256
14
[Code]....
I have a column with blank rows inbetween rows with data.
Column Heading
1000
Blank
Blank
1001
Blank
Blank
Blank
1002
Is it possible to autofill the blank rows with the row that has a value above them, have it skip the next row with a value, recognize the next blank cells and fill them in with the previously skipped row, and so on down the line? How might I go about doing that?
I have an excel list of My Existing Customers and have recently purchased an excel list of all possible customers in my market that happens to have my existing customers listed in it as well. How can I remove my existing customers out of this purchased list so that I can import it into my Database as Prospects? I'm using Excel 2010. Deleting duplicates doesnt work for this. I want a function that looks at data in one spreadsheetA and if it finds it in the second spreadsheetB, it deletes the row out of SpreadsheetB.
View 9 Replies View RelatedI have managed to create something similar to what i am working for using an example from Lacher and Gant Charts. i am now stuck as I can enter more than 40 status as it then gives me an error. The following is the code: Can any1 highlight where i need to make any changes to stop the error from occuring:
Option Explicit
Sub CreateTimeChartData()
Dim vTimeData As Variant
Dim i As Integer
Dim sRoom As String
Dim vLastEndTime As Variant
Dim oSeries As Series
' set up
Application. ScreenUpdating = False
Application.DisplayAlerts = False
' create chart data worksheet
With Worksheets("TimeData"). Range("TimeList"). CurrentRegion
.Sort Key1:="Room", Key2:="Start Time", Header:=xlYes
vTimeData = .Value
Worksheets.Add
On Error Resume Next
Worksheets("ChartData").Delete..........................
I'm currently applying these formulae manually, and also instructing other people to do the same, due to my lack of understanding of macros. Hopefully someone can give me some instruction as to how to do this automatically. I'd like people to be able to open sheets sent as normal CSV's and apply a macro to get them into the right format, changing only rows with data in them.
These are the instructions I'm currently sending:
In cell n1 enter:
full phone number
in cell m2 enter:
=RIGHT("00000000" & J2,8)
Drag m2 to last record
In cell n2 enter:
=CONCATENATE(61,I2,M2)
Drag n2 to last record
In cell o1 enter:
Date of call
in cell o2 enter:
=DATE(MID(C2,7,4),LEFT(C2,2),MID(C2,4,2))
I need to totally ungroup existing grouping of rows in a sheet. Totally ungroup = strip it totally of any grouping. In short, it should be back to its original state of no grouping at all.
Problem is that I do not know if the sheet has existing grouping, or if it does, how many levels of grouping.
The solution I have in mind right now is just to indiscriminately run ungrouping vba line 10X and just place an error handler i.e.
Code:
Sub Macro1()
On Error Resume Next
Range("A5:A29").Rows.Ungroup
Range("A5:A29").Rows.Ungroup
Range("A5:A29").Rows.Ungroup
Range("A5:A29").Rows.Ungroup
Range("A5:A29").Rows.Ungroup
[code].....
Is there a shorter way to handle this code-wise?
I have a table in which the rows represent cities and the columns represent certain characteristics/ attributes a city can have. In the cells every city & attribute pair receives a "grade"
City EatingBeachSki
Barcelona 210
Chamonix 002
I would like to transform this so that every row represents a city & attribute pair and includes its corresponding grade.(I want to do this in order to be able to add more information about city- attribute pairs rather than about cities only)
City AttributeGrade
BarcelonaEating 2
BarcelonaBeach 1
BarcelonaSki 0
ChamonixEating 0
ChamonixBeach 0
ChamonixSki 2
I am aware that I can use a pivot table to sort out all the pairs with a specific grade. and then I could paste this into a new table. But my data base (number of cities x number of attributes) is huge and I was wondering if there is no automatic process to make this transformation.
I have the following macro which I use to transpose a number of columns in one sheet to rows in another sheet.
PHP
Sub task1()Dim i As Integer, n As LongFor i = 5 To Columns.Count Step 2 n = n + 1 With Sheets("Working_Checklist_1").Cells(10, i).Resize(16) Sheets("Summary_Intermediate").Cells(n + 2, "c").Resize(.Columns.Count, .Rows.Count) _ .FormulaArray = "=if(transpose(Working_Checklist_1!" & .Address & ")=0,"""",transpose(Working_Checklist_1!" & .Address & "))" End WithNextEnd Sub
I'd like to change this macro so to transpose from rows to columns. I've tried a couple of things, but can't quite get it to work.
I'd like to transpose every second cell starting from E7 to IV7 in Working_Checklist into column BU in Summary_Intermediate, starting from BU3.
I have a workbook which contains 8 worksheets. I want to able to run a macro that looks for specific words in column A of each worksheet and hides any rows in between the specified words. The following code works except if a worksheet does not contain the specified words. Is there a better way to accomplish this?
Attached is an example of the spreadsheet. The code below works just fine on the tabs highlighted in green, but halts on the tab highlighted in red. The tab highlighted in yellow is showing you the rows I need to hide.
Example.xlsx‎
I have a file with many different row sizes and after calculating the figure, we will need to add up the total for a particular job. Due to the irregular data on each job (some with only 1 line item where sometimes the job have few line items).
The cell to sum already has a formula for the data.
For example, I already have a formula on Column L to get the figures. After few line items, I will need to add a total to the lines item, also on Column L. I think we can uses 2 sets of data from 2 different column as "pointers", Column D and the formula column itself (but I don't know how to). The columns that I need a sum function will be from Column L to X. After doing the sum, can we at the same time draw a sum line? (single top line and double bottom line).
Am I being "too greedy" ? Imagine if we have more than thousands lines of such data to be added manually and to draw these lines manually.
I have used a formula to achieve this before which seemed to work although now it seems to be replacing other data already in the worksheet so I am unsure of how to fix, thinking I should just start again.
The file in question is a tracker of activities, Column A (sheet1) is the Status and once the status is "Closed" I need to run a macro that copies all closed activities to Sheet5 at the bottom of previous closed activities. Once this is done I already have a macro to delete all "closed" activities from Sheet1 meaning that it is vital the new closed activities stack onto the bottom of previous closed activities.
The Column Headers start on Row 11 with activities running from Column 12.
I've already found a TON of threads about this process but nothing that matches specifically what I'm trying to do.
I have a spreadsheet that I'm using to auto fill other tabs with data that only matches specific criteria. Here's what I'm looking to do:
Columns I, J, K, and L may be marked as either Y or N (or blank). I have different sheets that require 1, 2, 3, or 4 of those columns to match Y. For example, on sheet 2 I want to copy the entire row if there's a "Y" match on column I and J. On sheet 3 I want to match "Y" against, I, J, and K. Sheet 4 I need to match only L, etc.
I need the data copied into the existing sheets to start on row 7. I have other data on rows 1-6 that cannot be moved.
I'm running Excel 2007.
Attempting to hide columns (of cities) via VBA generates an error when that same city is reselected (either individually, or as part of the group) in the list box, upon clicking the 'Hide' button.
The error happens at this point: ...
I need a macro in a workbook to look at all the files in the same folder that have "*att*.xls" in the name and determine and copy from the range A15:W515 only the rows that have data in at least columns A, C and D. Each file will vary as to how many rows there will be and there are more than the files with "*att*.xls" in the folder. The data will be on the only worksheet in each file and the worksheet is named "G2WAttendee_xls" the data from all the files need to be copied to the file called "Consolidated webinar reports.xls" (I am using Excel 2003) and to a sheet called "Attendance Data" and added to the end of the last paste.
At the start of the macro the current file "Consolidated webinar report.xls" should be saved to a sub folder of the current directory and have the date saved added to the name. The sub folder is called "Completed reports". The data in the original file on worksheet "Attendance Data" should be deleted.
At the end of the process all the files that have had data copied from them should be moved to the sub folder "Attendance reports consolidated" (This could be done as each file is closed if that is easier).
I have headings in row 1 of the "Attendance Data" worksheet that match the headings in the various files in the folder (which will always be in row 14 of the individual "*att*.xls" files).
The folder with all the files and the "Consolidated webinar report.xls" file is at path "Z:P and S MEvaluationsWebinar series 2012-13TB".
