I have a 7 (columns) by 2 (rows) grid of cells which I need to perform the following,
If cell a1 is populated, then it will clear the contents of a2, or if cell e2 then it will clear the contents of e1. Basically if any cell is populated it will clear the other corresponding column entry.
I came across this code which works well for one cell
...
Is there way to automatically clear the contents of a cell when the user clicks in that cell. Eg, click in "A1" then then "A1" clears automatically.
View 10 Replies View RelatedIs 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 have the following Worksheet Change Event in my worksheet. The contents of range rng are not cleared.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
rng = Range("F" & Target.Row & ":M" & Target.Row)
If Not Intersect(Target, Range("N5:N1000")) Is Nothing Then
If Target.Cells.Count = 1 Then
If Target.Value = "Yes" Then
Application.EnableEvents = False
rng.ClearContents
Application.EnableEvents = True
End If
End If
End If
End Sub
I have used the following =IF(ISBLANK(Q6),"",(NOW())) & =IF(ISBLANK(Q6),"",(Today())),
but when the excel file is closed and opened the date changes to now or today. How can I make the date stay and not change?
I am trying to make a date auto populate when an entry is made into a specific cell.
Context:
Cell H has a drop down list of staff names who will be assigned to a project, once a name is selected from the drop down box:
Cell I needs to auto-display that days date
is this possible with just a simple formula?
I am trying to have cells automatically populate when a cell in the same row is populated with data. I am trying to avoid a formula because this workbook will be a large file and I don't want to slow it down anymore than I have to.
I currently have a macro that pulls data from a website and fills in the data into Columns "E","F" and "G". Column "E" has values that are Alpha Numeric but also start with letters "P" or "R".
I have the Job ID in "I3" (Numerical Value) and the date of the job in "J3".
What I need to do is once "E6" is populated with a value, copy and paste "J3" to "C6" and "I3" to "D6". When "E7" has a value, copy and paste "J3" to "C7" and "I3" to "D7". When "E8" has a value, copy and paste "J3" to "C8" and "I3" to "D8", etc.
The cells "J3" and "I3" will never change location, just the value will change.
how to hide a value on a cell when another cell is not yet populated?
like for example:
A1 is Blank A2 has a formula of workday(A1,2) a date will appear on the A2 cell and I want not to show or be hidden the date in A2 until A1 is populated
I am now looking for a command that will return if a cell is populated. I.e. I need something that will find out which cell is the last one populated in a column (populated = contains data) and then will move 2 cells down, and then will enter some data into the following cells.
View 4 Replies View RelatedI am trying to get a cell to become a hyperlink after it has been populated.
For Example:
If cell H42 of Sheet 1 = [ X ] Then create a hyperlink to cell B2 of sheet 2.
I have been trying quite a few different things the latest I have written is the following:
Sub Macro1()
Dim cell As Object
Set cell = [ X ]
If H42 = cell Then
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
"Sheet2!B2", TextToDisplay:="[ X ]"
End If
End Sub
I am trying to write some code to choose the last "comments" entered in column 13 and then copy the data to B17 on the specified sheet below.
I get an Excel Error Excel cannot complete this task with available resources. Choose less data or close other application THEN a VB error Method'open' of object 'Workbooks' failed
Code:
Dim MyMgr As String
Dim whichsheet as string
MyMgr = Range("D10")
whichsheet = Range("D11").Value
If MyMgr = "Manager1" Then
Set myBook = Workbooks.Open("destination", Password:="manager1") 'Home
[Code] ........
I have to create a formula to fine difference between 2 numbers.
There are columns A to Z.
Values will be populated in A1 to Z1. Sometimes the value will stop in between itself. For example H1.
So, I need a formula to catch the last populated cell (H1 in our case) and then find the difference between the value in its previous column.
H1-G1 should be the result I should get.
I'd like to get a reference to the last row or cell in a column of data.
View 9 Replies View RelatedSee attached formula:
"=INDEX(DATABASE.xls.xlsx!$AT$5:$AT$2001,MATCH(AR10,DATABASE.xls.xlsx!$O$5:$O$2001,0))"
Cell AR10 is my target cell and then there are 15 other cells that populate when the correct information is put into cell AR10, but if one of the other cells does not have any information in it, it returns a "0" (zero). I am trying to get the cell to show blank as when I print I don't want a "0" (zero) in every cell that has not got any information.
2 x cells are showing telephone numbers, 1 x landline and 1 x mobile - sometimes we don't have a landline number, so I want the cell to show blank not "0" (zero)
The cell format is set to "text", but I have tried setting it to "general", "number" and finally settled on "text", but all return "0" (zero) if we don't have a number for them.
I have attached a sample file. I would if possible like to have a hyperlink at the top of the sheet that when pressed would take you to the last populated cell in column G. I can do a normal hyperlinks but over time the hyperlink would take you to the wrong place as the sheet is filled in more and more. Is this possible?
I have tried to adapt various macros I have found on this forum but they on't seem to do want I ask of them I also seemed to have acquired an extra vba project (Ubitmenuo4) in the worksheet and can't get rid of it, so just ignore that
I have a list where the lines of data I need are not uniform in relation to the customer identifier, ideally I want to get the Lookup (or equivalent) to ignore the blank and move down the column to the next populated cell as below.
Customer identifierAddressCustomer identifier
Address1Vlookup?12Vlookup?Address 13Vlookup?2
Address 24Vlookup?3Address35Vlookup?6Vlookup?47Vlookup?
Address48Vlookup?59Vlookup?10Vlookup?Address 56Address 67
Address 78Address 89Address 910Address 10
If I have a cell "C8" default value is "choose" and it is a drop list of other values. I want to validate if "C8" is populated with a value other than "choose" that the user populates the SOME of other cells in the row like D8 or F8? I would like to alert something that the cell needs to be populated.
View 1 Replies View RelatedI am trying to create a message box that initiates when a cell is populated with a value that is >= a certain value.
My current code looks like this:
Sub Flex()
Application.Screenupdating = Fales
If range("C6") >= .95 Then
Msgbox "Loan approval must be Flex"
End If
End Sub
The code works when I run the macro manually, but I want to run when the user changes the value in the cell.
i need to find the last populated cell in the row say I9:CN9 and return the description for that column the resides in I5:CN5 and then fill the formula down for about 500 rows, just not sure even where to begin, do i need a custom formula in VBA? as i can't seem to find a combination that works built-in...
View 5 Replies View RelatedBeen racking brain, searching through the forum here, and my Excel 2003 Bible all day trying to figure out this problem to no avail. I would like to clear the contents of any cell in a given range if the cell immediately to the right of is formatted as bold.
View 2 Replies View Relatedi need a code that moves down a column and for every empty cell in the column the cell to the left is cleared and then it moves on to the next cell down. the column is not always the same and will start from a selected cell, and the column will contain no more than 5 rows
View 3 Replies View RelatedI am relatively new to VBA. I am creating an attendance calendar that tracks employees calling sick, late etc.. It is a point based system. What I am looking for is, a way to clear the point value that was manually entered in a specific cell (I3), if there is a Value manually entered in (CU3). Each column in my worksheet is for a specific date ie; I3 is the cell where I enter the points (1.00) for that employee by calling in sick on 3/1/2013, (Column "I" is for 3/1/2013). After 90 days, this point accumulated by the employee does not count against them, so I need that point entered in (I3) to either = 0 or the cell contents to be cleared if there is a value entered in cell (CU3) which is 91 days after, so my (A3) cell does not add that point acquired on 3/1/2013.
I need this to run in a range (I3:I450) so if any value is entered into (CU3:CU450) it has the same result and continue to for (J3:J450) so if any value is entered into (CV3:CV450) and so on..
I am a financial administrator & every month I have down load 4 bank accts as CSV, import into xl & code (CACode) the amounts for our accountant. I have VBA that formats, adds headings & formula etc but I have a problem/s. In H col I place CAcode & I use a sumif formula in I col to sum all the amounts with that have the same CAcode. For simplicity sake I copy the sumif down & then sort H col ascendindingly (this is done by VBA). Now I am trying write a macro to clear the contents of the cell in I col
if eg h60 = h59
then I60 clearContents, Select h59
Else select H59
Do until H3 is selected
Psuedocode
Select table (A2:I Xldown)
Sort Table by CAcode (H Col)
Set Range as H3:Xldown
Select Last cell with CAcode (Xldown) in H col
For every cell in Range (H3:Xldown)
Use If/thenIf Last cell = 2nd Last cell (H Col) then
Clear contents of I col (last row)
select 2nd Last cell (H Col)
Else 2nd Last cell (H Col)Next Cell
I have to use Xldown to select range as the range will be variable each month & for each bank acct. I need to clear contents of cell to verify that all the sub-totals of unique CAcodes of the CAcoded amounts = the totals Because I may have up to 120 rows X 3 bank accts I am slowly using vba to do my work.
Sub sort_And_delete_Sumif_amounts()
Dim r As Range
'Select range to sort
Range("A2:I98").Select
'Sort CAcode in H col ascendingly
Selection.Sort Key1:=Range("H2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
'select range for comparisomn of CAcode
Set r = Range("H3", Selection.End(xlDown))................
I am looking to change a row colour if any type of data is entered into a specific cell.
Context: In column H entitled 'Start Date' when someone enters a date n any format into this box, I want the whole row to change colour to easily see what projects have started and what ones havent.
I have a column array with various cells in that array populated. In every subsequent cell in that array I want a formula that finds the previously populated cell and that value added a cell that is in the same row but two columns to the left.
Basically i have a manpower chart with months along the top and names down the far left. People are going to start work in different months so i want to know at the side of each persons name the month they start. Heres a simple example
I have column headers starting at B1 thru F1, with the following values Month1,Month2,Month3,Month4,Month5
In cell D2 i have the value 1
I want Cell A2 to return the value Month3
Whats the formula i need to input in A2
I've populated a temporary worksheet with several columns of data. In this example my Amount Column has several null cells. What I need to do is write a macro to find any nulls and populate the nulls with the data in the last populated cell above it.
Example: the four empty cells below Account (524150) also need to be populated with 524150. The cell immediately below 524200 also needs to be populated with 524200 and the 3 empty cells below 512800 also need to be populated with 512800. Week to week the number of rows will change and the nulls will also change.
DATA
DATE
PRICE
DATA
20120621
118.1000
[Code] .......
I have 3 columns (data, date & price). What I would like to do is find the last populated cell in 'date' column and autofill it based on column 'data'. The tricky thing is that the date form is text and while autofilling manually it changes.
I have a spreadsheet with many worksheets, everyone except the first representing a year. Every worksheet has twelve tables (the months), the first row of each table has the days of the month and the second row has the data that I put on a daily basis. The first worksheet calculates statistics using the data from the other worksheets.
I am looking for a formula that returns the date by finding the last populated cell and looking right above it, so I can use this info in the first worksheet to show when the spreadsheet was last updated.
I have a template worksheet that several people use and print from.
The range on this worksheet used is A1:C499
I want the VBA to look at tha last cell that is populated in column A and set the print area to suit.
i.e. the last cell used in column A could be A277. then I'd want the print area to be A1:C277.