In row 6 column O I need for the formula to look at column I and determine if the month is 1, If yes then I need for it to determine if the date is one of the following,
Day(I6)>=1,Day(I6)<=5), If TRUE Then do the following
Cells(J6)*1.04)*2080/12
If False then
ElseIfDay(I6)<=6,Day(I6)=<12, If TRUE Then do the following
Cells(J6)*43.33)+((J6*1.04)*129.99
If False then
ElseIfDay(I6)<=13,Day(I6)=<19, If TRUE Then do the following
Cells(J6)*86.66)+((J6*1.04)*86.66
If False then
ElseIfDay(I6)<=20,Day(I6)=<26, If TRUE Then do the following
Cells(J6)*129.99)+((J6*1.04)*43.33
If False then
ElseIfDay(I6)<=27,Day(I6)=<31, If TRUE Then do the following
Cells(J6)*1.04)*2080/12, but put this into column P
If row 6 Column O if the month is greater then 1 then I need the formula to do the following,
=J6*2080/12
What I am trying to do is have excel take columns O through Z look back to column I and determine which month the performance review is due in. Then I need for it to determine which week the increase rate will be effective for. So for each month the “If the month is >1” would change to 2,3,4,5,…..and so on.
In addition I am going to have the formula look at the shift and if it =3 then (J68)+1.00, but for the increase I have to have it -1.00 then J6*1.04+1.00. I will also have to create a table that holds the max rate for each grade, and then figure out the formula to figure out this new rate.
I have a string of letters in cell column L20 through L3019.
The letters are A, B, C, and R . No other letters.
I wanted to find out if it is possible to remove any duplicates when starting in cell L20 going downward.
For example:
L20 = B L21 = A L22 = A L23 = B L24 = C L25 = C L26 = C L27 = C L28 = B L29 = R L30 = A L31 = C L32 = C L33 = C L34 = R L35 = R L36 = B L37 = B
In the range L20 through L37 all the letters have appeared so my goal is to create a formula where the outcome is BACR. Starting with the first letter B in cell L20
Cell range L20 through L37. Looking for the next letter which would be either A,C, or R. Since we already used the letter B. In this case it's the letter A ABCR
Cell range L20 through L37. Looking for the next letter which is the C or R. In this case it's the letter C CBRA
Finally range L20 through L37. Looking for the letter R which is in cell L29. RACB
I'm trying to develop a new daily timesheet for my production workers, where non-production items are recorded in 15 minute intervals. The user would put in "Clock in" by the corresponding time, and the same for "Clock out" at the end of the day. Any non-production items will be type in next to their appropriate time. Since clock in and clock out times will vary, I need to set up a formula that searches the array of cells for the day, finds the "Clock in" and "Clock out" values, and counts any blank cells in between them. Basically the blank cells will equal production time, and the result of the Count function will be multiplied by 0.25 to get the hours.
I am having a very difficult time finding a way to set the "Clock in" and "Clock out" cells as the range for the Count function, because it won't always be the same cells. What would be the best way to automatically have excel find the cells containing these values and set them as the range criteria for a Count function?
The formula at the bottom was one of my initial attempts, but it didn't work. I took out the '=' for the screenshot, so that wasn't the problem.
develop a macro to send e-mails upon certain criteria, I then wanted to add a layer to the macro which I have, and it works... sort of. I'm wanting the Macro to send a specific e-mail if the dates fall between a certain range, a different e-mail if they fall between another range, etc... Here is the code I came up with:
Private Sub Workbook_Open() Dim LR As Long, i As Long, s As String, sb As String, sc As String, eml As String With Sheets("Vendor-Rltshp")
I have an enquiry where I'm trying to order error messages which occurs in if, ElseIf and Else Statements. The code I have works. However, I would like the error messages to be checked in a particular order. I tried rearranging the conditional statements but had no success. Here is my
I started changing it and then realized that I can keep going by adding new elseif's but i think an array would be a better option(i think). But i am really a monkey see monkey do kind of vba user. i can backwards engineer but cant come up with it.
here is code
Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("$z:$z")) Is Nothing Then If Target.Value = "NO CONTACT" Then Target.Offset(0, 1) = Date + 1 ElseIf Target.Value = "NO CNTC FCL" Then Target.Offset(0, 1) = Date + 1 ElseIf Target.Value = "NO RES" Then Target.Offset(0, 1) = Date + 1
you see i keep adding "results" and assigning lift dates. what i would idealy want to do is take my list (that i use in a drop down box in $z) and next to it assign the number of days i want to set the next call date(the offset 1 cell) to be. then have the code just read cells a1:a10(the results) and assign the corresponding b1:b2(the amount of days to wait to call). I can keep adding elseif's but then if i decide to add new results codes or change the days from 1 to 5 i have to go back to the code.
I have created a user form with a combo box and three text boxes. The drop down box is populated via the initialization of the form; code is posted below.
the "add item" names are names that are already on the worksheet in column 1. What i have done is used the command button "submit" to populate the information from the textboxes to the worksheet.
what i would ike to do is populate the empty cells below this area with the information that is in all three text boxes and the combo box. for the IF / elseif statments i have used the combo box as the condition. So the quandry is I cant input the information into the empty cells, i have tried a few things, my vba is very basic, as you can tell ihavent even DIM'd anything (i think that is another thread though :D )
here is the initialize code and below is the "submit button" code.
Private Sub UserForm_Initialize() 'sets values for text boxes cbomaterialdiscription.Value = ""..........
My code is below. See how I have repeated the same code over and over for each row. Is there a shorter way to write this, perhaps a cleaner way. Im not an expert and I write what I know and most of that is long code that can be shortened.
Basically it is a if statement saying that if the selected cell falls between 1/01/06 and 31/01/06 then Jan would be selected. The end part is not a problem; I’m just not sure how to write the one line of code that would test if the cell falls between the two dates. I attemped to create it as shown in the code attached below but wasn't successful. I used an else if statement to test the other 11 months.
Sub test() Dim SelectDate As Range Set SelectDate = Range("SelectedDate") If selectedDate >= 1 / 1 / 2006 And selectedDate <= 31 / 1 / 2006 Then ActiveSheet. PivotTables("PivotTable1").PivotFields("PnLDate").CurrentPage = _ "Jan" ElseIf selectedDate >= 1 / 2 / 2006 And selectedDate <= 28 / 2 / 2006 Then
I have a column with number of Km from location x to location y (A), and a column with fuel supply (B). I what to know how many km make the driver after the fuel supplyes (787 Km - for exemple from bellow).
I was thinking to SUM values from column A starting ROW 3 (column B) (first cell non blank), but i don't know how to match SUM and ROW functions.
I have the problem to get the starting and the ending time in a timetable work sheet, adding the starting hour in the first cell and in the second the ending.
1) I have added multiple command buttons as options within different categories but when I select one, all others are reset. Is ther a way to section off the command buttons into groups so that say the first three act together, the next two act together and then the last four act together, allowing a total of three options within three different categories?
2) How do I get the options from the userform to the worksheet?
I have Ctrl+x to run a macro and it is not working. I have used it before fine, but made modifications this time and am having problems now. I'll just show the code for now and can give more details if the fix is not obvious.
I want to build a Macro that basically moves right of the cell I am in a few cells and then copies and pastes this info into another worksheet and then prints this worksheet.
However, I can't seem to build a macro that will run from whatever cell I am in.
Everytime and try it just goes from the cell I was in when I recorded the macro.
For instance, if i build the Macro in row 1, but then want to run the marco in row 23, it keeps going back to row 1 whenever I press play.
I know there must be a way of telling it "move right 5 cells from whatever cell is highlighted when the Macro is run...etc"
I need some code to add to a macro that will delete customer account names that start with an "A" in column C. Specifically it needs to find customer names starting with the letter "A" and delete that entire row.
I need the formula for statistical data analysis for a research project i'm helping out with, and this is too complicated for me to know where to even begin searching for the information. The problem is this:
I have a sheet (called Sheet1) containing 9009 rows of information on columns A to AL. I am interested in searching for the starting sequences of codes contained in column AI and returning values on the same row but in a different column. The formula that works so far is this (note: I use Excel 2003 and a swedish version, translated the formulas though)
I havent come up with this formula entirely on my own so dont think my expertise is this high In any case, this formula gives me the value of the A-column in the same row as the first N06 found in the AI column. If N06 isn't found anywhere in AI, it gives me "error". The problem is this: I want it to "continue" searching column AI for strings starting with N06 and returning the values in the A-column for the following N06s found. I basically want to wash out all rows of information that dont contain the string N06 in AI, and get the new information on another sheet.
To make it even clearer: I want to have all values on column A on a new sheet, but only where the AI value on the same row starts with N06. Eventually I want other columns as well, and also search for other codes, but that should be easy to figure out once I get the initial formula.
I am using the INDIRECT function to get data from sheets with different names, with the format INDIRECT(SheetName&"!$A$1"), with SheetName defined as the contents of a particular cell which contains the name of the sheet. This works fine with the exception of sheets which have names starting with C01V. Is there something special about these names? If I change the 0 to O or the C to A, B or D, there is no problem. Sheet names C0, C01 also don't have any issues.
I'm trying to sum a range of data consisting of 12 cells (each range is in a unique row going across adjacent columns). The range of data to be summed is based on a lookup value which is a column header - I want the range to start with this lookup value. In column B of my spreadsheet, I have a 'First Activity number code' going down...In Row 4, I have the same First Activity Codes going across the row (the column headers). In Column U, going down the column I am looking for my formula.
So For example, in row 5 of my spreadsheet, I am looking at 73 as my base code (the value in column B), and I want a range of data summed that lies in the same row where the column headers are 73 - 84 (12 rows) - always starting summing the range with the same number that is in column B. So in row 18, 78 is my base number; so I would want the sum of the numbers in the same row where the column headers are 78-89 ( 12 columns).
I start an Excel program from a shortcut in different folders. I know that I can find the path of the Excel program in "Parent.Path". But I don't know how to find the path of the folder the shortcut was in. It should be findable because when I try to do something like <Save>, Excel knows the correct folder there.
An example: I open Excel in "Folder 1" using a shortcut to the Excel Program in "Folder 2". Parent.Path tells me "Folder 2". How do I find out what "Folder 1" is from inside Excel?
I have a form pop up when user runs a macro and it populates the form beginning with ActiveCell. How do I always populate the form beginning from column A (row based on ActiveCell)? It would be really helpful if I could just set cl to take the ActiveCell and back it up to column A.
So it will work much in the same way as match does for VLOOKUP ($A2,'Sheet 1'!$A:$FG,MATCH(AP$1,'sheet 1'!$1:$1,0),FALSE) but the array needs to change.
By doing this my vlookup will work automatically depending upon the header.
I would like to create a macro that deletes all rows starting with an open parenthensis in column B. The contents of what is in parenthis varies from 2 letters up - so anything starting with a parenthis is sufficient criteria.
After the initial deleting (mentioned above), would also like to delete (from column B again), rows containing specific, multiple phrases.
None of the functions I have see so far will facilitate this...
