Modules & VBA :: How To Selectively Clear Cells In A Column
Oct 7, 2013
I have had to copy a column of dates to a new column to extract the month and be able to make subtotals based on each month.Now I am trying to tidy up the column by removing all the cells containing the month and leave behind the subtotals.
I have got a little way there using:
lastrow2 = Range("A1").End(xlDown).Row
With Range("A1:A" & lastrow2)
Set RngFnd = .Find(Total).
Set rngDelete = .ColumnDifferences(Comparison:=RngFnd)
rngDelete.ClearContents
But it only clears the cells down to the first subtotal so how can I get this carry on?
Alternatively I could use code to find the subtotal, and then cut and paste it into the next column over if that would be easier?
I'm currently making an Experience Statement for my company. In one of the forms I've created I an Industry sort that is weighted and connected to a table so that it'll show a report based on the numbers provided IE. 1-5 One being shown first and a blank statement not showing on the report at all.
I have the report function working correctly the only thing I would like is a cmd button that clears sort table of numbers so that it doesn't show anything and can start fresh. What VBA code shall I use?
I was thinking something along the lines of this, but it's not working.
Dim s as String s = "UPDATE All_Projects_Sort_Table SET Industry Sort = Null;" CurrentDb.Execute s Requery
I'm new here and I have a question: In my db I have a yes/no tick, now I want to create a button that clears out all the ticks in that specific column, possible? To clarify: I have an address db with an gift table (tbl_gift) and a yes/no parameter for, lets say, 'did they gave me a present with christmas?' (col_gift). Naturally I want to clear their records before next christmas, now instead of going through all the records and remove the tick, is there a fast-one-button-click solution for this?
Thanks in advance! Paul (btw: sorry for my English, I'm Dutch and damn proud of it!)
I'I need to randomly input values into row cells and when press enter key, it calculates the total value and show in another sheet. Example, in sheet1, I input random values in A1,C1,D1,Z1 and when I press enter key, it calculates the total value and appear in the first row cell, A1, of sheet2. All by VBA coding.
I have a few problems and I want them to be able to be done from switchboard:
1. Is there a way for me to export a particular report (after selecting it) to a closed excel template, that is formatted? It would open the excel template (that has a logo and column headings), export data to below the column headings, then save the file with a unique name?
2. Also, a way to import data from an excel file, after allowing the user to select file? Only data below the column headings mentioned above. Same data will be appended to existing table.
I have spent the day using Access 2010 and attempting to move information from a parameterized query into specific cells in an excel template. It runs smoothly until I attempt to reference the query at which point I run into th error "Too few parameters. Expected 1." Currently my reference code looks like this:
I am trying to write a VBA Module that will look in a directory for Excel files with a specific file prefix, and then import specified cells into a temp table. Once it processes the first file, it copies the file to another folder, and then kills the file. However, when I run my current code, it goes through the loop the first time, but fails on the second attempt, because it is still looking for the first file it came across even though it has been moved. The code is pasted below:
Sub Count()
Dim xlwrksht As Excel.Worksheet Dim xlWrkBk As Excel.Workbook Dim nIndex As Integer Dim strMvPath As String Dim mvPath As String Dim strFile As String
I receive a bunch of excel files with items to be added to a database. some of the items are to be added as new, some to be deleted and others to be modified.
I identify the action (add/delete/modify) by looking at a column in the excel file e.g. column A.
At this point I am fine with the add/delete because I can filter the data or bring all the files to a temporary table in an Access database via vba script and then running queries to do the rest.
My issue is that the spreadsheets have a lot of columns and for the modifications what they do is use the same file for instance, make changes on the cells (any cell) that need modification and then highlight, bold or underline only the items that need modification and ignore the rest of the file (a tedious process since I have to then open each file and manually modify the database).
I'm designing a database for a laboratory. There are many tables that will contain test results. They all have in common a field called ID( primary key) linked by a one to one relationship because one patient has reports in different tables. The ID is unique to each patient. For the Reports, i hit the rocks... How can i structure a report that can extract a patient's records from the different tables where they appear in the database?
I am writing the following code that will first of all display column headers dynamically using "Headers" field data from Access table and then find out the sum(volume) using column header and first column values. The following code works fine to display headers dynamically in Excelsheet from Access table but doesn't display sum(volume) in all the corresponding cells. As I can't attach the Access table so I have stored data from Access table to sheet named "Access Data" as attached. The sheet2 named "Report" should populate total volume .
Code: Public Function Inputdata() Dim cn As ADODB.Connection Dim rs As ADODB.Recordset Dim r As Long Dim i As Integer
I have the code below which takes information from a form on access and sends it over to the correct place on an excel spreadsheet template. This works fine but I then need it to save and send on outlook.
The issue I am having is that the saved document is not attaching to the e-mail. The subject etc all work fine but the excel spreadsheet just doesn't attach. When I go into the folder I have specified for the document to be saved in it isn't there either. :0(
The code for the e-mail "callmail" function works perfectly for word documents but I don't know if it is different for an excel file.
Code:
Private Sub Command154_Click() On Error Resume Next Dim appExcel As Excel.Application Dim wbook As Excel.Workbook Dim wsheet As Excel.Worksheet Set appExcel = New Excel.Application
I have a form consisting of a list box and some fields belonging to a single record. When the user highlights a row in the list box then the fields show the details of that record. Now the whole process is Read only. But I wish to put a toggle switch on the form so that the detail fields of the record become updateable. I have tried the following constructs but I could not make those fields updateable.
using Ms access 2010.I have two text boxes where depending on what is entered on the first the 2nd disabled.that works fine but if I make a mistake and enters a value on the 2nd while it is enabled and return back to the first and enters a value which disable the 2nd, it disable it ok but keeps the value and even save it to the table.
Private Sub q300_AfterUpdate() If Me.q300 = "1" Then Me.q300_1.Text = "" Me.q300_1.Enabled = False Me.q301.SetFocus Else Me.q300_1.Enabled = True End If End Sub
I have got a form with a couple of dlookups on it, when they return an error it pops the debug message up and if there is a value in the field already it just leaves that value there.
How can I suppress the message and clear the field and move on to the next line of code?
I have a relatively complex update that I need to perform on a table from a form. I have a system that has "games" and "game copies". The game is simply a name of a particular game and the copy is something that has a stock number but a foreign key of the game catalogue number. This means I have several catalogue numbers that are the same in the GameCopy table.
The problem is that I have to reserve a game - not a game copy. When I have made a game available (it has been returned) I have to indicate this in the reservation table. I could have many different reservations for the same game so I need to only update the oldest reservation and indicate that a game copy is available now.
Summary: One "Game" Several "GameCopy" using "Game" as a foreign key Reservation table with possibly several reservations for the same Game - not GameCopy(s)
In other words I have an "Available" field in the reservation table and a "date reserved". I need to create an "Available" (Date()) entry for the oldest DateReserved entry on that reservation table. I could have done it as a boolean but I decided to use a date instead for logging purposes.
Would I use some kind of "Once only" action to make sure that only one of the reservation entries are updated? I really do not know how to proceed with this.
Obviously if I simply:
UPDATE Reservation SET Reservation.Available = Date() WHERE Reservation.CatalogueNo=Forms![Current Reservation].CatalogueNo;
...then it will update all of them. I believe there must be a bit more SQL I have to add or something else maybe.
I need to input a string into a column named "EventType". The code should first check if the column "Agent Name" contains any strings. If there is none, it will input "IBM Director" into the EventType column.
Once it has looped through the agent names, the code will then loop through the Details column and input into EventTypes based on what is displayed within the string.
These are the codes that I am using to achieve this, however nothing is being input into the EventType column.
Code: Private Sub Command11_Click() Dim dbs As DAO.Database Dim rst As DAO.Recordset
Set dbs = CurrentDb Set rst = dbs.OpenRecordset("Final")
[Code] ....
I think the problem lies with the code that checks the agent name. When I removed it, it managed to populate the EventType column based on the details. But I still need to find out how to check the agent name too.
I have an append query that adds data to a table in another database, but I want it to replace the data every time it's run. How do I clear the data before running the append query? The databases are not linked, and I don't want them linked.
I've put the following Function together to clear out unwanted bits before exporting as csv. I was just wondering if there's a way of holding the table names together with a total count of each item replaced? I wasn't sure if these were stored and could be returned?
Code: Public Function ClearCommas() On Error GoTo ClearErr Dim db As DAO.Database Dim tb As DAO.TableDef Dim fld As DAO.Field
I am trying to clear a textbox after the user enters an invalid date and I do not know why the following code is not working:
Private Sub txtStart_AfterUpdate() If Not IsNull(Me.txtStart) Then If Not IsDate(Me.txtStart) Then MsgBox "You have not entered a valid date" Me.txtStart = Null Me.txtStart.SetFocus End If End If End Sub
DoCmd.RunSQL (" update tbl_userinformation SET [05-Henrichpiramid] = Yes where Username= Text146.value AND actualdate=Text148.value ;") DoCmd.RunSQL (" update tbl_userinformation SET [combination] = [05-Henrichpiramid] where Username= Text146.value AND actualdate=Text148.value ;")
i want to update the column combination to its last value with concatanation to the value of current column.
