Auto Sum With Irregular Rows With Existing Formula - Bold It And Draw Lines
Feb 28, 2014
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 am trying to automate drawing a flowchart based on a moel which the user can add or remove nodes and links from at will.
I can add shapes and position them but what I want to do is to insert connectors between existing shapes (I can draw two shapes and a connector at the same time but what I want to do is add a new node and then work out which existing nodes it links to and draw the lines)
Code: Function AddConnectorBetweenShapes(ConnectorType As MsoConnectorType, _ oBeginShape As Shape, oEndShape As Shape) As Shape 'The ConnectorType can be one of three constants - msoConnectorCurve, msoConnectorElbow, or msoConnectorStraight.
I have a bit of code that I saved as an add-in. The code does a couple things: (1) Creates a right-click button that on action (2) draws a rectangular auto-shape that is the size of any selected range.
The add-in seems to loaded correctly.
However, when I launch a new excel sheet, my intent was to have the code (the right click option) be available whenever I opened Excel.
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 have added three lines above the existing data, and now the print preview doesn't work. Where the range is names as X18 I have added to bump everything down. I get an error in the SetActiveSheet.VPageBreak..... line. I'm sure the issue is with the range, but I'm just not sure what the issue is.
Here's the code:
ActiveWindow.View = xlPageBreakPreview Dim i As Integer With ActiveSheet.PageSetup .PrintTitleRows = "" .PrintTitleColumns = "$A:$Z"
I am merging columns. Col B has all text in bold. Col C has all text in regular font. When I use ASAP to merge, the merged column comes out all bold. When I use a formula to merge the merged column comes out all regular. What I want is for the merged column to contain the bold text from Col B in bold, and the regular text from Col C in regular.
What I want is...
This is for service plan B: Take all data and transer to new server.
What I am getting is...
This is for service plan B:Take all data and transer to new server.
This is for service plan B:Take all data and transer to new server.
I have designed a Macro to use an Advanced Filter to search for specific criteria in different columns. I would like the results to bold or highlight the cell if it meets the criteria for the advanced filter. There are multiple numbers in each row that are associated with each record, and I would like to be able to see which of these triggered the filter to work for that row by bolding or highlighting the individual cell.
I have made a custom list in Excel 2010 so it auto-increments the alphabet using the auto increment pull down/copy square on the bottom right of a cell in Excel. My question is I have some data in a sheet such as this
600 600 600 600 601 601 601 601 602 602 602
What I want to do is add a letter of the alphabet to the end of these numbers and have the alphabet auto increment based on the data above like this:
I am trying to Find a cell containing a string ("derf" in this case). Then bold the entire row and continue to loop till it hits the end of the row and column. Here is what I have.
Sub Macro3() Range("B65536").End(xlUp).Select 'Finds the bottom in column B and sets it to endhere ActiveCell.Value = "endhere" Range("A1").Select 'goto the top Do Until ActiveCell.Value = "endhere" 'The Do Loop Starts Rows("1:30").Select Set rfoundcell = Selection.Find(What:="derf", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False) With rfoundcell rfoundcell.EntireRow.Font.Bold = True End With Loop 'Keep looping till it hits the bottom of the columns row! End Sub
i have a worksheet which has a price list for parts, about 2500 rows. in the Column C i have a retail price and in Column D have -5% of the C. i need to add Column E -10% of CERTAIN items, the ones in BOLD Only, of Column D. and change the color of that cell, is there a easy way to do this. i have attached screen shot what i mean.
need 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.
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.
I have been working on a macro that compares a existing list of data to an updated list of data and then either moves any data not on the new list over to a completed tab (followed by deleting the record on the existing sheet), and then adds any items not on the existing sheet, but which appear on the new list, to the existing list.
I have come across a stumbling block, i have managed to identify on the existing list the rows of data that have been removed from the new list and therefore need to be moved over to the completed tab, but when i select the data it selects the header row aswell (which will always remain the same row). Obviously this then pastes the header row aswell, and also i can't seem to get it to paste in the new sheet to the next available row (i.e this will be used daily and i don't won't to overwrite the infor already in the completed tab). the next issue i have is then when i go back to existing sheet to delete the data i just copied across, as the header was initially select this also gets deleted.
The code below, is the complete code, including filtering, copying some forumals etc. The area i am getting stuck on is highlighted in red:
Sub Update() Dim bottomrow As Long Dim My_Range As Range bottomrow = Cells(Rows.Count, "C").End(xlUp).Row Set My_Range = Range("A1:Y" & bottomrow)
This task joins a string together based on a number of characters per cell in the range.
I want to isolate one range, Col N, and add an IF condition to it.
There may be other issues preventing this from happening, e.g. the number of IF that exist in the complete formula. I will isolate the current cell and its requirements and then post the entire formula at the end for reference....