I have Cloumn A with names of staff going down a2, a3, a4 etc
In Row 1 I have dates running from left to right
Within the table itself I insert a 1 when an agent is late for their shift
How do I get cloumn B to look across the top of row 1 and count 4 weeks before Todays date and use that as its range.
I'm sorry I can't post the sheet from work PC - No Colo on here
About half of the time when I move the contents of a cell I get the message "The Clipboard cannot be emptied. Another program might be using the Clipboard"
View 14 Replies View RelatedI have attached a workbook stating my problem.
file1.xlsx‎
Is there a way to write VBA code that can reference a range of data that may be moved on the spreadsheet by the User? For example, if I write VBA code to reference a range of data such as:
View 2 Replies View RelatedAt the moment I have the below formula that retrieves a value from a separate sheet (the INDIRECT part of the formula determines the sheet to be looked at, as there are numerous and they are dependent upon what is displayed in two drop down lists in cells B3 & E3).
The problem is that the ranges F6:F30000, AJ6:AJ30000 and AA6:AA30000 are not always consistently in the same column position each and every time I receive a refresh of the report. The column headings, however, are always in the same row. Obviously I don't want to have to manually update the formula every time I receive a refresh of the report, so any way that I can get the aforementioned ranges to become more dynamic, so that the correct value will be retrieved each time? I am thinking something like an INDEX/MATCH combo but I am struggling to apply that idea to the 'SUM RANGE' section of the SUMIF function. This is the formula I have so far:
=SUMIF(INDIRECT("'"&$B$3&"'!F6:F30000"),$E$3,INDIRECT("'"&$B$3&"'!AJ6:AJ30000"))
/SUMIF(INDIRECT("'"&$B$3&"'!F6:F30000"),$E$3,INDIRECT("'"&$B$3&"'!AA6:AA30000"))
I have a spreadsheet where I need to perform a calculation on the lowest 10 of the 20 most recent entries. Currently I am just deleting the oldest entry and putting in the new data but I now desire to keep all my data.
How do I select the new range of 20 rows and then tell it to take the 10 lowest values to perform the calculation
I have a Conditional Formatting query where in essence I am needing to ‘make invisible’ a range of data by changing the font and background fill to white (and at the same time nullifying the effects of a number of other Conditional Formats). On the surface, this seems an easy thing to achieve, but the problem is, the range I need to apply this CF is a moving range and is dependant on a user changeable date.
Description of worksheet:
The sheet is managing shifts worked over 1 year by 21 people. The top 33 rows are a frozen (and collapsing) display area below which is a matrix of 365 days down and 21 people across. The TODAY button uses a macro to scan the dates and display todays entry directly beneath the freeze line of row 33. The date in M28 can be entered manually and after hitting enter, that dates data is now displayed under the freeze line. Also, using the spin button will increment or decrement the date in M28 moving the entire matrix up or down.
My query..........
What I’m hoping to achieve, is to display ONLY the data selected by the date in M28 (ie that shown under the freeze line, and for all remaining rows to be ‘whited out’, but as the user increments/decrements M28, then the “whiteout” range also needs to increment/decrement. I have whited out a second worksheet “example” as if I had selected 18 Jan (this sheet will not move as there are no buttons, but the data is still there under the whiteout).
I hope this is not TOO unclear, but I would be grateful if anyone could suggest a solution or even tell me if this is not possible – or indeed any other way of achieving the same result ( I had thought of hiding the rows below the freeze line, but the freeze/unfreeze operation would probably be too jerky.
I am not sure if my problem is in the dynamic range or not. I have attached a small sample file. I tried to create a dynamic range by creating a named range of my "LabelRow" with the following as the refers to: =OFFSET($C$32,0,1,0, COUNTA($32:$32))
When I set my cursor on this, Excel makes an outline on the whole row 32 where there is data, which gives me some clue that it understands what I am trying to define. Next, I try to pick this range up and paste it using named ranges in a macro (CopyRange). It gives me an error like it doesn't understand the named range. I do similar macro code in many other places in this project and it works with non-dynamic ranges. Here are some other details on this project. I need a dynamic range because I insert new data into the dataset, which may be 2 quarters up to 20 quarters. This insert causes a regular range to be messed up. My ultimate goal is to graph selected rows of data with the same labels, but I need to have it starting at Q1, which is pushed to the right as new rows are added.
I want to search a word in my Data sheet and want to know what is the column position. Example: I have a word "LastName" as a Column header in my Datasheet whose column position can be anywhere in the datasheet based on the table selected. So i want to write a generic code which give me COLUMN position for this word in the datasheet.
So if "LASTNAME" header is coming as 7th column it return me 7
if "LASTNAME" header is coming as 9th column it return me 9.
I have an excel work book with 6 tabs. I would like to have Excel move an entire row from one tab to another tab (removing the row and inserting it in the other tab). I.e. Example I have a tab with items that are marked as "Open Actions" so if I were to change the drop down to close. Excel would move that entire row of actions to the tab with the "closed actions" and insert into the next available row. Now if someone were to come back at a later date say no it should be reopened than I would change the drop down to open and excel would move that row back to the open actions tab into next available row. I tried a PIVOT table and no good I played with few macro and not.
View 5 Replies View RelatedI am using the built in moving average function to calculate the moving average of a set of numbers. There are a few things that i would like to do.
First i would like to have the last result displayed in a single cell. Then next to that cell i would like to have a cell that would specify the period of the moving average. I would like to be able to change the period in that cell and have that change it in the actual function. And finally i would like to have the moving average in a chart that would also change its period once that is changed in the respective cell. I realize that this might need some VB coding which i am currently learning.
I have a macro that copies and pastes into another sheet. When I add a row of data into my spreadsheet I want the macro to be able to change the range size and copy and paste the add row with the prior data.
View 2 Replies View RelatedHere's what I want it to look like:
Here's Macro Code I have thus far: ....
I had this code working on a Range in a Worksheet. I decided I needed to move the range to another worksheet. I moved the data to column AA in a new sheet called Audit_Pool and renamed the sheet name in ThisWorkbook.Sheets, but I get a runtime error at the With Range statement.
With ThisWorkbook.Sheets("Audit_Pool").Range("AA:AA")
With Range(.Cells(1, 1), .Cells(.Rows.Count).End(xlUp))
Randomize
With .Cells(Int(.Rows.Count * Rnd()) + 1, 1).Resize(1, 2)
.Copy Destination:=Sheets("Audit_Results_Data_Collection").Cells(Rows.Count, "B").End(xlUp).Offset(1, 0)
.Copy Destination:=Sheets("Audit_Results_Data_Collection").Cells(Rows.Count, "B").End(xlUp).Offset(1, 0)
.Copy Destination:=Sheets("Audit_Results_Data_Collection").Cells(Rows.Count, "B").End(xlUp).Offset(1, 0)
.Copy Destination:=Sheets("Audit_Results_Data_Collection").Cells(Rows.Count, "B").End(xlUp).Offset(1, 0)
what I missed when I moved my list to a different sheet?
I have the following code that works properly, Copy & Paste Same Range On Protected Sheet From Multiple Workbooks
Sub RunCodeOnAllXLSFiles()
Dim lCount As Long
Dim wbResults As Workbook
Dim wbCodeBook As Workbook, book1R As Range
Application. ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
On Error Resume Next
Set wbCodeBook = ThisWorkbook
With Application.FileSearch
.NewSearch
'Change path to suit...............
I'm using a macro to copy the results of a formula and paste the values only on another sheet. The result includes lots of "blank" rows. I have another macro to get rid of the empty rows and move the information up.
It's not working because the "blank" rows aren't empty. Even though I paste values only, experimentation shows that the cells that appear blank return a false to the ISBLANK test with a length of 0.
So now I think I need a macro to run after the pastespecial command to look for cells within a range with a length of zero and delete the contents of those cells, but leave alone anything with a length of >0.
I am brand new to the idea of using VBA, but I have successfully cobbled together some stuff and can usually modify things to work.
It seems I need to maybe use some sort of IF statement along with a LEN and ClearContents. I don't want to delete the blank cells, just make them truly empty so that all of my actual data stays where it should, and my delete empty rows macro works correctly.
I did a search and see some info on clearing contents of columns or rows, or clearing contents based on the content of other columns or rows, but I'm unsure of how to tell it to search each cell within a range and clear the contents of 0-length cells to make them truly empty.
I'm trying to figure out the best way to reference a cell's contents as a range in VBA code. So essentially it would be simple like this (except of course this doesn't work)... Range("cell(contents,A1)").Select
I always look in my CD of old forum posts, Mr. Excel articles, Mr. Excel books I have etc. before I break down and ask.
I have column headings that come in through a file upload and for some reason some of the column headings(which are in cells in row1) have extra spaces in the middle of them and at the end of the word. This messes up some of my later coding when trying to find specific header names.
Example:
"Location " - has extra 2 spaces at end, should be "Location"
"Read (Only)" - has 3 spaces in middle, should be "Read (Only)"
I came up with this piece of code to quickly correct the bad headers by Trim()'g the contents of each cell, but it's error'g out & not sure how to fix it:
With Range("A1:A24")
.Value = Trim(.Value)
End With
It errors of course on the '.Value' line.
I have a workbook with lets say 10 sheets. 9 of the 10 sheets are identical as far as layout, they just hold different information for different employees. I need to get a range of cells that are NOT empty and copy that data to the 1 different sheet (summary page). A6 through A25 is where the data is going to be, but all those cells may not be filled up, so I want to drop the empty cells from the range. So If only A6-A15 are filled, then those are the only ones that get copied.
ALSO (dont you love that) I need to take that number range and have columns A, F and I as well. So if I have A6-A15, I will need the data out of F6-F15 as well.
I'm trying clear the contents of a column range when the cell above this column range is empty. I know how to do this for one cell, but I would like it to work for a rangefrom R15:BB15.
If IsEmpty(Range("r15")) Then
Range("r16:r35").Select
Selection.ClearContents
End If
End Sub
I am filtering column S in a sheet to show all rows with a date after the end of the previous month - i.e. >= 01/01/07. What i want to do is clear the contents of those visible cells in column S. I tried the code below (got it on this site) but it works its way up from the bottom of the sheet until it finds the first visible row and then clears the contents of column S in each row above it, whether it is visible or not.
Sheets("Planning").Select
With Range("e2:C2")
.AutoFilter field:=5, Criteria1:="<=" & Sheets("Filtered Statistics").Range("c3")
.AutoFilter field:=19, Criteria1:=">=" & Sheets("Filtered Statistics").Range("d3")
For i = Range("s65536").End(xlUp).Row To 3 Step -1
If InStr(1, ">=" & Sheets("Filtered Statistics").Range("d3"), Cells(i, 19).Value) = 0 Then
Cells(i, 19).ClearContents
End If
Next i
.AutoFilter field:=19
End With
I'm using the formula =SUMIF(F18:F18,"LY",G18:G18), =SUMIF(F18:F18,"KT",G18:G18), and so on.
I want to change the range to: =SUMIF(F7:F18,"LY",G7:G18), =SUMIF(F7:F18,"KT",G7:G18), and so on.
I can do it manually, but it's 20 rows.
Is there a way where i can change the formula range on the first and copy it down, without also copying the initials, (etc. "LY") ?
I'm trying to add a line of code to filter some data based on a date in the range cell C3.
This is my code;
[Code] ....
It doesn't seem to work.
I have a range variable named data I want to clear all the data in the 3rd column in the range variable I know how to reference a single location in the range variable but not a whole column. see example code below
[Code] .....
I want to have users fill out. While it is possible to have it Read Only so that they always SaveAs I would rather put in a macro to clear the user input fields. I have done this in Excel 2003 and when I try using the same techniques in 2007 I get an Error 400.
I have all the cells (some are merged cells) in a named range and have the following code for clearing them.
Is is possible to clear the contents of a cell range if a message box appears stating an error. I have tried the following code but the logic doesn't actually work and am struggling to achieve what I am trying to do.
My code so far is as follows:
Code:
If ActiveSheet.Range("F84") > 0 And ThisWorkbook.Worksheets("PES").Range("D24") = 0 Then _
MsgBox "Your Entitlement is currently 0", vbCritical, "Error"
ActiveSheet.Range("K84:T84").ClearContents
The message box appears fine if the IF statement is true but if IF statement is false, the cell range of K84 to T84 on the current sheet is still cleared..
I've got a spreadsheet with a 'data' sheet and I want to add and name additional sheets based on the contents of a range in the 'data' sheet - it could be 1 extra sheet, it could be 100, the range is open ended.
View 3 Replies View Related I have a cell with a drop-down menu where the user can select "Plant A" or "Plant B".
I have a cell below that with a sales number, which comes from a different tab where the sales number is input and has a named range.
For example, the input tab has a named range called "Plant A Sales" and a value of 1234, and a named range called "Plant B Sales" with a value of 5678.
I want to put a formula in a cell that will look at the cell where the drop-down menu is and will populate that cell with the correct data for the selected plant.
So if the user selects "Plant A" from the drop-down menu, the cell will show "1234". If they then select "Plant B" from the drop-down menu, the cell will show "5678".
There is data(numerical) or blanks in each of the cells in the range H32:O37
I want to clear the contents if they are a duplicate of the previous line.
Looking for a macro hopefully. Found many to delete the whole row but just the specific range based upon the duplicate criteria..As there is data surronding that area do not want to delete whole row just clear that specifc area.
H32:O32 clear contents if same as H31:O31 if any valuse are different then stays the same
H33:O33 clear contents if same as H32:O32 if any valuse are different then stays the same
H34:O34 clear contents if same as H33:O33 if any valuse are different then stays the same
H35:O35 clear contents if same as H34:O34 if any valuse are different then stays the same
H36:O36 clear contents if same as H35:O35 if any valuse are different then stays the same
H37:O37 clear contents if same as H36:O36 if any valuse are different then stays the same
I want to be able to send out one “master” workbook with the fourteen ranges, and use a pull-down list (or anything else) so when the user selects a state, the proper lookup range will be used. In the attached abbreviated example, I want the lookups in Column B to refer to the state designated in B2. In this case, the VLOOKUP works correctly, since Colorado zip codes are in the ColZip range.
The people in Idaho or Oregon using this will only be working with their respective zip codes in ranges IdaZip or OreZip, so if they enter their state in B2, I would like the VLOOKUP to look in a different range. My whole idea is to only have one master workbook so I don’t have to create one for each district office. Maybe I’m thinking in a wrong direction altogether.