My Excel Vba shown below inserts time() into a cell on my sheet. Is it possible to clear this cell if time() is already into the cell. My VBA is onky entering the time() once and not insering the current time() if a change cell values after some time i.e.
View 3 RepliesWith code I enter text by double clicking in any cell in a range.(column A,B,C are excleded)
What I need is to clear the content of the cell adjacent to the left of the one I choose to dbl click AND the one below that.
Example: I dbl click in E1 and the content of cells D1 and D2 is cleared.
I am quite a novice with excel and I am trying to get a cell to show blank when I select a drop down list value in another cell.
Cell D2 contains a validated list containing two items (Air, Vacuum).
If I select Vacuum I wish to change another cells (D4) contents to blank, D4 is also a validated list containing three items (Yes, No, BLANK) the blank is actually a blank space and not the text blank.
I can get the cell to operate correctly but if I select Air in Cell D2 and then Yes in cell D4, but then change my mind and reset to Vacuum in cell D2 i need the cell D4 to clear its contents automatically.
I have a sheet which is running a a query against an external source, and coming back with a list of client data. Each row is an investment with it's own unique InvestmentID number and a ClientID number. Each client has their own unique ClientID, however may have multiple investments (InvestmentID) linked to their ClientID (does that make any sense?!).
Anyway, the query I have returns ALL investments. I know how to manually change this to only bring back certain client(s), however I want to be able to have the users (who don't know what a query is, yet alone to how to use/edit one) enter a ClientID into say, cell B2 and have the query reflect that ClientID. If they have to click a button to run a macro after entering the ClientID, so be it (although it'd be great if it actioned once they hit "Enter").
I know this is possible because I've used something similar before (that someone else created!). What I don't know is... if I had multiple "entry cells", could I have the query filter by two (or more?) ClientIDs? That is, they enter the first ClientID in B2 and the second ClientID in C2, the query then returns all information relating to B2 or C2
I have a cell A1 in sheet2 linked cell A1 in sheet1 (simply A1='sheet1'!A1). A1 in sheet1 is a data validation drop down menu.
I want to clear the content of A2 in sheet 2 everytime the content of A1 in sheet2 changes/is updated. That is everytime the value of A1 in sheet1 is changed using the drop down menu.
I tried using a Worksheet_Change event macro (which I do not fully understand) but it won't work with a cell that updates from a calculation. It also doesn't work if triggered from a cell from another worksheet (I tried linking it to cell A1 on sheet1 in this case).
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub
Range("A2").ClearContents
End Sub
Any simple solution to clear the content of cell A2 in sheet2 when A1 in sheet2 updates?
I wrote some code that toggles bewtweeen the word "Yes" and the function clearcontents.
What it does not do, is if you click on cell A1 and change it's contents and you click on the same cell again it does nothing. You need to click on another cell say A2 before you can go and change A1 again.
My code is as follows:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("Documents")) Is Nothing Then
On Error Resume Next
Application.EnableEvents = False
If IsEmpty(Target) Then
Target.Value = "Yes"
Else
Target.ClearContents
End If
Application.EnableEvents = True
On Error Goto 0
End If
End Sub
i have the following macro when cell value in column "K" changes, it distributes the data accordingly.
however, the issue is, if i change the value in K (which is a date), so another value, it leaves the distribution in tact....i want to apply "clear content" and then make the macro re-apply.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Left(Target.Address, 3) = "$K$" Then
Dim srt As String
[Code].....
I have a text file being used as a log file. Sometime I need to clear this file when I start-up the UserForm. I load this text file with this code. First is this in Module1:
Public Const FILENAME As String = "Log.txt"
Public Const LOGFILE As Integer = 1
This defines the text file to be added to. The code that actully apends the text to the first open row of the text file is: ....
I have a column with various values in, string and numeric. The strings I want to keep have "," in and letters one side and "space" on the other. All the cells I want cleared do not have ","
The rest of what I'm using works fine accept this line which forces an error.
[Code]....
I am currently working on writing a macro that clears all rows in which the cell in column F doesn't say "Sale", "Purchase", or "Tax Code Description", however, I keep receiving an error that says "Wrong number of arguments or invalid property assignment".
[Code] .....
Also, I originally was deleting the rows, but I have formula's pulling from this tab and need them to not lose their reference after the macro executes.
How can I create a msg box which ask's for Yes or no option before clearing a sheet with all contents?
if code is this:
Sub Clear_sheet()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Sheets
If Not ws.Name = "master*" Then ws.Cells.ClearContents
Next ws
End Sub
I am trying to return the range of cells that is not in a range of cells.
Dim rge As range
Set rge = activewindow.visiblerange
'set rge2 = cells not in rge 'HERE IS WHERE I NEED HELP
rge2.clearcontents
I am trying to avoid a for each style loop.
I use this to read cell content, add some text/characters (ie. [ and ]) and change the properties of the complete cell
Sub COMMENT()
Worksheets("DVD Lijssie").Activate
If ActiveCell.Value 0 Then ' Change all in to ... ... ...
ActiveCell.FormulaR1C1 = ActiveCell.Value & " " & "]" & " " & "["
With ActiveCell.Font
.Name = "Arial Narrow"
.Size = 8
.ColorIndex = 16
End With
End If
End Sub
HOW can I change this vba-code so it leave's the content of the cell like it is and add some content with the use of let's say TexBox1 and ONLY use different font properties for the newely added content?
Formula/macro/etc that would enable me to have content of a cell changed based on the content of another cell in the same row.
Example: cell in column D says "PSA" - so I would need the cell in column H for that same row to read "Radio"
I would need an entire sheet scanned to review for these occurrences and make the appropriate changes. I also would need the formula to be inclusive enough to scan for variations in column D cell content (PSA 1, PSA 2, etc).
Need the query parameters which takes the date from the cell into the query. How should I modify my query if it needs to take the date from a cell?? The bold one date should be picked from one of the cell in sheet 2.
My query is this
WEB
1
http://fc-web-phl1-101.phl1:8090/gp/...runReport.y=12
Selection=15
Formatting=None
PreFormattedTextToColumns=True
ConsecutiveDelimitersAsOne=True
SingleBlockTextImport=False
DisableDateRecognition=False
DisableRedirections=False
I have a row (will always be row 3) where each cell contains a day of the week, the days repeats for a year or so, making the row almost 400 cells.
Like this,
Mo - Tu - We - Th - Fr - Sa - Su - Mo - Tu - We - Th - Fr - Sa - Su - and so on...
Though, A3 doesn't have to be "Mo" because the days in this case can change (A3 can start with "Tu"), hence I think I need a macro.
So if this row contains a weekend, "Sa or "Su" I want all the cells in the column beneath that which contains a specific value to be cleared.
Example, if "Sa" or "Su" has 3 values in the columns under them, all the values that contain "X" or "Y" has to be cleared.
Like this:
Rows (1,2,3...,)
1----
2----
3 Mo - Tu - We - Th - Fr - Sa - Su - .. and so on..
4 A --- B --- X --- Y --- X --- B --- Y
5 A --- B --- X --- Y --- X --- X --- X
6 A --- B --- X --- Y - --X --- Y --- C
After the macro it should be:
1----
2----
3 Mo - Tu - We - Th - Fr - Sa - Su
4 A --- B --- X --- Y --- X --- B ---
5 A --- B --- X --- Y --- X --- ---
6 A --- B --- X --- Y - --X --- --- C
Notice the two examples in the excel file.
Excelforum.xlsx
For a table like the one below produced for the sake of example (actual is much much bigger) I want to make it list rows that are true for a certain column for a certain variable in the matrix. So for say water terrain, which types of activity can I do i.e. swimming. Or for Offroad the activites which I can't do i.e. Run and Swim.
ActivityWaterRoadOffroad
Jog nym
Run nyn
Walk nyy
Swim ynn
y=yes
n=no
m=maybe
I am using VBA to create a word document (.docx). This word document contains plain text content controls as well as picture content controls. I then use VBA to automatically select a picture based on the code below
Code:
Set oCC = Word.ActiveDocument.SelectContentControlsByTitle("TabPic").Item(1)
On Error GoTo TabErrorHandler
oCC.Range.InlineShapes.AddPicture Filename:="X:XFERANDREW-TDCD " & LblVL &
[Code].....
After the document has been closed down I try to open it again and I am told "The file cannot be opened because there are problems with the contents."
When I click details it says "Unspecified error" and "Location: Part: /word/document.xml, Line: 2, Column: 0"
If I click ok it says "Word found unreadable content in "". Do you want to recover the contents of this document? If you turst the source of this document, click Yes.
Clicking Yes opens the document with all the contents and it is now renamed to Document 1. If I click no it does not open.
how to combine the content of 2 cells into one cell and have the information separated by a comma.
For example:
CELL 1:
Software 1
CELL 2:
Spreadsheet Software
The desired results is:
CELL 3:
Software 1, Spreadsheet Software
I have an MS Access query that contains a parameter. The parameter is a date field, and I have configured that in the Access query. If I run the query within the MS Access user interface, it prompts me for the paramater value as expected, and runs just fine. However, I want to connect to this query from within Excel as a data source.
I have created a connection to the Access file using ODBC from within Excel. In the MS Query window, I am merely selecting all of the fields resident in the MS Access query, and returning all values. In other words, there is no selection criteria in the MS Query. I have done this many times with Access queries that DO NOT contain a parameter, and everything works fine. However, in this instance, I need to pass a parameter through to MS Access in order for the query to run. At the moment, I get the "Too Few Paramaters...1 expected" error message. This makes sense, because I haven't figured out how to pass the paramater to MS Access.
Is there a way to structure this that does not involve VB code? If so, I'd love to know how. I have tried creating parameters in MS-Query with the same name, but although I get the prompt it doesn't connect with the Access query as the source for the parameter value.
If the solution requires using code, I'm good with VB Code in Excel...is there VB for Excel code that could make this happen?
Failing that, I guess there must be (I've seen a few in my search thus far) Access VB Code that can make this work. I'm very rusty using VB with Access, so this is my least favored solution. However, if this is the only option, keep in mind that I need to pass the paramater ultimately from a user who will initiate the process using Excel.
I have a csv file on another drive on the network that i need to query. I believe that ms query would be the best way. I know that a DSN needs to be setup but this macro will be used by various users who wont know how to do that. thus I would like to create one via VBA every time the task needs to be run.
I haven't a clue how to do this and i need it to be explained to me in general terms with words of one syllable!
How to determine the correct formula for this requirement?
Assuming I have 2 individual excel files and an index excel file (in reality, there are more than hundreds of file). For index excel, once the user enter part number (eg. 1 or 2), the excel will look for the part number excel file and determine vlookup function.
The only problem I have here is I can not make the index file automatically add the part number shown on most left column into the required formula (replace the part number file section).
I tried use the indirect function but this require each file to be opened, which is not possible for actual use. I am looking for a function that can work in closed worksheet.
Attached files :
index.xlsx
1.xlsx
2.xlsx
I am working on developing QR Codes using some MATLAB code and it would be really convenient if I could create an excel program which changed the background color of a cell containing a 1 to black and a cell containing a 0 to white.
View 2 Replies View RelatedI am currently trying to create a spreadsheet whereby if I enter certain text in a cell in Column A on worksheet 1 that correlates with text in a cell in Column A on Worksheet 2, then the description in Column B in Worksheet 2 is entered into Column B on worksheet 1.
For example, if worksheet 2 has the following:
Column A Column B
XXXX PRODUCT 1
YYYY PRODUCT 2
and I enter XXXX in column A on worksheet 1, I want Column B on worksheet 1 to automatically enter PRODUCT 1.
multiple search match and replace content in a different column so for example
new workbook (look up table)
sku
search1
search2
[Code]....
so something like where you compare two tables and find and replace based on another cell that matches in my sku.. more details would be if the table column aren't exactly matching but the column header and the row header would match and fill or replace in the correct/corresponding cell is there a macro or vba to do this job in excel?
I have the names of companies in one column, and the amount they owe in cells in the column beside them. I then have a second list of companies that is a subset of the first. Is there a formula that would place the amount they owe in teh corresponding cell adjacent to the compny in the second list? I've attached a sample workbook, Full Company List in column A, amount owing in B, trimmed down list in D and ideally I'd like the corresponding values in E.
View 3 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 have 2 worksheets, let's call them "Sheet1" and "sheet2".
Sheet 1 has 2 columns (A, and B)
Ie. "Sheet1:"
Col A, Col B
Red
Green Yes
Blue
Yellow
Orange Yes
For each entry in Column "B" that has a "Yes" value, I need to copy the color value in column "A" into the next available empty cell in "Sheet2" in column A.
"Sheet2"
Col A, Col B
Red 3
Blue 5
Yellow 6
Final Result:
Col A, Col B
Red 3
Blue 5
Yellow 6
Green
Orange
how to do this particular challenge? For Sheet2, I think I'll need a function to determine the first available blank cell in column A.
What I need to do is have a cell that will be say yellow until there is information put into this cell. The information could be in the format of text or numbers. The information would not always be the same so it would need to be yellow when there is no information in the cell and another colour or white when there is information in the cell.
View 6 Replies View Related