Resetting "end" Cell, Not Vba Related
Apr 19, 2007
I have a small worksheet and hit CTRL-END, the cell at the lower right-hand corner of the smallest rectangle bounding used cells is selected. This might be, say, C10.
Now suppose I put some data into the sheet, and accidentally put data into all available rows in a column. Now I hit CTRL-END and it takes me to C65536. Now I select those rows and Delete them, selecting the option "delete entire rows" when prompted. But CTRL-END still takes me to C65536. How do I reset the sheet to once again think that only the first few rows are used?
View 2 Replies
ADVERTISEMENT
Jun 13, 2014
VBA code in worksheet won't revert a changed cells color back to 0(white) after removing the comment.
Observations: code does set the desired cell colors to Yellow after inserting a comment, however removing said comment, and re-activating the worksheet does NOT reset the color after deactivating/re-activating the worksheet.
[Code] ......
View 2 Replies
View Related
Jun 23, 2014
I'm using Excel 2010. I have developed a calculator (attached) to make life easier for my colleagues. It works perfectly fine. But one thing keeps bugging me.
In Cell C3 - You have the option to choose "Daily" or "Weekly" (Drop Down). This selection effects your selection for Cell C6.
If you choose Daily in Cell C3 then your options for Cell C6 are restricted to select/enter an amount between £0.00 and £6.00.
If you choose Weekly in Cell C3 then your options for Cell C6 are restricted to select/enter either £0.00 or £12.00.
This works perfect.
However, if I select "Weekly" and then select " £12.00", all my results are perfect which is exactly what I want BUT if I then go back up to C3 and change "Weekly" to "Daily", the £12.00 in C6 remains (though the data validation for "Daily" is restricted to £0.00- £6.00) and all the results are consequently wrong until C6 is changed.
The obvious thing is to delete the £12.00 from Cell C6 or introduce a "clear" button BUT I don't want my colleagues to do this as it leaves the possibility of error and since we are dealing with money, I can't have it.
So what I would love and what would complete this calculator and deem it ready is if every time Cell C3 is selected (i.e. from the drop down list you choose Weekly or Daily) it as a result resets Cell C6 to £0.00.
Calculator 23-06-14.xlsx
View 6 Replies
View Related
Nov 20, 2011
In my excel workbook, I have a customer table and invoice sheet among many others.
As part of my system, there is functionality to grant new customers with a discount on the first purchase. Within my customer table, the last two columns are "Number of Purchases" and "Customer Type" (either single/multiple depending on no. of purchases), which are then used to determine whether the discount is valid or not on the invoice.
Once an invoice has been created, archived and refreshed with the customer selected (via a Customer ID), I would like some code to auto increment the number of purchases on the Customer table for that specific customer to +1.
For example, customer called Bob (Bob-1);
Number of purchases = 1
Customer Type = Single
Discount = Yes
Invoice then created for Bob (using Bob-1 as the unique value), sent off and refreshed. New figures should be:
Number of purchases = 2
Customer Type = Multiple (can be achieved by using IF statement on No. of purchases)
Discount = No
View 1 Replies
View Related
Jul 14, 2009
in this sheet i have 3 groups of data. group 3 is the sum of group 2 - group 1.
how can i make the cells highlight that relate to the cell sum in group 3.
i.e when i click cell b38 cell b21 and b4 should highlight.
View 9 Replies
View Related
Apr 30, 2014
See the attached : ToolMatrix4.xlsm
Combobox2 filters listbox2 which is pulled from sheet2. How do I get the X to return the value to the relevant cell on sheet 2, ie create a relevant listindex for the listbox
Also, one other minor thing, why selecting the last record in the listbox doesnt write to the worksheet?
View 4 Replies
View Related
Mar 25, 2012
I am trying to put a message box related to a cell if a threshold values is reached.
E.g.: Cell A: 85
If cell A values moves to 86 I am trying to put in a message that says something like " This value is not allowed". Is there a way to do this ?
View 8 Replies
View Related
Dec 9, 2012
I have worksheet with 5000 rows data's including the comments in one coloumn.My problem is when I edit the comments,the comment box shows somewhere else is not showing to near the related cell.I am using excel 2007,its happen after the upgradation of 2003 to 2007.
View 2 Replies
View Related
Dec 2, 2009
I have a spreadsheet with my Periods along row 10. e.g. C10: "1", D10: "2", E10 "3", F10: "4", G10: "5" etc. (green on the attached sheet). I have my departments along column B, e.g. B11: "Baked" B12: "Fresh" B13: "Frozen" (yellow on the attached sheet)
what I need and cannot work out is some VBA code that will populate two variables (lets call them Period & Department) when I click on one of the figures. For example if I click on cell: if I click E14: Period would have the contents of cell E10, and Department the contents of cell B14.
if i click G14: Period would have the contents of G10, and Department the contents of cell B14 again. I know how to get the click on the cell to work properly etc, and I have code to slot these variables into that works very nicely, I just can't get this bit to work!!!!
View 2 Replies
View Related
May 4, 2006
What I am trying to do here is to compare 2 lists in 2 different files, and when there is a match, then copy and paste the related cells of the matching name. Sorry if this sounds messy, perhaps the sample file I have attached can explain better.
Every month I get a new file in the format of “Data Source” sheet where the list of banks in column A and the figures in column M, AA and AB might change from month to month. For the sake of convenience, I put the source data as a different sheet instead of different file here.
I have an existing report template in the format of “Final report” sheet where basically I copy and paste the relevant cells according to the name of the banks.
I don’t think I can use Vlookup because the cells that I want to extract are not right beside the search criteria. If I’m wrong please correct me.
Anyway, assuming a macro is needed for this, I am wondering if I can create a macro, where it can search the list of banks in column A in “Data Source” sheet based on the list in column A in “Final Report” sheet, then copy the correct cells from column M, AA and AB and then paste them into the correct cells in columns B, E and H in “Final Report” worksheet?
Note that not all the banks in the “Final Report” sheet are in the “Data Source”, so for this example, row 4 for ABN Bank should remain blank after the search because it is not listed in the “Data Source”. The Data Source List might also change over time.
There is also this problem of the bank names from the “Data Source” sheet not being exactly the same as the existing list in “Final Report”. For example in this file, ANZ Bank in the other sheet have all the extra stuff behind, but we know it is the same bank.
View 9 Replies
View Related
May 29, 2009
my excel is no longer showing any tool bars and I can not get them back, is there any short cuts to bring back the menus? I can’t even do a right mouse click on the sells for some reason. The only tool bar that is showing is the formula bar.
View 10 Replies
View Related
Aug 13, 2009
Code runs rather slow. Is there a better way to achieve the same results?
View 5 Replies
View Related
Nov 1, 2009
If I change the country in cell B2, I would like to have that the cells D2, E2 and F2 show the same name as cell R2. (This is always the first option of the drop menu's)
Cells D2, E2 and F2 are made by using the validate option in order to get drop down menu's.
The source information is in the hidden columns I-Z.
So for example:
if I change cell B2 to Belgium, I would like to see that D2, E2 and F2 also show Belgium immediately. I can then select the place I want to move from Belgium to by using the drop menu in D2 (for exampla Belarus).
As I selected for D2 Belarus, this has to be shown in E2 and F2 also.
I can now select E2 (for example Austria).
If Austria is selected in E2, I want to see this also in F2.
To recapitulate:
If I select Belgium as starting place, I want to see in movement 1,2 and 3 also Belgium, as there hasn't been anymovement yet.
If I let my army go from Belgium to Belarus, I want to see for the two remaining movements Belarus (as this means that the army remains in Belarus)
If I let my army go forward after Belarus to Austria, I want to see in the last movement phase Austria (no movement). If I still want my army to move I can still adapt it.
View 2 Replies
View Related
Apr 12, 2012
I have many spreadsheets that use shapes such as arrows and other symbols. The process for these spreadsheets is to move the shape (arrow for Ex) to indicate a specific location of pain or something like that.
Once the arrow is in position, the spreadsheet is printed to PDF and and a snapshot (in adobe) is "taken" and the picture (with the arrow) is pasted into our application.
My questions is how can I move the shapes back into position when you want to do it all over again.
When I have a few shapes I use
PHP Code:
Set sShape = ActiveSheet.Shapes("Right Arrow 6") 2
    With sShape
       .Top = 220
       .Left = 91
       .Rotation = 20
   End WithÂ
For each shape
But if I have lots of shapes, 50+, it's a bit more cumbersome.
Can I loop through the shapes when I enable the macros to pick up the current positions and use that information when I do a reset (loop back again to move any shape that were touched back to where it was when the spreadsheet was opened?
Currently I have them close the spreadsheet without saving and just reopen.
View 3 Replies
View Related
Mar 25, 2005
I have a spreadsheet to keep track of purchase orders, and I use groups of option buttons to run macros to filter the info by category using auto filters. Not sure if I'm explaining this well, but as an example, I have one set of option buttons for whether the items are late, and one for whether I have flagged the order for some reason, and a couple of others. It does everything I want, basically.
I have a button which resets all the autofilters in one go, but the problem is it doesn't put the option buttons back to the "Show all" option for their categories. I tried recording a macro and clicking in the "Show all" option so I could look in the code to see how it refers to it, but it just says to run the macro, nothing about selecting "Show all".
Also, the way I reset all the autofilters is to have a macro that goes to every autofilter in turn and selects "(All)". Is there a better way of doing this?
View 9 Replies
View Related
Feb 25, 2013
I am creating scorecards for our company and ratings are inputted using radio buttons (form control). I have a "clear" macro that clears all fields and radio button values (none is ticket). This macro is called throughout different sheets, as different roles in the company have different KPIs and should be on different sheets. I need a code that detects all radio buttons within a certain sheet and clear their values. I cannot put specific radio button names, as I have a lot.
View 2 Replies
View Related
Feb 26, 2008
I have a column (L8 down) of cells formatted as $'s
At the end of the column is a sum formula.
At the end of each month the client wants to 'reset' all the $ amounts back to $0.00
Is there a macro that would reset the column of $'s back to $0.00 without removing the formulas like ClearContents does?
View 9 Replies
View Related
Dec 19, 2011
When I open Excel 2010, the formula bar appears about 6 times as tall as normal. I have not been able to find any way to reset it to normal size.
View 11 Replies
View Related
May 24, 2013
I have a spreadsheet with many rows of data. I want to be able to see the last month or so of data (each day is one row), but I do not want to have to scroll to the bottom of the worksheet to see the recent data, so I want to reverse the data - I want Row 1 to be my column headers, and for every new day I want to insert a new row at Row 2 and add my data. However, when I do that, my conditional formatting gets all messed up. Once the new row is added, the formatting rule changes its range to only those rows which had data in them before I did the insert, each new row gets a separate but identical rule that applies only to itself, and the headers row gets its own rule.
That seems incredibly stupid to me. Is there a way I prevent the formatting rules from changing and just stay the way they are which applies to the entire column?
View 5 Replies
View Related
Jun 9, 2009
Is there a way for me to have combo boxes, labels, and command buttons to be moved to a set position rather then just moving by intervals? What I need this for is every time one of my worksheets opens it refreshes the data and does this via lookup formulas and a macro that searches for values and deletes the rows with the matching values, it also adds rows to the top after it is done deleting. This causes all my controls to move randomly and I need some code to reset them to their original sizes and positions after the code that adds and deletes rows is done running.
View 2 Replies
View Related
Apr 1, 2008
Every time I re-define a named range, either through the UI or via macro, the permissions I had set for that range are seeminly deleted from the allowEditRanges collection. (When I go to view them under tools>protection>allow users to edit ranges>users allowed to make changes without password ends up blank)
I would like to re-define the range in a VB macro without this undesired effect. Alternatively, a script that reads the permissions for that range before my code and writes those same permissions after would do fine.
View 5 Replies
View Related
Feb 15, 2007
I've recorded a macro that clears a worksheet, fetches data from an Access Table and then creates a new pivot table. The Pivot Table Fields are summarized by "sum" and this worked the first few cycles for the macro. Now it is returning the Pivot Table Fields summarized by "count". Can an option be inserted into the existing code to specify "sum"? Here is the
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"'raw data'!R1C1:R205C12").CreatePivotTable TableDestination:="", TableName _
:="PivotTable14"
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet. Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable14").SmallGrid = False
ActiveSheet.PivotTables("PivotTable14").AddFields RowFields:=Array("Name", _
"FIELD_ASM_USER_NAME", "Data")
With ActiveSheet.PivotTables("PivotTable14").PivotFields( _
"SumOfSumOfSumOfCYYTD_SHARE_QTY")
.Orientation = xlDataField
.Position = 1
End With..................................
View 2 Replies
View Related
Jul 30, 2014
I have a situation where I have to curve fit data, this can lead to different formulas being used with varying constants.
Is it possible to pickup a TEXT based formula and related constants from other cells, and then place this into another cell as a functioning formula. For Example
Cell A1 contains the formula as a text string whether it be y=a+bx+cx^2, or y=a+b/x, etc
Cells A2:A6 contain the individual constants, a, b, c, etc
I would then want the VBA to read the text based formula and put it into an output cell as a functioning excel formulae.g
In cell B10: =a+b*A10+c*A10^2
I understand picking the constants up and putting the formula should not be too much of an issue, however trying to insert the variable form of the curve fit is the part that I am struggling with, and am unsure if possible.
View 2 Replies
View Related
May 10, 2013
I have a task to rank the stocks from the highest price to the lowest price. I've made a template and managed to get the ranking right. I used the LARGE() function.
But now the difficulty is to get the 'Stock name' related to the ranked price. I may do it with the MATCH() function. But the problem is there is an equal number 15%.
Can you have a look at the example attached here?
View 5 Replies
View Related
Jul 18, 2006
I've tried LOOKUP, HLOOKUP, VLOOKUP, MATCH, SUMIF, ETC... Nothing working correctly. Here's what I need the function/formula to do: Find a # number value in column C, and then whatever text in another column ( range) I specify (like column A) corresponds with that same ROW. For example: VALUE: $40.00
1. Search column C3:C40 to look for $40.00- let's say it finds the value $40.00 in cell C21.
2. In cell A21 there is text "Gena's Sales".
I want the new cell the return a value of "Gena's Sales" because the $40.00 is in the same ROW as "Gena's Sales."
I think the reason I'm having such a problem is because it's numbers and text combined, and also LOOKUP does this "ascending order" thing that screws up the output. I'm getting values with LOOKUP from column A, just not the right ones. I don't need any ascending or descending orders. I just need to find the value in a range, then find the text in another range in the same ROW.
View 2 Replies
View Related
Aug 26, 2008
I have a vehicle intake-form in which users have to enter vehicle data.
I also have a huge list containing make/model information.
I would like to be able to use this database in the following way : I want the user to select the make in cell A1 , and then the model in cell B1. It must be possible to choose for example the make "BMW" in cell A1 and then cell B1 should only display the BMW models, and not all others that are present in the list...
I tried to use Data Validation with a list of choices, this works fine for the make (A1) ; but how do I make the list used for Data Validation on B1 dynamically related to the value in cell A1 !
View 7 Replies
View Related
Aug 12, 2006
is it possible to set up a form (or any method) that would let me input a name (from my workbook) that would then "pull" up details about that name. I am thinking of making a staff holiday planner. This would have a column of names, holiday entitlement,days brought forward etc and columns that have a year (daily) calendar.
View 2 Replies
View Related
Jun 23, 2007
I need the listbox to have the related dates to the salesman I am selecting in Combox Not all dates
You can check my Attachment
View 4 Replies
View Related
Jul 30, 2013
I have a table in excel which looks something like this:
Product Ref
Date
Description
1
29/05/2013 19:58:50
Product A
[Code] ....
All I'm trying to do is get the only the first dates for each of the related Productsrefs. So the result should look like this:
Product Ref
Date
Description
1
28/05/2013 19:26:48
Product A
[Code] ....
Is there a built in function in excel that can achieve this?
View 3 Replies
View Related
Apr 16, 2014
I have a table in format below, I want show a third column with sum of w.r.t the minute, so for time 2:10 ,
i want to show 10 which is sum of first four values.
Started DB request
2:10:00 2
2:10:11 3
2:10:11 4
2:10:13 1
2:11:10 3
2:11:11 2
2:11:12 1
2:12:10 1
2:12:12 2
2:12:12 2
2:12:13 1
2:13:11 1
2:13:12 1
View 4 Replies
View Related
