Sub testDateCompare()
Dim nmiDate As String
nmiDate = CDate("23/4/08")
Dim origDate As String
origDate = CDate("23/04/2008 15:05")
origDate = Left(origDate, InStr(origDate, " "))
Dim minDate As String
minDate = WorksheetFunction.Min(origDate, nmiDate)
MsgBox minDate
End Sub
Giving '400' error!?
I have a CSE formula that's been working great since day one.
In B202 I have the name Magnecraft.
In Row9 I have all the possible sources for this item starting in D9:CB9.
The fx in C202 is as follows:
{=SUBSTITUTE(MCONCAT(IF(INDEX(D202:CC202,MATCH(B202,B202:B202,0),0)="X",","&$D$9:$CB$9,"")),",","",1)}
I have an "x" in E202 and I202.
C202 should then show (in this case) Abacus and A/E as the 2 sources available.
I'm getting an #NAME? error.
And if I insert a new row, all the data in each cell in C"C turns to #NAME?.
I am using VB6 to automate excel. Ofcourse, the code needs to work with 2007 and all lower levels. When trying to save the excel doc in 2007 I get no errors. However, in 2000 (using the same code) I am unable to save my document.
Here is the
OfficeDoc.SaveAs FileName:=NewFileName, FileFormat:=FileFormatValue
It fails with error 1004: application-define or object-defined error.
I have checked to make sure no other office documents are running in the background under task manager.
I'm getting a mismatch error on my If activecell.value > 5 Then portion of this code. I'm trying to say if the value in column AG is greater than 5 then create an email address from the first and last name. I've even made sure to copy and paste the formula results as values.
Code:
Sub CheckPFPDates()
Dim OutApp As Object
Dim OutMail As Object
Dim cell As Range
Dim emails As String
Dim salution As String
Dim currenttime As Integer
[Code] ........
When i try to add comment to range of cells, i am facing error.
rng.AddComment "Comment"
My indirect formula doesn't work when I have the entire path defined. I have the entire filepath specified (which will never change), and then I have cell A6 with the tab name of the worksheet on the workbook
.
Workbook 1 named 'Testme' contains the table with range a2:b5
Worbook 2 contains the formula
I put this formula into workbook2 (the tab in the example is named hitab & I put that name in cell B6)
Here is what I have:Does not work even when other workbook is open
=VLOOKUP(B6,INDIRECT("'H:CUSTOMERSCurrentClientAMRO and General SuppliesBearings, Motors, PTsSavings Tracking[Testme.xls]"&A6&"'!a2:b5"),2,FALSE)
This DOES work when workbook is open, but not when it is closed (which would seem logical if when the workbook is closed, the formula needs the exact path)
=VLOOKUP(B5,INDIRECT("[Testme.xls]"&A6&"!$a$2:$b$5"),2,FALSE)
I can hard code everything except the tab name. Why doesn't Indirect recognize an entire file path and also why does it give a REF error?
- I did a search and found that some people said to use the add-in Indirect.exe. I followed the link and the page was no longer found.
I am trying to use the Special Cells Goto and Paste, but I am getting an error saying 'this operation requires the merged cells to be identically sized' when I do the Copy the value that I want, Goto Special|Constants, then Edit|Paste Special|Values|Multiply. The cells containing constants are selected and if they are together, e.g. B7 & B8, then there is no problem, but if they are apart e.g. B7 & B9, I get the error.
View 5 Replies View RelatedI am trying to used match function in the code but I am getting the following error:
Run time error '1004'
Unable to get the match property of the worksheet-function class
Here is the
I am very comfortable with using sumif statements although I have tried it link two data from 2 different sheets and I keep getting returned #VALUE!
I get a message "Unable To Read File" in a pop up box before fully opening.
The following code is giving me an object-defined or application-defined error:
wsDst. Range("E" & NextRow) = "=VLOOKUP($B" & NextRow & ",PriceList!$A:$IZ,MATCH(HLOOKUP($I" & NextRow & ",PriceList!$C$3:$IZ$3,1,1,PriceList!$A$3:$IZ$3),FALSE)"
I created several macros objects to do simple hide columns, filter on, filter off, etc. type functions, using the Tools / Record Macro feature. Then discovered they won't work in a protected worksheet. Is there a way around this situation, other than turning protection off? Here's an example of the code for one of them, although I get the error or every attempt to perform a macro once the sheet is protected:
View 4 Replies View RelatedBelow there is code for some inputboxs as well as some if statements on their results. The Exit sub situations work fine but If i do actually put a valid answer in the secod input box then it results in a mismatch error on the OR statement... why the first inputbox works fine but the second one gives me a type mismatch error when a valid input is given? Also if there's a more efficient way to do this please feel free to correct the code.
View 4 Replies View RelatedI must be using hte wrong command or I am dumb.
Cell B8 I have a validation list of either Hub, Router, N/A
On cell C8 I want if (B8=Hub,3COM,0) but I get a NAME error. Is it because it won't work with letters but it can make the calculation?
IM USING VLOOKUP TO FIND a VALUE IN AN ADJACENT CELL, BUT IF THE VALUE ISNT LISTED THEN I WANT IT TO DO NOTHING. EXAMPLE
=VLOOKUP(1100,Tuesday!A:F,6)
BUT IF THERE IS NO 1100 TO LOOKUP THEN GIVE ME A 0 VALUE OR DO NOTHING
I'm working on a spreadsheet and I want the letter "E" to have a numerical value of zero, and not necessarily in just place as my sheet will be regularly updated. If you're a fan of golf, you will understand what I am doing. I'm basically making a custom leader board for a fantasy golf game that I'm doing, and I'd like for the letter E to represent the value of zero when I am adding up the scores.
For example
A1: -1
A2: -3
A3: 4
A4: E
A5: -2
A6: 3
A7: 3
A8: -5
Total: -11 (have a formula set up to only count the best 4 scores).
I want to be able to add up those columns, and since only the lowest 4 scores count, I need E to equal zero. I get that I could just put 0 there, so I'm being a little picky (in golf E represents Even par, essentially a value of zero for the purposes of this game). There are 20+ teams in the game so I need E to be recognized as zero in each of those fields.
Sheet 1 has 3 columns: grade, step, NBS
Sheet 2 has 3 columns: SM, grade, step
I want to add a column NBS to sheet 2 for that exact grade and step, from
the data on sheet 1.
I tried the following formula but it gives me #value!.
=INDEX(sheet1!$C$2:$C$6,MATCH(B2&C2,sheet1!$A$2:$A$6&sheet1!$B$2:$B$6,0),3)
This formula has been working fine for me...until I have the condition where no value >0 has been entered in my LOOKUP range as yet. Or at least that is what I have managed to test so far. I've verified that my values have the same format (General) and I've verified that my ranges are all of the same size.
I'm looking in one worksheet for a work order meter reading that will be entered in another sheet when the Unit # for the equipment matches. I discovered if there is a work order entered for and no meter reading has ever been recorded yet, I get the #N/A error. If I go in and put a fictitious meter reading in of 1, the error goes away. It doesn't like a blank or zero meter reading and I need to know how to get around this. Here's the current calculation:
I referred the above two articles and accordingly created Personal.xls file in XLSTART folder. Here is the screenshot: http://dl.getdropbox.com/u/872430/excel.png I have done nothing else to the workbook except what is seen in the screenshot.
I have tested the funtion by using it as a formula in the Personal.xls file.
When I start Excel, Personal.xls does start up but the funtion is not available to my in my other workbooks. Is there anything that I have missed out?
I have a very large excel spreadsheet with many formulas in it. Some of the formulas are giving a 0.00 value even when there should be a $value. I don't know why this is happening? It is not happening to every formula and it is not consistant within the same row or even the same column. Most of these formuals have been used in this spreadsheet for years by many different people with no problems.
View 4 Replies View RelatedI noticed that when I clicked F4 Button on the keyboard it shows the screen resolution(with screen 1 and screen 2) as though one wants to use projector. The keyboard's configurations seems different from what I am used to.
What I can do to resolve this as I wanted to use the F4 Button referencing($).
I have a problem with isnumber giving me two different results. One time it will evaluate the following as true:
ISNUMBER(0)
and in the same sheet it will evaluate the same as false.
I need ISNUMBER to NOT recognise "0" as a number all the time.
I've got a Macro that generates three reports for me, one at a time.
After a report has been generated it creates an Email and attachs the report to the Email, I then check the report is ok before sending out.
The problem is, that once the first Email has been generated Excel is pushed into the background and the macro sloooooooows down.
I believe (though I will quite happily be wrong) this is because Excel is no longer the PC's main concern.
What I was wondering was, is it possible to bring Excel back to the foreground once the Email has been generated.
I was looking at the other posts and this should work. Why the bleep can't I get the MROUND function to work. Simple scenario...I need to round UP numbers to the nearest 50. 320 should bring back 350, 351 should bring back 400.
For example, if this is in cell A1, I think the formula should be =MROUND(A1,50). Yet everytime I use the function, it gives me NAME#.
Cell A3 on my SHEET1 has the following formula =DATE(YEAR(C1),MONTH(C1) + 1,DAY(C1). It goes on like this for the next 12 months (+2, +3, +4....).
Column B on my SHEET2 has One of the five product codes (A, B, C, D, E) and Column D on my SHEET2 has manually entered dates
I created the following condition for each product code under each month in SHEET1
=IF(MONTH(Sheet2!$D$1:$D$39)=MONTH(Sheet1!$A$3),COUNTIF(SHEET2!$B$2:$B$39,"=A"),"")
The formula only works for two product codes (A and B), and then yields blank cells.
I need to find the which two columns that give the greatest combination.
Row 2 shows some possible combinations, a total of combinations is 21.
Attached File : test.151515.xlsx
I have a fomula field in row 67 and 68 .Row 67 calculates the average and row 68 calculates the improvement i.e. average(row 68)- last row inserted(row 67). The values for improvements are not showing correct value. C68 seems to give correct value but B68 and D68 is giving wrong value.
B68=4.8-4.5 =0.3 but it shows 0.4
C68=14.9-15.1=-0.2
D68=11.3-10.2=1.1 but it shows 1.0
I have attached the sheet also.
Sheet 1.xlsx
I am making a company wide estimate sheet and having trouble with the drop down box. I have a drop down to pick the branch, but when I name the source it only says branch, even though I named the data on the branch sheet. I would like to be able to pick the branch initials and have it populate the phone and fax number under the main heading but am getting a error there.
Also for the labor is it possible to have it insert the correct labor into the formulas at the labor lines according to which branch is picked from the drop down.
Find enclosed a chart wherein there is a super user of a particular system under him there are 5 users (can get increased also), now every user including super user is having a specific function to perform and a specific area to operate. i want to give a separate hyperlink to every user for describing his role & responsibilities and to list his dos and donots.
Sample.xlsx
how to give attractive colours to my ordinary macro buttons to which we assign macros
View 2 Replies View RelatedSo I am using a macro to do a particular task. Part of the macro's process is to delete some unwanted rows.
The code below was for my original macro which works absolutely fine:
Note: The ranges are fixed (i.e. column "AT" is reffered)
Code:
Sheets(1).Select
Dim Current As Worksheet
Dim Flt As String
Flt = Me.TextBox2.Value
For Each Current In Worksheets
With Current
With .Columns("AT")
.AutoFilter field:=1, Criteria1:="