I have been the code below in many a different form since royUK first helped me find a certain value and delete the entire row. I've used it to find values, text, multiple conditions and so on and so forth, but I cannot figure out how to compare the cell to the cell directly above.
View 10 RepliesIs there a way to make a macro to make this true?
The WS is 300 rows,
The column width is 6.90
If LEN(B1)>147 then format ROW Height to 0.25
If LEN(B2)147 then format ROW Height to 0.25
If LEN(B300)
I'll admit I know very little about creating a macro in Excel so I hope someone can help me out with creating what I consider a fairly simple macro to adjust values in a column or columns.
What I am using this worksheet for is a merchandise database that contains 3 columns of pricing values, ie. Column headers Small, Medium, Large.
The columns are approximately 2000 rows deep.
I need to change the pricing values in all 3 of these columns by a specific amount. However there are some values in the "Large" column that will not be adjusted. Those values not adjusted will be based on the contents of another cell in the same row that would only contain the data "OT,JB" or only "JB".
I've considered using find/replace but think it would be very inefficient.
Right now I am looking to lower the price values by 2 dollars. All values are numerical and formatted as Number/General. ie, 11.95, 7.95
I've considered creating another column or linked worksheet and forumla-ize it and then copy/paste my value results back, but I am hoping for something less cumbersome, something that is expandable as the worksheet grows, etc. I think a macro, but I do not have any idea what to do or how to get started.
The macro below works perfectly except for one problem. I only want it
to set the print area from Column A - Column M . It currently selects data i have in Columns N and beyond and i do not need that to print. From A - M is 13 columns.
i want a code to adjust the column A:Y from width
View 9 Replies View RelatedI want to concatenate two Cells into a single cell BUT have the first field left justified and the second cell right adjusted.
A1 = "John Williams", A2= "Single"
A3 = "John Williams Single"
I have a worksheet used for a car game. Each row is designated to show one particular car that can be used in the game, so let's say that there are 40 cars taking up 40 rows.
Column A shows the car type and model; column B shows the engine that the cars has represented by a number (the higher the number the better the engine is); and so it continues, Column C shows the transmission; column D the brakes etc.
Underneath the 40 rows (lets say from row 45 to 65) I then have a table with spare-parts that can be used to replace the cars existing parts. This Parts-table is also arranged with engines in column B, transmissions in column C, brakes in column D etc...
This point of this layout is that it should be easy to compare each type of part in the spare-parts table with the corresponding type of part already used in any of the cars as everything is perfectly lined up by the columns.
About the spare-parts table: because the inventory in the spare-parts table often are added to or subtracted from, as parts are won in races or being put on the cars, I have created a macro to sort the parts in each column so as to put the best parts at the top of the table and the bad ones towards the bottom - so far so good.
The problem is this - as I add new cars, the table of spare-parts is pushed down worksheet and that means that the macro no longer will reference the right rows. So the question is this, how do I automatically update the macro to adjust for rows being added above it? I am not really interested in redesigning the worksheet.
I need the macro to put Rec sheet data for each bank in Sheet Summary under the Each bank header, the macro needs to figure out the number of rows required and adjust if necessary as the rec data is not fixed. See below examples
Rec ABCDEFG5CategoryRecAccountValue DateEntry DateTypeAmount6CITIBANKCASH AMP1WTGDPFUSD1-Oct-075-Dec-08LDR-2,203.677CITIBANKCASH IMS02WTFPT2USD24-Jun-0817-Dec-08SCR2,001.388JP MORGANCASH DESWTFPT2USDSUSP15-Sep-084-Sep-09LDR-3,608.919RBSCASH DESWTFPT2USDSUSP15-Sep-084-Sep-09LCR3,889,582.9410BOACASH DESWTFPT2USDSUSP16-Sep-0829-May-09LDR-1,557,609.9511BOACASH IMS09ZPBRKQUSD17-Sep-0817-Dec-08LCR4,554,511.1112 13 14 Excel tables to the web >> Excel Jeanie HTML 4
Summary ABCDEF6CITIBANK 7RecAccountValue DateEntry DateTypeAmount8 9JP MORGAN 10RecAccountValue DateEntry DateTypeAmount11 12RBS 13RecAccountValue DateEntry DateTypeAmount14 15BOA 16RecAccountValue DateEntry DateTypeAmount17 18 19 20 21 22 Excel tables to the web >> Excel Jeanie HTML 4
Summary ABCDEF4 5 6CITIBANK 7RecAccountValue DateEntry DateTypeAmount8CASH AMP1WTGDPFUSD1-Oct-075-Dec-08LDR-2,203.679CASH IMS02WTFPT2USD24-Jun-0817-Dec-08SCR2,001.3810 11JP MORGAN 12RecAccountValue DateEntry DateTypeAmount13CASH DESWTFPT2USDSUSP15-Sep-084-Sep-09LDR-3,608.9114 15RBS 16RecAccountValue DateEntry DateTypeAmount17CASH DESWTFPT2USDSUSP3970640060LCR3,889,582.9418 19BOA 20RecAccountValue DateEntry DateTypeAmount21CASH DESWTFPT2USDSUSP3970739962LDR-1,557,609.9522CASH IMS09ZPBRKQUSD3970839799LCR4,554,511.1123 24 25 26 Excel tables to the web >> Excel Jeanie HTML 4
I have the following macro that imports data from several input files and rearranges it in a master file.I want to change it so that I can use it in each of the input files. Therefore, it should look in the input file for Spreadsheet "XYZ" and rearrange it in Spreadsheet "Data".
[Code].....
I have a spreadsheet set up for my son to roll up D&D characters. There's a combo box to select Race and another for Class of character. What I now need to do is automatically insert the correct set of racial and class bonuses into the relevant squares.
The Combo box points to a list of Races on Sheet 0, in cells A3 to A12. The cell reference for this returns a value between 1 and 10 depending on race selected, i.e. 'Dragonborn' yields a 1, and 'Tiefling' yields a 10. Cells H3 to H12 contain the Strength modifier for the different races.
When I select for example a Dragonborn (1) in the combo box, I want the Strength Modifier cell to pick up the value from cell H3, and if i change the selection to Tiefling (10) I want the strength modifier cell to pick up from (obviously) cell H12
How do I use a variable in the cell reference to adjust the cell data is retrieved from?
way to get Excel to automatically expand the cell height when the text becomes to long to display. Is there something I am missing that will accomplish this, or do I need to write some VBA that will make the cell larger once the character limit that will fit on one row is exceeded?
View 7 Replies View RelatedI frequently have to copy information from one excel sheet and paste it into another. But when I do this the formatting doesn't carry over. So I have to go to each individual cell that I pasted into and adjust the size of it. How do I copy and paste so that the formatting/cell size is preserved?
View 4 Replies View RelatedI have a spreadsheet at work . I am tracking the quality target for the department and I need to create a formula that adjust accordingly to the target set for month end which is 90%.
I have build in the foreacasted numbers for the whole month but I need the formula to indicate the minimum error points needed to achieve the target of 90% for month end when I replace the forecasted numbers on a daily basis with actual numbers.
I have attached the spreadsheet which will be clearer.
I have cell C5 value set as 5*B5+3. Now, using vba,I would like to adjust the value of B5 (basically find it), so that the value of C5 is let's say 8.
In this easy example, one can find rapidly the correct value of B5 to be 1.
What I've tried is the following code:
Private Sub CommandButton1_Click()
Range("B5").Value = -5
Do
Range("B5").Value = Range("B5").Value + 1
Loop Until Range("C5").Value = 8
End Sub
So, I am setting the initial guess for cell B5 and then increasing it by 1. However, if I set the increment to 0.5 instead of 1, program goes crazy and loop doesn't close.
What changes should I introduce to this code, so that even if the increment is 0.001 everything will work.
I have this nice macro that copies data from another workbook. I want to make it to also add the filename of the workbook (for example data.xlsx) it copies the data from, in a cell on the actual workbook (for example on sheet "Combined", Cell A10). What should I add to my macro?
View 3 Replies View RelatedI am trying to adjust the below macro so that it will work in Excel 2010.
Sub OpenAndProcess()
Dim fs As FileSearch
Dim I As Integer
[Code]....
I have the code below that is two separate activities and I want to change the second activity from a cell trigger (Set KeyCells = Range("K42:AD42")) to a button trigger. I need to first to remain unchanged.
I'd be ok if this was just one macro that I could assign to a button but because its two and I need to write the second's to clicking a button I'm over my head.
Its occured to me while writing this that because it'll be a range of buttons I'll probably need to make each one an individual code? Is this the case? If so I may have to just keep this as it is.
I would like to do some calculation with percentage as it showing in the attached file.
[url]
I have a vlookup into a row of cells. Sometimes the new text is so long it has to word wrap. How can I get the ros to expand with out me manually doing each row, and then go back to one line with the data changes.
View 9 Replies View RelatedI am new to Excel VBA - Normally, If u move your cursors to the corner of your userform,we will see the single-two pointed arrow pop-out and then we can adjust our own desired size of the user-form.
How to do that in EXCEL VBA?
In simplicity: We want to be able to enter a value in cell A1 (and hit enter), but we want the value in A1 to be increased by the value saved in H1.
So: if H1 had 250 saved in it, if we enter 150 in A1 (and hit enter), we want the value in A1 to become 400.
I have a piece of vba code that uses the date in column D to group by month and subtotal the amounts in column E.
The problem is the subtotal in column E doesnt adjust. So if a user of the spreadsheet adds or deletes rows the subtotals (and grand total) do not up date.
Dim LastRow As Long
Dim NextMonth As String
Dim R As Long
Dim Rng As Range
Dim SubAmount As Currency
Dim ThisMonth As String
Dim TotalAmount As Currency
Dim Wks As Worksheet
I have a vlookup into a row of cells. Sometimes the new text is so long it has to word wrap. How can I get the ros to expand with out me manually doing each row, and then go back to one line with the data changes.
View 9 Replies View Relatedattached a sample, with the page settings, print area set, row height, etc... all set to my needs. I would like vba to adjust the horizontal pagebreaks so that a "box" is never split into two parts. in attached sample the pagebreaks should therefore go below row30, row54, row124 and row128. Setting pagebreaks is easy, but still want VBA to do it for me.
View 6 Replies View RelatedHow to adjust the shape of the chart in the worksheet? I try to write the code, but can not work.
ActiveChart.Shapes.ScaleWidth 0.87, msoFalse, msoScaleFromBottomRight
ActiveChart.Shapes.ScaleHeight 1.5, msoFalse, msoScaleFromBottomRight
I currently have dates in a column in the format DD.MM.YYYY. I would like to convert this into MM/DD/YYYY, with the slashes instead of the periods.
View 9 Replies View RelatedI have a list in excel.......(see attached) In the attached you will see some empty boxes for this purpose I have only provided a dummy duplicate. In the original I have information in these boxes.
Problem:
When i go to print preview the list boxes fall half way down the page instead of at the top of the page......when I try to adjust the margins in print preview, manually, or either in page setup it doesn't rectify it.
I've used various "zoom" searches and can't find what I need. My users have different amounts of toolbars so the amount of screen space changes. I need to zoom so 4 graphs show. Am looking to enable the user with a scroll bar where the user could click / slide the control to change the zoom size on the screen so they can see the 4 graphs at their preferred "zoom".
I would prefer a control on the sheet and link it to a macro (or line of code if that's the proper phrase) and let the user decide (some people will want bigger zoom due to eyesight etc).
Private Sub ScrollBarZoom_Change()
ScrollBarZoom.Value = ActiveWindow.zoom.Value
End Sub
But I get Runtime error '424': Object required. Auto Merged Post Until 24 Hrs Passes;Nevermind. I found it. Answer was:
Private Sub ScrollBarZoom_Change()
ActiveWindow.zoom = ScrollBarZoom.Value
End Sub
I have this VBA (from a recorded macro) how can I amend it so that these two formulas change depending on the number of values....right now the range is to 129 only because we only have 129 cells of info but some sheets will have more some will have less. I want excel to base it based on the column I which used to be H.
VB:
Sub SLCREPORTDATA()
Cells.Select
Range("F1").Activate
Cells.EntireColumn.AutoFit
Columns("H:H").Select
[Code]....
In need of a formula to trim data:
Examples:
* 5 Extravagant Mak (nz) (100) Need the number plus a dot then space then the name, then minus everything after the name, so this would now read 5. Extravagant Mak thats 5dot space Extravagant Mak
*
11 Frisbee (100) This one would become 11. Frisbee thats 11dot space Frisbee