Scroll Bar With Mid Point Of 0 And Positive Increasing Values On Both Ends
Mar 28, 2014
I've got an excel sheet that lets people choose between 2 choices. I'm using scroll bars to make it easy for them to choose and visualize their preference with as little mouse clicks as possible. The scroll bar is only one sided though, meaning it starts from a min value to a max value.
I would like a scroll bar with a of 0, with the value increasing positively as the user scrolls to the left, and the value also increasing positively as it scrolls to the right.
way to insert a Line where the (x) line on a chart is breakeven or the instance where it changes from positive to negative. I am doing this now by just drawing a line, however with a chart that is constantly updating the line does not adjust to this.
I have a User Form with 2 groups of 3 radio buttons let's say. The user must choose one button from each group, then press "GO" which runs specific code depending on what buttons they pressed.
How do I make the variables public or static so that after both radio buttons were selected, another seperate procedure in the same module (The GO procedure) can know what selections were made so it can run code based on those selections? In this example, there are only 2 groups of three radio buttons, but in reality I have 30 buttons and it keeps growing so I need the most direct/simple way to solve this.
For Example:
UserForm1 Radio Button Group 1:
Button 1 Button 2 Button 3
Same UserForm1 but now here is Radio Button Group 2:
Button 4 Button 5 Button 6
I need to know which buttons the user clicked from Group 1 and Group 2 after they finished both their selections from each group, and then run code depending on what combination of buttons have been selected. I'm good to go with the If/Then code to decide which code to run, I just cant keep the variables from reading as 'Empty' when I try to see what the user selected when pressing GO and running that procedure.
I have to find the union of 2 columns in excel and club those 2 columns into a single column with values in the increasing order.eg: column 1- 0 2 4.. , column 2- 1,3,5.. final result in column 3 should be 0 1 2 3 4 5...plz let me know the code for this that i can run in VB editor(- excel 2007)
I want cell D5 to be the minimum value of cells D29:T29. Some of the cells will have no value (or $0.00) and some will have values in them. I tried the following formula in cell D5 but it doesn't work: =IF(D29:T29>0,MIN(D29:T29))).
Using AVERAGEIF on a row containing both positive and negative values one can easily calculate the Average of only the positive or negative values. I need to filter the 20% outlier on each case, how can I do that with TRIMMEAN ?
This is an example of the current formula using Averageif for the positive values:
I have two columns of data, which are constantly being added to. Column A is a unique name (no duplicates) and column B is its corresponding value. In many instances the value in column B is zero. I want to produce a list in column C with the names that have a positive value which will update automatically as names and values are added to column A and B. Is this possible?
Based on filtered data I want to produce a result for the Average trade gain.
Lets say I have 100 rows of data, and based on filtered settings the rows reduce to 5 rows of results.
Of those results I want to find an average of all the positive values (trading profits) and ignore the negative ones.
$2000 $1500 -$700 $4000 -$1000
The answer should be (2000+1500+4000)/3
The need for filtering makes this problematic. I am inclined to use SUBTOTAL as it applies to filters rows only, but I can't see how to use an IF statement within it.
In addition to this I would also love to know how to subtotal all positive values without averaging.
I'm trying to define a range of values in two cells M1:M2; lookup that range of values in a three column array (K12:M600) and sum the values in column M12:M600 if they are positive values. I've been trying this formula:
I've got 12 months worth of business accounts which have been downloaded onto a single spreadsheet.
These show negative and positive values. As I only want to work on the negative figs at the moment, how do I highlight the colour of the positive value cells or font to prevent any mistakes ?
I have a sheet which contains credits and debits for several accounts:
Customer Amount
ABC 2000
XYZ 3000
XYZ 1000
ABC 1000
ABC -5000
XYZ 1000
What I need to do is match negative and positive values within each account and highlight them. I have been able to put together a VBA code from google and other forums to match within all rows.
Code: Sub matchdata() On Error Resume Next
For Each cl In Columns(10).SpecialCells(2, 1) Err.Clear
[Code] ........
1. I need to adapt the code so as to do matching within accounts. Example within ABC and XYZ separately.There will be 100s of accounts. 2. If possible, I need to offset a credit (negative value) with multiple debits. Example: ABC -5000 would offset ABC 2000 and ABC 1000. I am not worried about the remaining balance.
I use data on a text file and import this into Excel and use a macro to format. The data lists invoices and credit notes and their dollar value.
Due to a fault in the program that creates the text file all dollar amounts are shown as positive figures. Credit note amounts should correctly be shown as negative figures.
The invoice number format is 8 characters beginning with a letter and credit notes begin with a number.
Is there macro code that will change credit note values to a negative figure?
I am trying to find a formula where I can identify where in row two the value chages from positive to negative, and where it changes from positive to negative it should return the date the value changes from positive to negative ie:
VBA code to change a cells value in a column and the adjacent column to the right of it. If the column has a negative number in it, I need to put the absolute value of that number into the next cell over on the right. I then need to change the original column values to 0. I've added a spreadsheet that shows an example and the desired output.
I'm looking to change raw data into a "point" system. One of my value exchanges is every $1,000 equals 5 points. So if cell A1 equals $4,685 I am looking for cell A2 to give me a value of 20. (5pts times 4 increments of a $1k) The point value only changes at the $1k milestones, so the remaining $685 does not change the point value. What would the formula look like for something like this?
I have a pivot table as shown below which is pretty straight forward however I am trying to create a macro that will automatically change the point name to match the point name in column E then copy that resulting dispaly to another sheet then pick the next point name in line and do the same thing and repeat for 50 rows, so my end result will be 50 pivot tables ready for printing. I can do this manually but I am trying to make it automatic...BTW the point names in column E change everyday but the pivot table supports the name changes.
Option Explicit Sub PointName() Dim Ws As Worksheet Dim Rng As Range, Cel As Range
Set Ws = ActiveSheet Set Rng = Range(Cells(2, 7), Cells(Rows.Count, 7).End(xlUp))
For Each Cel In Rng Ws.PivotTables("PivotTable1").PivotFields("Point Name").CurrentPage = Cel & " "
Ws.Columns("A:B").Copy Sheets.Add With ActiveSheet .Paste .Name = Trim(Cel) .Range("A1").Select End With Next Ws.Activate End Sub
I turned it off for a while and when I turned it back on I am getting an error Unable to set the _Default property of the PivotItem class
Debugger is highlighting
Ws.PivotTables("PivotTable1").PivotFields("Point Name").CurrentPage = Cel & " "
I'm trying to count the number of times a payment is made within a certain set of dates. I believe that I should be using a countifs function, but I'm not exactly sure how to program it. I have a number of months, say the last 12. And in each month a payment is made or it isn't made.
I want to compare 2 columns based on their positive and negative values..
For example,
A B C ID1 91 -7 ID2 -7 74 ID3 -4 -5 ID4 90 4 ID5 -55 34 ID6 33 3
I want to know 1) which id's have both negative values in both columns B & C, 2) which id's have both positive values in both columns B & C, and which id's have opposite signs in both columns B & C.
EXCEL 2010. I have a column of negative and positive times (eg. -00:52, -03:07, 06:02) in custom hh:mm format, and would like a new column rounding these times to the nearest negative or positive hour (with just the whole hours displayed), so I need a formula that would give me (for this example) -1, -3, 6, etc.
how to link axis properties with a scroll bar (two scroll bars).
The idea is to be able to modify the maximum and minimum values for the axis in case there are outliers which might cause problems by analyzing the chart.
The Guideline in the scroll bar should be set to its maximum (all the way up) by Default, meaning that if there chart behaves itself well there is no need to change its axis values.
The same applies for the minimum guideline of the scrollbar but it should be sent all the way down by Default.
I have a section of my program that asks for board scores (point value is 6-10 in increments of .5) I have set up a data validation so that users can only enter those specific values. Problem is, none of the values can be more than 1 point from each other. (the part I have yet to figure out)
Example, if the first value is a 6.5 the second is 7.0 and the third is a 9.0, I need the data validation to display an error because the 9.0 is beyond the one point spread.
Summary: Display an error message if any of the points are more than 1.0 point from another.
Basically, I have a Userform and am trying to have a user enter 1 datapoint into a Textbox. Based upon that datapoint (it's 9 digits), I am trying to see if I can therefore reference a linked Access Table to fill in other textboxes with information tied to that record (so if they enter a unqiue ID, I want to automatically return Name of Person, Location, etc.) . I liken it to a vlookup in regular Excel but am just not sure how to go about it in VBA.
I'm trying to create a macro that when run, scans Row 12 (only after column E), Finds the first empty cell, then inserts the cell value from Sheet4 CellE8. Then the hard bit begins. I need it to insert cell info in all the cells below it, from different locations...
For example
A B C D
[Code].....
In the above sheet, I need it to go to cell D2 and insert the values from Sheet4 CellE8, Then proceed to D3 and insert data from Sheet3 D4, then to cell D5 and insert data from Sheet1 A7, etc etc
I dont mind doing each cell individually, but they will always be in the same column (and row 2 "Value" will always be the one that determines the next empty column).
I am using the toolbox/ properties method to restrict the scroll area in the sheets of my workbook. However, for some sheets when i keep the right hand arrow pressed, the screen will not scroll to the last column. (i.e. the screen display will not move with the selected cell box)
In the menu via TOOLS-->OPTIONS-->VIEW I am trying to remove the scroll bars from just one worksheet but when unticked, this option affects all of the worksheets in my workbook. Is there a way to just nail this selection down to one sheet via properties?
I am having a with a controlled loop. If the loop is on the last pass and an error occurs, it goes into a never ending loop. Once the error portion of code is excuted, the code resumes to the same line. I'm not sure how to solve this problem. I have attached a sample file with all code if needed.
Copy the correlating Row from (Worksheet DataDump Column AJ) to (Worksheet Calc Column A) IF Worksheet DataDump Column B ends in 3
Here is what I got so far..
Code: Sub copyWorkPackages() Dim WPThree As Range For Each WPThree In Range("DataDump!B2:B" & Range("DataDump!B" & Rows.Count).End(xlUp).Row) If Right(Range("DataDump!B2:B" & WPThree.Row), 1) = 3 Then End Sub
