I have a MROUND function to round a cell value to multiples of 20 but to also return 20 if the value of the cell is less than 20. How do i combine the MROUND function with the if statement to give me that result?
Let's say for instance I have a vlookup that displays a the quantity of available items in stock pulled from a column in another workbook. Occasionally these numbers are negatives as they are backordered I would like to display these values as zero.
I almost get my eyes killed whenever at the end of the month
Statement
A B C D
[Code].....
I have issued invoices with reimbursements to Mr. A so on 30th of the month, I issue a statement to summarize my invoices and reimbursements as above example. Note : An invoice and a reimbursement are always in a file(sheet1 and sheet2) and a solo reimbursement is a file(sheet1). "Invoice"(Folder) in drive D contains Invoice00100(SI11) & Reim00111(SE55).
Samples of invoice and reimbursement Invoice(sheet1)
A B C D E
[Code]....
Solo reimbursement is the same as above reimbursement table
code that can extract all excel file in "invoice" folder and auto create as my first above table. I would like to manual select the files which are taken to extract to the statement.
I am currently using an Intersect statement in a worksheet module to perform two things: 1. Insert a time stamp into row 2 when row 1 has a price inserted 2.To clear that time stamp if the price is deleted at some later date.
My problem is with the time stamp value being deleted by the user. If I try to clear the price (now that the time cell =empty) I get a Runtime error 91 - Object Variable or With block variable not set.
I would like to convert this code to a select case statement but I'm not sure how to do this in this situation. Would error coding be appropriate in this instance?
I'm trying to set up an if statement that will recognize that if a cell is FHR it will do something...but if it's PHR it will do something else. I think I found the place where I keep getting an error but I'm not sure how to go about fixing the issue.
I am attempting to use a previously Set variable as part of the next Set statement, pretty unsuccessfully at present.
My purpose is trying to look up
Code: tb_SelJobID.Value
from a userform in Col Z then look across the row to Cols D,I,N,S & W (different types of work) to see if
Code: TbSelYr.Value
matches the year selected then insert a formula in the row to the left. Then loop down to the FinalRow.
Currently my Set Found1 statement does not recognise my Found10 value. I know it will be my syntax as it always is. I have cut down the following code to display where the problem areas are, Found1 thru 5.
Code:
Sub CmdGo3_Click() Dim Row As Range Dim FinalRow As Long Dim Found1, Found2, Found3, Found4, Found5, Found10 As Range Application.ScreenUpdating = False
I have an Excel Sheet which I use as Database. The database has 11 columns and I insert data with the following function:
Code: Sub testInsert() Dim adoCommand As New ADODB.Command Dim sQuery As String Dim i As Integer
Dim strTest As String
strTest = "test"
[Code] .......
Now I want to retrieve this data. i.e. I want all F1 where F2 and F3 are 0 AND I want them ordered descending. I'm trying to achieve this with:
Code: Sub testSelect() Dim adoCommand As New ADODB.Command Dim sQuery As String Dim mrs As New ADODB.Recordset Dim strTest As String
strTest = "test"
[Code] ....
The result I am getting looks like this: 9 8 7 6 5 4 3 2 15 14 13 12 11 10 1
I assume, that the data is interpreted as String instead of an integer. But I explicitely stated the data as Integer when storing the data into the DB.
I have created a very long switch statement, which is too long to be placed in one row in VBA. I have attempted to put a space and underscore at the end of one line and continue the statement on the row below by placing a comma at the start of the second line. VBA will accept my efforts, but when I run the statement in the immediate window, the following error appears.
"Invalid procedure call or argument"
I understand that there are certain rules where I can split a switch statement onto two lines, yet I do not know what they may be.
If A1 shows 10:00am and A2 shows 4:00pm, then A3 calculates the total number of hours: =(A2-A1)*24
But if A1 shows "Off," then A3 shows 0: =IF(A1="Off",0,(A2-A1)*24)
Now, if I want to change "(A2-A1)*24" to another if-statement, how do I do this? I can always set up a hidden cell (A4) that contains the results of the first if-statement, and then say: =IF(A1="Off",0,A4). But can I do this without going through all the trouble of setting up hidden cells?
Each row represents a call. If a call in column A equals "CW" and it has the highest duration (H:MM:SS) value in column B, then provide me the date (MM/DD/YYYY) for that call that is stated in column C.
i.e. Column A --- Column B ---- Column C AB ------------ 0:02:22 ----- 04/14/2007 CW ----------- 0:03:13 ----- 04/16/2007 CW ----------- 0:01:42 ----- 04/13/2007
Thus, the value that should be returned is "04/16/2007".
I am trying to have a cell in sheet "Summary" count the number of cells in column DX of sheet "Analyses" that are greater than 0, provided that the value in column A of "Analyses" corresponds with the value in B8 of sheet "Summary."
(In "Analyses," there are 106 subjects, each taking up 64 rows. So, columns 1-64 correspond to Subject 1, columns 65-128 correspond to subject 2, etc. In column DX, each subject has 64 values that are either 0 or greater than 0. In "Summary," each subject has one row that summarizes the 64 trials. I want a single cell in the "Summary," sheet to reflect the number of times each subject produces a value greater than 0 in column DX of "Analyses.") I tried using this formula, but it did not work correctly:
(Summary!B8 = 1, so I am trying to calculate the number of values in DX that are greater than 0 only for subject 1.) When I press enter, this yields a value of 384. This is impossible, given that subject 1 only has 64 possibilities of yielding a value greater than 0. Subject 1 has 2 values in column DX that are greater than 0. I tried making this an array formula by pressing Shift+Ctrl+Enter, and that just gives me a #VALUE! error.
I have 12 workbooks (one for each month), they have a list of assets to our company and a running total of all expenses for the month that they incurred. The 13th workbook is a yearly analysis of each asset. I need to transfer all charges into this worksheet.
So for example:
Worksheet 1 (January)
Column A Column H Asset # Total Expense 5021 $3,041.52 970289 $242.08
Worksheet 13
Column A Column B Column C Column D Asset # Asset Description January February 5021 Rivet Machine $3041.52 $345.65
So hundreds more machines for twelve months can take awhile to populate this info and very confusing when dealing with separate worksheets.
I need to write IF statement with 4 criteria, or at least I think IF will do the trick. It would be difficult and not very visual to describe my question here, therefore I have attached a dummy workbook with the comments. Basically I have got 2 values in 2 columns. Next 2 columns will hold letter "x". There are 4 possible variations how "x" will appear in those two columns: first, second, both or none. So depending where the "x" is, I need to return one of the 2 values.
the biggest problem is that this formula does not work as it is. Or is there some other function than networkdays that i could use?
The purpose of the formula is to count how many "objects" that are in service have been there for over 20 days. There a a few other criteria also, but sumproduct takes care of that. Can networkdays even be used the way i´m trying to?
I am trying to use SUMIF as part of an IF statement, What I am trying to do is, for example, if a column has two values M and P, then SUMIF only those rows that have a corresponding M, and not calculate the P's.
I am so frustrated to combine several spreadsheets into all in one, and seperate all in one spreadsheet into several with the same header frenquently. Pls check the attached example excel spreadsheet., could anyone teach me how to program in vba to achieve the result as above?
- Table 1 has ~1,600 records (name of game, meta score, user score) - Table 2 has ~3,000 records (name of game, genre, publisher)
I want:
- Table X has ~1,600 records (name of game, meta score, user score, genre, publisher)
Basically, I want to take the genre and publish columns from Table 2, and add it to Table 1. However, I want to ignore the extra ~1,400 games that Table 2 has.
I'm nearly a complete noob when it comes to excel. Noob to the point where I don't even know what this thing is called, so I don't even know how to search for it properly! I know something can be done since there is a common column between the two (name of game).
Edit: Also, I think it'd be easier if I were able to attach my excel spreadsheets but I don't even know how to do that