I took an Excel course in college but am a bit rusty. I have a customer database that I created and I would like to see certain things after entering a date range that I specify. I uploaded a small excel sheet to show my database and what I'm looking to do. Basically I would like to enter a date range and see these answers:
Total Orders in that date range
Total unique orders in that date range
List of customers (name included) who ordered more than once in that date range
The only columns of data I need to use are: firstname, lastname and orderdate.
I have a spreadsheet that contains data from sales being made in various locations (sheet 1). The date the sale was made is in column A, and the location is in column AZ. I may have from 0 to 12 sales at that location on the same date. I am building a report in another sheet (sheet 2) that counts the number of sales for each date and displays that number in a cell in row 3. Above row 3, I want a formula that will reference the location (AZ sheet 1) and display that into my cell above row 3 in sheet 2. Each column in row 1 sheet 2 will have new date.
I have 13 columns - 12 representing each month of the year and then a total. I start in F15 with Jan and end in R15 with the total - this is the heading row.
Below that I have my numbers for each month. Here's what I need to do: Anytime the total changes (by updating the monthly numbers), I need the day's date to be automatically entered into F14. This way I know the last time the data was changed.
I have used data validation to colour rows in one of my spreadsheets at work. The column that uses the data validation is the first column and has a drop down list of 5 words. One of which is collected, which currently indicates when the card has been collected by the individual in question and no further attention is required. It colours the rest of the row green. There is also a column that is entitled 'Date Collected'. Instead of having to input this manually, is there a way to automatically enter the date to be the date that the 'Collected' value was selected?
The title is misleading, I realise there will need to be some sort of IF statement in the 'Date Collected' column. It's been a long day already.
I have a spreadsheet where a date is entered in column A with data in B-F. I am trying to write code to look in column A for a specific date and enter additional data into columns G-L on the same row. It needs to be able to skip the rows that are blank. So I have the dates of 12/1, 12/2 and 12/3 in column A rows 1,2 and 3 and I want to enter data for 12/2 on row 2 skipping the blank cells in row 1 for 12/1. Here is the code I have below.
Private Sub CBSecure_Click() Dim my_name As String Dim r As Variant Dim l As Long sFind = DockDoorCal.Value If Trim(sFind) = "" Then Exit Sub
I am needing a custom format for the following. I need a cell entry to be 1 letter, three numbers, a dash, 5 numbers, a dash, two numbers, a dash, and then two number. Example: A109-54785-13-00. The first letter will almost always be an "A."
To further complicate the matter, the entry is copied from an email and pasted without the dashes. In the above example, the number in the email would read, "A109547851300." I copy it from the email and paste it in the proper cell in the worksheet. I would like the custom format to automatically enter the dashes at the appropriate spot.
If it was all numbers, this would be easy. I created a custom format for the numbers, but when the A is included, Excel no longer treats it as a number and the custom format did not work.
Is this possible in Excel (without VBA).
I could use the custom number format I created and then later go back and add the "A" at the beginning, but that is as much work as manually adding the dashes.
I have a custom range I'll be copying from one sheet (a single entry registration form) that I'm adding to another (an "all entries" sheet) with a button assigned to a macro.
I then loop through the range, copying the cell from Sheet1 to Sheet2.
All works perfect, but I need to manipulate the data a little. If B3 from Sheet1 equals a certain string, I want to manipulate the data From Sheet1 B6 & B7 to paste into Sheet2 Column 1, otherwise paste B3 into Sheet2 Column1.
Again, my loop and everything works if I put B3 into Column1, B6 into Column2, and B7 into Column3, but it doesn't play well with the reporting I want to do later from this sheet.
What I'm trying to do is if B3 <> "NEW TEAM" then put B3 (Joe Smith on Sparkles) into the new sheet column1 if B3 = "NEW TEAM" then put "B6 on B7" (Jane Doe on Fluffy) into the new sheet in column1 (where B6 and B7 are the strings, obviously).
I haven't figured out how, in my loop, to access the data in (myCell + 1) and (myCell + 2) while I'm on the first cell (myCell). I could do it by jumping back to the Sheet1("B6") and Sheet1("B7") but I'm trying to write reusable code, and this range might change, so I'm trying to be a little more flexible than hard coding in more cells.
I really know nothing about vba so here goes. I would like to enter data in a row with 4 cells of info. then hit enter and return to the first cell and move the row down. all four cells must have data entered. and all four must move down. i tried some code as below i found and i modified but it did not work as expected. this moved the row down when returning the cursor to A2. It also should not copy the data style of the top row.
Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Column <> 1 Then Exit Sub Application.EnableEvents = False
Recently I found this forum through a Google search along with the perfect solution to a problem I was having finding and replacing text based on a table of replacement values. Here is the thread:
Multiple substitute or replace text using a table
I incorporated the SuperSub function that steveorg developed as a result of that thread into my worksheet by creating a new module and pasting the following code into it:
Function SuperSub(OriginalText As String, rngOldText As Range) Dim cel As Range Dim strOldText As String, strNewText As String ' loop through list of old_text used in substitute For Each cel In rngOldText.Cells strOldText = cel.Value strNewText = cel.Offset(0, 1).Value OriginalText = Application.WorksheetFunction.Substitute(OriginalText, strOldText, strNewText) Next cel SuperSub = OriginalText End Function
It works like a charm as a formula, but I also have a macro in the same workbook that, as soon as it selects the sheet with the formula, calls the SuperSub function and starts executing the code as a macro (even though there is no call to this routine in the macro). The macro ran just fine before incorporating the function into my worksheet.
The formula looks like this: =TRIM((supersub( UPPER(E2),rngSubst))) If cell E2 contains the string "101 North Main Street, Apartment 5", it would return "101 N MAIN ST APT 5"
Here is the macro:
Sub Import() ' Sheets("Shoebuy FTP").Select Range("A2:R200").ClearContents 'This is where it jumps to Function SuperSub(OriginalText As String, rngOldText As Range)
The function runs for every occurrence of the formula in the worksheet, and when it is finished, all of the cells containing that formula show a value of "#VALUE!". I should mention that it does not change the formula at all, but I then have to highlight each cell that contains the formula, press <F2> to edit, then <ENTER> to get it to display the correct results again.
It is probably some stupid little thing that I didn't do when I created the function, but I can't figure it out. I have written numerous macros, but this is the first time I have ever created a custom function.
I am trying to set a custom view when exiting, but I keep getting runtime error 424 saying an object is required. I am attempting to unprotect the sheets, check the value of an option button, set the custom view based on that value, and then protect the sheets. Please keep in mind that the user may or may not be on the sheet containing the option button when exiting. Here is the code I am having trouble with.
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call UnprotectSheets 'Unlock to set option button value
With Worksheets("Customer_Info") If btnExistCust.Value = True Then ActiveWorkbook.CustomViews("Sales_Exist").Show Else ActiveWorkbook.CustomViews("Sales_New").Show End If End With
I'm trying to make a converter between about 8 various types of values. These are not units like Km or miles or something like that, but rather numbers that represent a specific "hardness value" on a variety of scales (to name a few: HRC, HRA, K)
What I've been doing so far is plotting the two types against eachother and then getting the best trendline I can so that I can use that formula to convert between the two with relative certainty. (for example, when plotting HV vs HRC my fourth order polynomial trendline with an Rsquared of 1 is y=0.0001x4 - 0.0188x3 + 1.0768x2 - 20.709x + 350.69)
My questions comes up where I was hoping to make a window or box of some sort allowing the user to input a numeric value, then selecting the Input units and the hopeful output calculated units, and have the box spit back to the user the conversion.
I have a userform with a combobox (cmbSearch) , a textbox (textbox1) and a listbox (listbox1)....and a commandbutton (cmbFind). This userform is used for searching records in my worksheet....where user selects a "Search By" category from the combobox and then enters relevent text search criteria in the textbox....and all the search results are shown in the listbox. This part works fine untill someone tries to search a "non-existent" record. e.g. a customer name thats not in the database and it gives an error. I would incorporate in my code to have a Message box pop up on these type of searches saying "textbox1.value Not Found!" insetad of error message.
Private Sub cmbFind_Click() Set c = Range("a65536").End(xlUp).Offset(1, 0) 'write userform entries to database Dim DataSH As Worksheet Set DataSH = Sheets("ComplaintData")
With DataSH .Range("L1").Value = cmbSearch.Value .Range("L2").Value = TextBox1.Text .Range("N1"). CurrentRegion.Clear .Range("A1").CurrentRegion. AdvancedFilter Action:=xlFilterCopy, criteriarange:=.Range("L1:L2"), copytorange:=.Range("N1") End With lstSearch.RowSource = vbNullString lstSearch.RowSource = "outdata"
I have some more questions about excel formula. In one column I have data appearing, as either a 1 or 2, at random as I input it. Each column has around 120 rows. In the row at the bottom I created a formula to calculate how often, on average, the data appears in that column. 1st how do I get each row to represent a date. 2nd how can I get the sheet to show the next expected date that the data may appear in that column, taking into account the date it last appeared and the average between appearances?
I would like to format a row of cells. I want each cell in the row to contain (and show) both an interger and text. The interger will range in value from one to ~10,000. The text will always be two characters long...but these characters will change from cell to cell.
Elsewhere in the spreadsheet I will have a cell (w/ a formula) that counts the values of the intergers in the row, but ignores the text in each cell.
When I click the excel printing icon, I want a userform to pop up in which I make the choice for a certain customview that I build. This customview must then be printout. Note that I want to use the standard Excel print icon, not a custommade one in the worksheet. I figured out some code, but it doesn't work. For instance my if structure returns "false" even if I did click that commandbutton, see the code below.
Private Sub Workbook_BeforePrint(Cancel As Boolean) Static PrintRequest As Boolean formMyview.Show If formMyview.CmdCancel= True Then MsgBox "printrequest canceled" Unload Me cancel=True Exit Sub End If If PrintRequest = True Then Exit Sub End If If formMyview.CmdOk = True Then For Each Myoption In frameViewoptions.Controls If Myoption.Value = True Then..........................
I have a spreadsheet with four columns of data for every month (i.e. January has a Prior Year, Budget, Outlook, and Actual column). I have been trying to come up with a macro or form that will allow me to customize which columns I would like to see and hide the rest. For example, if I chose to see the Budget and Actual columns, it would hide the Prior Year and Outlook columns for each month.
I want to show numbers on my excel spreadhseet in thousands. However, the challenge is I want to show negtive numbers in red within brackets. For instance, I have a number -104,784,089. I want to display it as (104,784) in red color. Similarly, I want to show 577,090 as 577. Is there a customer formatting I can set up in excel that do that?