I am trying to make some additions to this code. Basically what it does is copy some data from another sheet. Now there are two types of copying here.
1. A ‘call value’ from another sheet is copied to this sheet.
2. The formula from the previous cell in the column is copied. (eg. Formula in C3 is copied to C4, formula in D3 is copied to D4, and so on)
So the macro works fine for all cases EXCEPT the initial entry, which will be in Row3, that’s where the formulae begin. So I need some kind of an If-Then statement.. to say if A3 is empty then do the following, If not then do something else....
below is some code from a spreadsheet I am working on,,,,,, as you can see it adds items to a dropdown list in a combo box. This may require an update at another time,,,,, is it possible for the list to be updated from a list in the spreadsheet, therfore preventing people from accessing the code
I saw the below code in another thread that does almost what I need it to do. The only thing is that I need to only select certain worksheets, not all. Is there an adjustment I can make to this code or is there something I can do differently?
Each worksheet has a table on it as well, is there a code I could use to just combine certain tables?
Sub debit1() 'Combine all worksheets to the Summary sheet 'Created by Trevor G 30 June 2011 Dim ws As Worksheet
I have a worksheet that is automatically updated based on actions in other sheets. There are 10 columns of data (A-J) Headings are on row 6 and data starts on row 8. I have created 3 buttons to sort worksheet by client column (B), year-end column (E) and to bring it to original order (by numbers in column A). I have created the following codes that I have assigned to each button however it does not work.
Sub Macroclient() Columns("A:J").Sort Key1:=Range("B8"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom End Sub
On Sheet1 I have a macro link. On the Setup sheet, in cell C14, I have the name I want to display in the link on Sheet1. The wording is changeable in the Setup sheet. This all works fine.
Now, I want to modify it. I want an if statement at the beginning of the macro that says. If Setup, cell c14 is blank, I simply want the macro to stop and do nothing. If Setup sheet, cell c14 is not blank, I want the macro to continue as normal.
I have developed (plagiarized) the following code but it is very slow. how I could speed this up? Eventually, the range may encompass more rows, and there are three separate ranges this code will act upon, so how to speed things up.
Sub DeleteBlanks1() Dim i, Endrow As Long Endrow = Range("K2000").End(xlUp).Row Application. ScreenUpdating = False For i = Endrow To 2 Step -1 If Cells(i, 11).Value = "" Then Range(Cells(i, 8), Cells(i, 13)).Delete End If Next i Application.ScreenUpdating = True End Sub
Attached is an example of the program. The purpose of this program is to allow someone with very little knowledge about the information to enter data. The first tab labeled "Instructions" is will have two macros assigned to the buttons at the bottom of the page. The first macro labeled "Move Data" will move data will clear data in the shaded cell on the "Entry Form" sheet and fill those cells in with '----. The second macro labeled "Generate and Save" will save a master copy of the program, save a historical copy of the program by date, and print/save a pdf copy of the "Reports" sheet.
Each one of the historical (the last 8 sheets of the program) sheet receives data from the "Entry Form" and then moves the data by date into the table for archiving purposes.
If you look at the code for the "Entry Form". I have a couple of subs. The first being Worksheet_change. the first section of this code is to force certain cells to be uppercase. This part of the code is working fine. The second part of the code is the Dim MyValues. This part is working fine also, but...
My issue is... When I run the move data macro from the "Instructions" sheet the Dim MyValues reads these as changes to the cells and wants to push the data to the historical sheets.
What I would like is an IF Statement above the Dim MyValues part of this code that stops the Dim MyValue part of the code running if those values are blank, 0, or have '---- entered into them.
I've selected a case statement (see below) but I want to that code to work automatically. So i do not want to press a button to show a certain value in S2. But I want cell c2 to automatically pop up the value (depending of the value in V4). So kind like an if/then statement.
Sub CASEMEDEWERKER() Select Case Range("F4").Value
Case "Medewerker" Range("S2") = "M"
Case "Interview" Range("S2") = "I"
Case "Data" Range("S2") = "D"
Case "Observatie" Range("S2") = "O" End Select End Sub
I have the following code to execute a connection to a DB and return the result of the SQL statement. I have a variable in the statement that I want to be able to change from user input in a single cell. Unfortunately I am unable to get this to work.
This fails and gives me a debug error, however if I declare the condition I want the code executes fine. This would mean having to update the code every time the condition changed or putting the whole query in a cell on the sheet which I do not want to do.
Example6.xlsx .i just cant remember what it is im meant to be using as i havnt done it since school. basicly on the attached spreadsheet, there are 2 tabs. the common column is Contract number, if the data in the contract number column matches then i need the figures for 20/40/TEU to show against the Special aggreement by code tab.
I am trying to figure out a formula for the boss, but I just cannot wrap my mind around what I need to do. I’m sending out an S.O.S.! If you can figure this out, please respond.
=MIN(b4*12-25%,b4*12-500) + MIN(c4*12-25%,c4*12-500) fills in the cell with the correct answer, but not quite the answer that I need. Let me illustrate:
The first part of the formula is to take b4 times 12 minus 25% --if this amount is less than or equal to 500 then use the amount. If it is more than 500 then use 500.
(b4*12-25%) 234.25 * 12 = 2811 – 25% = 2811 2811 - 25% = 702.75 500 Since this amount is more than 500, 500 should be used instead. ~~this is actually the amount that I want to show in the cell, the amount saved~~ 2811 - 500 = 2311 ~~this is the answer that is showing up in the cell, which is not quite what I need. ~~
I want to find the answer to this question: b4 times 12 equals ?, subtract 25% or 500 from that, insert this answer into cell.
One of my biggest problems with this is that I can’t even figure out how to say what it is that I need. The formula is doing exactly what I'm telling it to do and I can't even figure out what it is that isn't working. This formula is confusing my brain…!
I have a table upon which I wish to perform vlookups, however I need to be able to adjust the range that the vlookup searches to include any data that may be added in the future without having to go into the sheets and change the vlookup.
Example
Name DOB Tom 01/01/81 Dick 02/02/82 Harry 03/03/83
if someone then adds
Fred 04/04/84
I would like the vlookup to automatically accommodate the extra entry without needing any changes.
I've seen this done on data validation with the following formula:
I am using the =today() function to places todays date in a cell...how can I get the next to cells to automatically display dates that are 2 months and 4 months later without having to change them manually every day. FWIW, this is for an certification expiration chart.
I'm working on a spreadsheet to rank stores based on how they perform in certain metrics. These metrics are weighted, and occasionally a metric for a store will get waived. I'm having trouble figuring out how to handle this without making a custom formula for each occurrence.
I have the a file that is downloaded from AS400. The time column is presented in 24 hours format in column D. For example : 92114 means 9.21 am. I need a formula to adjust the date in column B based on the following criteria:
1) If the time is after 2.00 pm, adjust to the next working day taking into account public holidays in column H
2) If the time is before 2.00 pm, remain the same day
3) if the time is after 2.00 pm and it is Friday, adjust to the next working day taking into account public holidays in column H
I am trying to figure out if something here is even possible. I have an order form and a price list. I get different pricing based on the quantity I purchase. I would like to have the price auto populate based on the quantity entered. The price will be on another sheet.
I couldn't find a lot of info but I also may not know where to look.
example:
if (b2<62) then price is ($sheet2.b2) if (b2<124) then price is ($sheet2.c2) if (b2<372) then price is ($sheet2.d2)
I currently have a tab that is contains a macro driven query pulling data from an access database. However, I have selected the option to add new rows for new data which in turn causes my sumtotal at the bottom of the sheet to be over ridden by this new data. My question is, could there be a way to have the total row move down as new rows are added from the query (formatting and all)?
I've got a Table with 3 columns. (Goods, Amounts, Type). Every column has similar filter. Can I from the second and third filter remove the single item selection (red square below).
I am created a very simple account plan for colleagues in my team which is proving to be very poular with the exception of one thing.
I created the plan which is around 30 sheets on my laptops highest resolution setting. 1400 x 1050. Only one other colleague has this res available so of course when the rest of the team open it, it looks huge.
I need help with this as I'm still very new to excel, although learning very fast!
My preference for finding a fix for this would be to insert a scroll bar on each sheet that, when scrolled, changes the zoom of the page between a range of 50% and 100%. This may not be the best way of doing it but I'm trying to make the sheets as simple to use as possible as some of my colleagues are only just compuetr literate, let alone excel competent!
i do not know or can seem to find out is how i can program the team list in the table to adjust each team position auto-maticly relevant to each teams points scored
I have a macro on a workbook that when I hit the button it asks for the date of a register sales journal that I want to import. Then it asks for the cash in drawer amount. Then it puts in all of the data into the sales spreadsheet. Now I want to use the on time method to bypass the button and the entry of the date, and even get rid of the cash in drawer. I want to make the macro run at 9:30 every night. I want it to use the date on the computer to tell it which date to find? Here is the code I'm using! button macro
' Button2_Click Macro
Sub Button2_Click() ImportData End Sub
Here is the main macro Function FileExists(sFile As String) As Boolean Dim iLen As Integer On Error Goto NO_FILE FileExists = True
