I want to have the macro delete the color applied once a new "Row" is selected!
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim RR As Range
Set RR = Target
Cells(RR.Row, 2).Interior.ColorIndex = 36
Cells(RR.Row, 3).Interior.ColorIndex = 36
Cells(RR.Row, 4).Interior.ColorIndex = 36
End Sub
I can select an individual cell or range, select Format->Cells->" date" format, hit okay, and nothing actually changes unless I click on a cell so that it brings up the edit cursor. I can then hit enter (not changing the content of the cell in any way) and then Excel recognizes it as a date. The cell values are all in the standard "3/14/2001" format, with the exception that single digits are 0-padded, and there are no spaces preceding or following the date string. The issue here is that without the cells being recognized as actual dates, they cannot be properly sorted, eg: the original range:
01/04/2005
01/01/2005
01/02/2004
01/03/2005
is sorted, incorrectly, to:
01/01/2005
01/02/2004
01/03/2005
01/04/2005
...obviously for ascending order, the single 2004 date should be placed at top. Any ideas other than a custom date-text parsing macro? I could do that, but the size of each sheet, the number of sheets per batch, and the frequency of individual batches makes that idea completely impractical. ...Technically, my macro couldn't sort the above ranges correctly, but on further investigation, I noticed I couldn't do it manually either.
I have a thoroughly outdated system to keep a log of call centre stats. In order to facilitate a marginally easier workflow, I have rearranged a few things so that a user simply has to copy and paste data from reports into the worksheet.
The worksheet is set up with all the number formats like time and percentage which are extremely important in totaling and averaging hours and availabilities, however the pasted data doesn't match the destination format and it therefore affects the total.
I have tried many different special pasting options to no avail.
The only way i seem to be able to do it is to go into each line and retype my percentages (as 100 becomes 10000% ) and click the tick next to the formula bar so 00:00:16 is calculated as a quantity of time instead of text. With 30 people and 15 stats each day that exercise becomes a little bit tedious and counterproductive.
We have a macro that formats a report from our accounting program quite extensively (mostly moving text around). The last step is to select the entire worksheet and set the cell format to a custom setting #,##0.00_);(#,##0.00). We haven't been able to include this in the macro - we end up manually doing this step after the macro has completed. Is there any way to set this custom format as part of the macro?
View 2 Replies View RelatedUntil recently I successfully used this code to navigate to a web page and populate some of it's boxes, before programatically pressing the page's "Validate" button, whereupon the page would test the data I'd populated the boxes with, and come back with an answer to tell me if there were any errors in my data:
Private Sub CommandButton1_Click()
Dim uf1_cbutt1_click_ie As Object
Set uf1_cbutt1_click_ie = CreateObject("InternetExplorer.Application")
Can anyone shed any light on how I may be able to programatically navigate with VBA to the page I want? I'm also having trouble finding the new name of the "Validate" button - and how to programatically click it.
I wanted to disable the function "Delete" when right-clicking on worksheets.
I copy paste the following VBA code in every sheet code :
I have a workbook that contains a worksheet with commission data with a validation list of about 50 items. Each of these items represents a sales peron within my organization. Every month I select each of these items or sales people from the validation list (using the drop down box), which changes the commission data for each rep on the tab, then convert this to pdf to create and send each of these reps a commission statement.
is there a shortcut for navigating between 2 worksheets in the same workbook...
for eg if i am copying some values from sheet 1 into sheet 2 of the same workbook...wat i am doing now is clicking sheet1 copying the value then clicking sheet2 and pasting the value in the cell i want.. i want some shortcut ( if its there ) like ALT+TAB which is for navigating between different open windows.
I have a Sheet ( Named "Summary" for Example ) of about 4,000 Rows that has a LOT of Conditional Formatting.
I Added Another 100 Rows this Morning and when I Tried Saving it a Message Saying that Not All the Formatting for the New Data that had Been Added had Been Saved. Is there a Macro or Something I can Run that will Make the Conditionally Formatted Cells Stay the Colour that they are but Delete the Conditional Formatting Part of it Achieved Using "Format" & "Conditional Format" from the Menu Please.
Ideally I would like to be Able to Enter the Number of Rows ( From Row ? to Row ? ) that I want this to Apply to.
Imagine I have a cell with validation and I need to add to my list. My list is Cat, Dog, Frog, Parrot and I need to add Elephant. So I tab down to Source, I want to right arrow to after Dog and type Elephant, but as soon as I hit the right arrow key it replaces the full list with an absolute reference to the selected cell. Is there any way I can get this to not happen so I can instead use the arrow keys to edit the text in the same manner as when composing this text.
View 2 Replies View RelatedClicking on the below link will take you to a results screen, however I need to click on the button or image with the word Races on it. I've tried to use getelements, but I can't get the equivalent of clicking on the button.
[URL]
I want to be able to select a button called 'GO' on the following website http://www.xe.com/ict/. I have the following code.
On Error GoTo errHandler
Dim IE As Object
Set IE = CreateObject("InternetExplorer.Application")
IE.Navigate "http://www.xe.com/ict"
IE.Visible = True
While IE.Busy
DoEvents
Wend
IE.Document.all("Go").Item(0).Click
oSubmit.Click
errHandler
ie.Quit: Set ie = Nothing
I cannot acheive to have a {Sum(if(...)} formula applied to whole columns. See below.
The following formula return a numeric value. So it works.
{=SUM(IF(Query!$L1:$L65535=TEXT($A37,"@"),Query!$AM1:$AM65535,))}
(confirmed with Ctrl+Shift+Enter)
The following formula returns #NUM. So it fails.
{=SUM(IF(Query!L:L=TEXT($A37,"@"),Query!AM:AM,))}
(confirmed with Ctrl+Shift+Enter)
how to use {Sum(if(... )} to whole columns?
I have a very simple workbook in Excel 2000 with one page containing all my data which I update on a daily basis. Some of the data is linked to another workbook so that it updates each time I open it. Then I have 12 tabs, one worksheet for each month of the year with 10 simple graphs on each worksheet.
As you can see, it is a very simple worksheet. Each month, I copy and paste the graphs from the previous month into the current month and simply alter the Source Data in the graph to gather the current month's data. At the start of this month, it allowed me to copy and paste, but when I tried to alter the Source Data to the current month, I got a window on screen with a yellow triangle containing an exclamation mark and the words "No new fonts may be applied to this worksheet". If I click the OK button or the cross, it responds but the whole programme is locked. The only way I can get out is to give the 3 fingered salute and close Excel down.
I have a workbook with 33 worksheets and more than 150 charts. Now, when I right-click on a chart to access "Source Data" for example, I get a message saying "No more new fonts may be applied in this workbook". Then I have to click "OK" about a dozen times before I get access to the dialog box. Curiously, it happens for some charts and not for some others.
View 7 Replies View RelatedIf filtering column C, just showing 'A's, by pressing the add row button, I want to copy the last cell in column C and pasting it in row 73, since it's the next unused cell when the worksheet is unfiltered.
View 2 Replies View RelatedI have a auto filtered applied to 1000 rows of data and the actual quantity of rows I currently see is 55 rows. When I do a filldown command to apply this formula, "=CONCATENATE(X$1,I51)" the result I get is wrong becuase where the "I51" value is at the formula is picking up cell values that are hidden due to the applied autofilter.
What do I need to add to this formula so that it only picks up cell values that are not suppressed by the autofilter? I want it to disregard the hidden rows when I do a filldown and only apply the formula to the rows that are not hidden due to the applied autofilter.
How would I write a formula if I was trying to get the cell to count all expired certifications (based on a range of cells and their expiration dates) as well as all blank cells? This is what I have so far and all it's doing is displaying "FALSE"... =COUNTIF(D2:D95,"
View 5 Replies View Relatedin this example below i need to work out what the divsor would be for values inbetween the data?
View 9 Replies View RelatedI have a spreadsheet that I want to sum the data similar to SUMIF or SUBTOTAL after a filter is applied. So I have data in column "F" that I appliy a filter and based on that filter I want to sum only the filtered totals in column H. Is there an easy way or is creating a new function the only way to go?
View 9 Replies View RelatedI have the following code (below) which calls a function to return the sum of values in a specific part of the array.
I haven't set the function up correctly as it is causing an object not defined error(424), but I can't see where and have been going round the houses changing it. I'm wondering if it's my confusion over when to use By Val?
Sub getDataIntoArray()
Dim arrDataTot()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
On Error Resume Next
ReDim arrDataTot(1 To 1)
I have a worksheet that I'm trying to rank program selling. The range has subtotals that I need to exclude, so I'm trying to rank over multiple ranges. I've thought about LARGE in an array function, but can't seem to make that work. Is SUMPRODUCT a possibility? I've attached the worksheet, trying to get the top programs (again excluding the subtotals in bold).
View 4 Replies View RelatedI have a problem in applying Data Validation on cells containing Formula. Eg: cell A1 has a value 10 and cell B1 has a value 15. i have a formula "=A1+B1" in cell C1. If i apply a Data validation to the cell C1 so as to accept values only in the range of 0 to 10, the cell C1 is still accepting values grater than 10 when i change the values in the cells A1 and B1.
View 2 Replies View RelatedI've got a report which is approx 40,000 rows long and I need to delete out alot of the info.
I need to delete out entire rows if column C contains a 0.
I've written a loop to do this but as theres so much data it take a long time to run.
Is there a smarter way to do this?
I have a list of 400 numbers. I wish to filter down the list to those 500 and then of that list get the 95 percentile.
I have tried
=IF('GRML ECP 01'!O$2:O$584 500 I get a blank. When I run the function manually on the numbers > 500 I get 1800.
I have applied conditional formatting to a range =$F$1:$J$44 based on the contents of a single cell =$L$44="No"
Now, I want to copy the whole range, including L44 to create another range controlled by L88, for example, but the $ signs are preventing that with the new range still being controlled by L44.
Is there a way to easily copy this whole range, including the CF, as I need to copy it many times down the page and I don't want to have to edit the "L44" cell each time.
Sheet one contains a table of table from C6:AY7000
On Sheet 2, I want to create a summary of data from Sheet 1 based on 2 Criteria which applies to column I and N.
On Sheet 1, I have put in a formula to link to sheet 2 for the criteria to make things easier.
So BN3 = Sheet2C3
And BN4= Sheet2C4
I have managed to run my coding to filter the data that I correctly want to copy and paste onto sheet 2 - however I only want to copy specific columns.
This is the coding I have written to select and filter the correct data values.
Sub AddFilter()
'
' AddFilter Macro
'
Dim rCrit1 As Range, rCrit2 As Range
With Application
.EnableEvents = False
.ScreenUpdating = False
[Code] .......
This now gives me the correct data in the table.
I want to copy the visible cells from this table but only for columns e, dr, r, v, w, o, z, AD, AG, AQ, AW, AY
The copied data will then be pasted into sheet 2 starting in cells B9
I plan to attach a button on sheet 2, so when the user updates the values in C3 and C4, this will update values in BN3 and BN4 on sheet1, they can run my macro which will go to sheet 1, filter the data on what they have entered and paste the correct values.
I want to take the value in I and apply a 10% discount to it and place the new value in column M. Would it be possible to have this new value rounded up or down to the nearest 9.
Example:
Column I=7.99
Column M=7.19
Column I=7.59
Column M=6.83 (however I would need it to be rounded to the nearest 9. The price I would need would be 6.79)
Column I=2.19
Column M=1.97 (however I would need it to be rounded to the nearest 9. The price I would need would be 1.99)
Im having some issues with some code Im working on. My goal is to delete the rows that do not have conditions met. Conditional formatting is used in 6 columns of my spreadsheet and the cell fill is to turn red when below .50 . This code is shown below. What I need to do now is look at the 6 columns where the conditions are applied, and delete any rows where the column cells are not red. To further clarify, if in row 1 column X:X does not meet criteria, but column AL:AL does, the row would not be deleted. However if none of the columns meet the criteria, the entire row is deleted.
View 6 Replies View RelatedI'm trying to apply the code for accumulating value in a single cell to a range of cells.
I want to use the code found in this thread [URL] ..... but, it only lets me edit "B1" to another single cell. I need my cells G4 to G469 to operate the same way.
How to edit the code so I can include a range or show me a version of the code where I can enter my desired range?