I have the following ...
.Offset(3, 0).Value = "For " & P & " numbers there are " & Format(tly, "###,###,##0") & " x different values of " & cmb & " numbers. "
... which includes figures upto and including millions.
The thing is that the above code ONLY produces figures that are relevent.
I have 5 columns of numerical data from Column L to P.
I use the SumIF function in column S to add all the negative numbers, if any.
I then use the IF function in Column R to add the loan # wherever negative values are present.
Here is a screenshot of what it looks like after I'm done with the above:
All I need to do is align this data and move it to the bottom, near the total. This is to make it look presentable and i'll know the defaulting loans at a glance.
I can move it manually, but there are over 50,000 loans and its time consuming.
So, I need a macro to do it, but I'm not sure how to code it.
Below is the screenshot of what it should look like:
Its basically just eliminating the blank cells. Can someone please help me out with the macro code for it?
here is the code i'm using for the SumIf.
Sub addadvances()
Do Until IsEmpty(ActiveCell.Offset(0, -3)) And IsEmpty(ActiveCell.Offset(1, -3))
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=SUMIF(RC[-7]:RC[-3],""
I'm having trouble coming up with vba code to align values in columns A and B on my workbook. I've attached a sample spreadsheet to illustrate what I am in need of. I've searched the forum and can't quite find the same problem. In my case, their may be the same value more than once in a column, so I'm not sure that vlookup would work.
View 5 Replies View RelatedHow to show negative values displayed in brackets so that it is aligned properly with positive values in the same column. That is, the right bracket of the negative value is as follows:
(1,234.56)
800.12 - so that 2 is aligned underneath 6
I have done research on the forum and help on vba and got the following
"#,##0.00_);[Red]($#,##0.00)"
I understand that the underscore is needed for the alignment.
However, when I include the _) the above is shown as follows:
(1,234.56)
800.12_) the underscore and bracket are included and the bracket and is aligned underneath the right bracket above.
I feel stupid asking this, but for some reason I am having trouble keeping alignment of columns in a Pivot Table....
I have a Column of text in a pivot table and I am just trying to center the darn thing... but no matter what I have tried, when I refresh the table it goes back to left-aligned....
I have Preserve Formatting set on... in the Table Options.
I have a fixed height userform textbox that i would like to show the last line of.
After there is text in the textbox, enable=false.
I can see how to align for left, right and centre, but not for bottom.
I don't want to change the height or size of the textbox and just need to display the last line of data.
These charts are generated via a macro and have to be aligned using VBA.
The charts look fine on my screen bu their alignments and position are messed up when another person uses it on another computer.
This is the code i am using to align the charts
chartwidth = 300
chartheight = 200
With ActiveSheet.Shapes("Chart 1")
.Width = chartwidth * 2 + 10
.Height = chartheight
End With
With ActiveSheet.Shapes("Chart 2")
.Width = chartwidth
.Height = chartheight
End With
With ActiveSheet.Shapes("Chart 3")
.Width = chartwidth
.Height = chartheight
End With
ActiveSheet.Shapes("Chart 1").IncrementLeft -128.25
ActiveSheet.Shapes("Chart 1").IncrementTop -75
ActiveSheet.Shapes("Chart 2").IncrementLeft 182.25
ActiveSheet.Shapes("Chart 2").IncrementTop 139.5
ActiveSheet.Shapes("Chart 3").IncrementLeft -126.75
ActiveSheet.Shapes("Chart 3").IncrementTop 138
Example of Cell Movment.xlsx
The following attachment should explain what I am trying to accomplish.
I need a macro to identify the word "(blank)" based upon alignment (here it is left most) and to delete the next 4 rows only.
View 6 Replies View RelatedAs a simple example, I have three columns (A,B,C). In both Column A and B they have single word text in them, but in Column C it is a paragraph of words that I format the cells to 'fill' so that it is all tight and concise when viewing the worksheet.
Afer I have saved the document and have closed it when I reopen the document. Cells from column C have randomly duplicated themselfs throughout the entire worksheet,but only onto columns A,B,C (where there is pre-existing text). As the random cells get duplicated it overrights the original text (results) as it does it, so once I open up a document and see this it is to late. this is a continual problem that I cant find a resolution for.
I have 5 columns of data where each column of data has two number in it separate by a space where the headers for each column is c1, c2, c3, c4 and c5. for example
c1 c2 c3 c4 c5 c6 c7 etc
1 1 1 2 2 2 2 1 1 1
3 3 3 4 4 4 4 3 3 3
etc
where each of these number pairs is under a separate column. The preview option for this forum editor is showing quite a difference between intended presentation and actual..
What I am looking to do is for each line item is to put the content of each row into a single cell with vertical alignment of the pairs of numbers. for example
c6
1 1
1 2
2 2
2 1
1 1
3 3
3 4
4 4
4 3
3 3
where each group of five pairs is in a single cell.
I am looking to do this in as automated an approach as possible. I dont want to have to ctrl-enter for example 4 times for each cell in c6 for 1000 different line items..
i am looking for excel custom number format for 0 (zero) number that make center alignment..
for example ;
sample (when type 0 (zero) number)
after custom number format
- (right alignment)
- (center alignment)
how make center alignment with custom number format for 0 (zero) number..
I need to indicate if the figures for this week has gone up or down compared to last weeks figures.
So looking at the attached, I need to add a column (I5) to sheet Admitted Patients WE 20081005, so it compares the % treated in <18 wks figure by specialty to previous weeks figures on sheet Admitted Patients WE 20080921.
Ideally I would love an arrow either point up if the figures has increased or down if they have reduced.
I need to produce three different formats of accounts data monthly. I attached a file with each accounts description in Columns A, E and G. Is there a way to match the data with those accounts name in a faster way than manual linking?
I would like the last 6 figure in cell a1 to be highlighted cut and pasted into B1 - the amount to be cut will vary
View 4 Replies View RelatedI am trying to make an inventory cover page - that shows data of separate products on different sheets with the following;
Product
Date
Previous Total
Amount Taken
New Total
and occasionally there is a Re-issue of stock to the previous total.
This is at the moment an historical record as anyone can see the usage across the dates for any particular product by looking at the sheet. Each time there is a change to the inventory it is recorded on a new row. But what I am trying to do is to copy the last row of data from each sheet to the cover page to show an overall inventory.
The part where I get stuck is making the selection of the last entered row of data [from each sheet] automatically updating the coverpage, when each sheet will get new data added frequently.
I thought about somehow making a duplicate of the last row and locking it in place and linking that to the coverpage - but I still do not have a clue on how to get it to automatically select the last row of the data.
Dim figure from the cell b4,b5,c4,c5 to accumulate add total b11,b12,c11,c12, respectively.
View 5 Replies View RelatedHow to make space between the figures ex:11480831525 i want like: 11480 831525
because i want to do it for thousand of transactions in one time by using only one formula.
I want to calculate the sum of a column but I have 4 lots of figures going down the column and I only want it to calculate the some of 2 of those 4.
The 4 figures: £4.80, £3.80, 31 & 25.
These 4 figures are within the column multiple times and I just want to have one cell that sums up the £3.80's and £4.80's within the column and then another cell to do a separate sum of the 25's and 31's.
Is this possible, if so how? Does it require an IF SUM formula of some sort?
I have a large number that has 6 figures.I want to display this number to 3 significant figures on the spreadsheet, but I have more formulas that need to use the unrounded number.So is there a way to DISPLAY the number to 3 sig figs whilst keeping it as the complicated number for the rest of the formulas?
View 6 Replies View Relatedhow can I total three columns together, one with the value of #N/A, and two with figures in, without the total coming back as #N/A ?
View 10 Replies View RelatedI'm wanting to Rank a set of figures in one column but use criteria in another column to pick up the array which is used. I don't want any figures to be ignored so will have multiple rank 1's 2's etc. Would also be handy if I could use multiple criteria.
View 9 Replies View RelatedI am using a Hlookup to retrieve Words and Figures but when retrieve words it is failed and output is figures ONLY while I need the same word to be shown in the cell value?
View 2 Replies View RelatedDATE
PRODUCT
BALANCE
1/2
KEY CHAIN
4
1/2
KEY CHAIN
8
1/2
KEY CHAIN
27
[code]....
How to get final figure each product from sample data above:
the result:
Key chain = 27
Gift = 4
Hamper = 1
I have 2 spreadsheets, one is on crew costs and the other one is a summary page. On "crew cost" worksheet, say A1= IBM, B1 =Microsoft, C1=HP and D1=Pioneer. I want to link A1 to D1 to cells A1 to D4 on "summary page".
However, I do not want to use "paste special", "transpose" function as I may need to add in columns to "crew cost" spreadsheet.
Instead I hope that I could link A1 on "crew cost" spreadsheet to A1 on "summary page" spreadsheet. Then I hope to drag the formula from A1 down to D4. The result will be A1= IBM, B2 =Microsoft, C3=HP and D4=Pioneer on "summary page" spreadsheet.
However, it does not work as dragging only occurs through columns or through rows. Is there any formula that I can use?
I am working with percentages, and trying to avoid #DIV/0! Errors at the same time.
Lets assume column A holds last years figures, and column B holds this years figures, column C calculates the difference in percentages.
I am having dificulty where say A1 = 0 B1 = 10 (C1 Should be 100%)
then what happens if A2 = 5 and B2 = 0 (C2 Should be - 100%)
Is there a common formula that will account for all possible + or - figures in A and/or B that will also suppress #DIV/0! Errors?
I have a list of 30 numbers representing receipts due. I have one receipt which I know must represent the sum of some of these numbers. Is there anyway in Excel that I can highlight the range of 30 numbers and ask Excel to sample all possible combinations to find which numbers add up to my total receipt?
View 9 Replies View RelatedI have an excel database with about 5 columns and 150 rows whose cell data I want to display to 2 significant figures. Almost all the data is numeric, but there are one or two cells with text in them, and the numeric data varies between 1 and 4 significant figures at the moment, some >1, some
View 9 Replies View RelatedIm trying to find a way to script a button to delete entire rows from a selection that contains rows which total 0. It seems like an easy task but ive drawn a blank.
View 5 Replies View RelatedI'm trying to find a way of displaying results from a worksheet which can range from a few thousand to several million. However I only want to report to a set number of significant figures(5), i.e. if the result is say £25,256,128 I would like to show the resuilt to the first 5 significant figures i.e. £25,256,000, and say £3,508,569 as £3,508,600.
View 4 Replies View Related