Why Doesn't My If Function Print An "X"
My function is Finding the inputted last name on another sheet then looking up the date at the top of the column and comparing it too the current date, However I can't get the current cell to = "X" when they do equal.
Private Sub ReturningRush_Click()
Dim Value7 As String
Dim strCheck As Range
Value7 = InputBox(prompt:="Enter LAST Name")
With Sheets("Attendance")
Set strCheck = .Range("B6:B200").Find(what:=Value7, LookIn:=xlValues, Lookat:=xlWhole, _SearchOrder:=xlByRows,
SearchDirection:=xlNext, MatchCase:=False)
If Not strCheck Is Nothing Then
Application.Goto strCheck
ActiveCell.Offset(0, 2).Select
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Macro To Print Active Area Excluding Cells With Forumula That Doesn't Return Value
Trying to put together a macro that looks down active sheet for all cells that contain a value, sets a print area and then prints ! Is this possible? FYG, I have a column that run from 3 - 2000, which contains a formula, which may produce a value depending on corresponding cells. I used this code from a post on a similar topic, but excel is complaining code in bold Private Sub Print_Area_Click() Dim lastCell As Range Set lastCell = Cells.SpecialCells(xlCellTypeLastCell).Offset(1, 0) Do Until Application.Count(lastCell.EntireRow) 0 Set lastCell = lastCell.Offset(-1, 0) Loop ActiveSheet.PageSetup.PrintArea = Range(Cells(1, 1), lastCell).Address End Sub
View Replies!
View Related
"Run-time Error '1004', Method ' Range' Of Object '_Global' Failed"
I keep getting this "Run-time error '1004', Method ' Range' of object '_Global' failed" Here is the code that has the problem: Option Explicit Dim i As Long Dim j As Long Dim lDup As Long Dim lRow As Long Dim NoDupes As Collection Dim rRng As Range Dim Rng1 As Range Dim Rng2 As Range Dim Rng3 As Range Dim Swap1 As Variant Dim Swap2 As Variant Dim wks As Worksheet Private Sub UserForm_Initialize() Call DefaultSet For lDup = 1 To 3 Call NonDuplicatesList(lDup) Next lDup End Sub..................... It does not even loop once though the original UserForm_Initialize For/Next loop.
View Replies!
View Related
Match Function Doesn't Find Values
I've been using a Index and Match Functions to locate values, What I'm trying to do is as follows: Column A contains x values and Column B contains another value A length is entered at the top of the worksheet, when the x value is greater than half the length the values will be mirrored about the centre point. To do this i'm doing x-(length/2) to give the corresponding value of x on the other side of the length I can do the If functions etc to sort all this out, but the match function isnt working, see attached, it isn't finding values which are in the list, when i use 0 for exact match, i've tried using 1 but it still isn't finding the correct values.
View Replies!
View Related
UDF Working When Called From Sub, Doesn't Work As Worksheet Function
I have written a user-defined function that searches for a small range within a larger range. The function requires two input parameters: the range you are looking for, and the range you want to look within. For example, I might look for the string of values in cells A1:D1 in a larger range E1:H20. The function returns the row number in the larger range where the smaller range is found. My problem is this: The function is working fine when I call it from another sub procedure. However, when I try to run it as a worksheet function, I get a "#VALUE!" error. The function pops up in the "insert function" menu, and it prompts for the two input parameters.
View Replies!
View Related
Nested IF(AND Formula Error Message) "You've Entered Too Many Arguments For This Function"
I am entering the following Nested IF(AND statement into a cell and getting a message stating "You've entered too many arguments for this function." Any idea on what formula I can use to get the needed information into this cell based on the fact that there are different inputs that can be entered into the related cell that will trigger the needed respons in the working cell? =IF(AND(K1051="Down",L1051<2),"Yes","",IF(AND(K1051="Partially Down",L1051<2),"Yes",""))
View Replies!
View Related
When I Hit "/ " Key It's Highlighting The 'File' Menu
I've no idea what combination of keys I hit to do this, but when I hit the / key it's highlighting the 'File' menu - working as though I pressed Alt. Unlike Alt though, it doesn't un-highlight it if I press / again. It only does it in Excel - Word works fine, as does notepad and obviously I can enter it on here to. I've tried rebooting, but it's still there.
View Replies!
View Related
Modifying =INDIRECT("'PD "&$AB12&"'!O13")
I have this formula =INDIRECT("'PD "&$AB12&"'!O13") in worksheet (say Sheet1). This returns value from O13 of worksheet PD 1 (if AB12=1 in worksheet Sheet1. The worksheets will be PD 2, PD 3, and so on for AB12 = 1,2, so on respectively) Suppose I make some changes on worksheet PD 1 which makes cell O13 to become P15. Now, in worksheet Sheet1, the formula remains still the same =INDIRECT("'PD "&$AB12&"'!O13"). I want this to be automatically changed to =INDIRECT("'PD "&$AB12&"'!P15") or any other formula that gives this result.
View Replies!
View Related
Runtime Error '91" Using Find Function
I have a spreadsheet with a large amount of data, and one thing I need to do is Find the maximum value in a column and then select it. I was originally using an If loop to find it, but I would like to make the code more efficient by using this method. My code is as follows: Function FindCells() Dim FindData As Long Dim Row_Number As Integer Range("L5:L2000").Activate FindData = ActiveSheet.Cells(6, 15).Value Range("L5:L2000").Find(What:=FindData, After:=ActiveCell, LookIn:=xlValues, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate Row_Number = ActiveCell.Row ActiveSheet.Cells(2, 14).Value = Row_Number End Function This string of code works fine, but it doesn't find the right value. Cell (6, 15) contains the maximum value but excel treats it as a whole number. If I try to change the data type of FindData to double or to string I get a Runtime 91 error. If I change LookAt to xlWhole I get the same error.
View Replies!
View Related
Doesn't Work If The Worksheet Hasn't Been Saved Yet
I'm trying to simplify a formula that I'm using in a spreadsheet that's being used mostly in Excel 2000. 2 reasons I'm doing this: 1) It doesn't work if the worksheet hasn't been saved yet. 2) It's ugly =DATE(YEAR(DATEVALUE(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,3) & " 20" & MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+5,2))),MONTH(DATEVALUE(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,3) & " 20" & MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+5,2)))+1,0) The formula returns the last day of the month of the name of the sheet. For example, if the sheet is names "Mar 07", it returns 3/31/07. (yeah, when the 2100 issue becomes a big deal, I'm not going to be ready but I have 92 years to think about it) Even if I can't make it prettier, does anyone have a way to get the sheet name into a formula without VBA Code in a file that hasn't been saved.
View Replies!
View Related
"Run-time Error '9'" Calling Userform On Some Computers
I get a "Run-time error '9': subscript out of range" error when calling a userform. The code opens a reference spreadsheet then shows a userform with a combobox and two command buttons (Continue and Quit). The userform has a private initialization sub-routine to populate the combobox with values from a list on the reference spreadsheet. When the userform is called the error comes up. Here's my problem: The code was written on my computer and saved as an Add-in. The add-in has since been installed on a number of other computers and works properly. Recently I tried to run the code and this error started popping up, don't know what changed. All versions of Excel are the same and haven't changed. BRF = ActiveWorkbook.Name If BRF = "Blank upload.xls" Then Workbooks.Open path & "QC Data AnalysisQCDA ref.XLS" OtherBRF.Show Workbooks("QCDA ref").Close SaveChanges:=False Goto 1 End If
View Replies!
View Related
Enhancing Conditional Formatting: Write "NO DATA", "HIGH", "GOOD", "MODERATE", "POOR", "BAD" Depending On The Value
I would like to write a function that enhances conditional formatting capabilities in this way: suppose that cell(4,5) contains a number that can be 0 to 5 in the adiacent cell(4,6) i want to put a function that: 1) write "NO DATA", "HIGH", "GOOD", "MODERATE", "POOR", "BAD" depending on that value 2)Choose color font depending on value 3)Choose color background depending on value I wrote this piece of Public Function StatusResponse(AdiacentCell As Range) As String Dim thisStatus As String Dim ThisFontColor As Integer Dim ThisbkColor As Integer Select Case AdiacentCell.Cells(1, 1) Case Is = 0 ThisbkColor = 2 ThisFontColor = 1 StatusResponse = "NO DATA"............
View Replies!
View Related
Error Message "run Time Error '424' Object Required"
For some reason my form won't open when the workbook is opened. I get an error message "run time error '424' object required" (which happens when I have Form1.show in the BOTH workbook_open event and the userform_initialize event (oops)). When I removed form1.show from the userform_initialize I don't get an error but I also get no form. I recall having this issue before but I can't recall how to fix it. Can I call the userform_initialize event from the workbook open event to get around this successfully and properly?
View Replies!
View Related
Using A "Find" Based Function Instead Of "Autofilter"
I work as a data manager at a bear meat factory*. We use a numeric code which refers to the tenderness of each bears flesh and prior to harvest -I often have to sort that data based on the numeric code. Excel has issues with screening numbers in Autofilter. You can not filter "begins with" or "ends with" if the data is numeric. For Example: This same problem does not exist in "Find" Since I seem to be relying on Autofilter for my queries, and it is possible to create a function similar to autofilter using the properly working "find" feature -I was wondering if anyone had already written a function that returns a range and functions similarly to autofilter?
View Replies!
View Related
Variation On An "if" Function & "lookup"
I have created a workbook for keeping records of all maintenance to vehicles in our fleet. I have a sheet called Vehicle maintenance input: & then another 10 sheets with the vehicle callsign. So Sheet 1 :- Input all data sheet Sheet 2 :- Y4 Sheet 3 :- Y7 & so on. I need a formulae that will copy multiple info to the relevant sheets, I have found a way of copying multiple info, with the formulae below, but I can't get it to work if there is more than 1 sheet. =IF(ISERROR(INDEX($A$1:$B$24,SMALL(IF($A$1:$A$24=C$27,ROW($A$1:$A$24)),ROW(2:2)),2)),"",INDEX($A$1:$B$24,SMALL(IF($A$1:$A$24=C$27,ROW($A$1:$A$24)),ROW(2:2)),2))
View Replies!
View Related
Print "Custom View" From Userform
I was just reading an answer to a question re: printing a set area. Dave Hawley mentioned the best method would be to set a "Custom View". how to "call" a custom view from a Userform dropdown? I need to print several "reports" which are seperate parts of the same sheet.
View Replies!
View Related
Hyperlink Doesn't Work
I have a hyperlink to [url] that doesn't work. When I click it I get an error saying "Unable to locate the server or proxy server". But when I cut & paste the address from the hyperlink into my browser (no chance of mistyping), it works fine.
View Replies!
View Related
Validation Drop Down Doesn't Appear
I named a range State, it contains a list of all the US state abbreviations. I do a data validation that refers to this range as a List. If I type in something that's not in the named range I get the error message I created for the Validation. But - I don't get a drop down button. I tried to do the same validation on another cell and get the same thing. Other drop downs for validation appear.
View Replies!
View Related
Right Click Doesn't Work
As said, the right click doesn't work anymore, last week it was working but it doesn't anymore. When I right-click wherever in the spreadsheet nothing happens but I know it's working because it works outside excel.
View Replies!
View Related
Listbox Doesn't Redraw
I have a worksheet with a listbox from the controls toolbox. I have to use the controls toolbox activex version because the box is formatted and I don't want a vertical scrollbar. I don't want to use a userform either because that would be major overkill. When the view is set to full screen the listbox partially disappears. Paging up and paging down will redraw the screen and the listbox. I've tried application.screenupdating with different listbox events but nothing works.
View Replies!
View Related
Some Times If I Do A =if( ) Formula It Doesn't Work
What am I doing wrong here. Some times if I do a =if( ) formula it doesn't work. For example, look at the attached picture. Cell K63 should say "End of Run". But it doesn't, What gives? Also I have had before where i do =IF(X62>$O$2,"End of Run", 0) (and $O$2 =81) and the cell when X62 is 81, not greater than. I know I can to >=, but thats not what I am doing.
View Replies!
View Related
Why Doesn't The Sound Play When The Value Changes Indirectly?
This is a complete mystery to me. I have some code (reproduced below) that plays a .wav file when any of the values in a1:a9 changes from 0 to greater than 0. This code works when I manually change one of the values to a trigger number in any of the cells. The problem is that the values in a1:a9 are really pointers to other cells. For example, a1 is "=sum(D9:D20)" So, when I d9:d20 chnages values such that a1 changes from 0 to 5, the sound doesn't play. To put it simply, if I type "5" in a1 the sound plays. If a1 = D1 and I type "5" in D1 the value in A1 changes to 5 but the sound doesn't play. How do I modify this code such that any change in the value of A1:A9, no matter the source of that data change, triggers the playing of the sound? ......
View Replies!
View Related
NAME Doesn't Stay Attached To The Cell
I have a cell (H27) which has a (sum) total in and have attached a NAME FRED. This is fine as long as the client doesn't add a row above it. If they do when I re-open the sheet the NAME doesn't stay attached to the cell (now H28) Is there a way to keep the NAME attached to the cell regardless where it is? Private Sub Workbook_SheetActivate(ByVal Sh As Object) Range("$H$27").Name = "Fred" End Sub
View Replies!
View Related
If Statement Doesn't Recognize Non-zero Values
I have the following formula with multiple if statements: IF(N2>0,N2,IF(O2>0,O2,IF(P2>0,P2,IF(Q2>0,Q2,IF(R2>0,R2,IF(S2>0,S2,IF(T2>0,T2))))))) This formula refers to cells in columns N,O,P,Q,R,S and T. Every cell in columns N,O,P,Q,R,S and T also contains formulas (VLOOKUPs). My if statement works fine but when it checks cells in column P, for some reason, it doesn’t recognize cells with non-zero values. I can’t figure out why this is happening and how to fix this problem.
View Replies!
View Related
Isolate Data That Doesn't Match
I have a long lists of values (product numbers) from our inventory records. Now, we've been given a new list of product numbers, some are the same and some are not. I want to isolate (spit out) the values from column A, than don't match column B. Column B is the list we SHOULD be using, column A is what we're currently using. See example below. Old Products New Products 34544 34544 34545 34546 34546 34547 34547 34548 34548 34550 34549 34551
View Replies!
View Related
Full Screen Doesn't Work.
This might be extremely easy, but why my Excel (nor Word for that matter) doesn't go to full screen anymore? I'm pretty sure that I haven't used any options, I do have a new graphics card, but all the other applications goes to full screen. I have restarted my computer. Basically I think that it's either some option, or then reinstalling Office.
View Replies!
View Related
VBA Doesn't Recognise Selection
I have a report that has been running in it's current state for several months with no issue. However, today it has decided that it doesn't understand the word 'selection'. eg: selection.copy gives compile error. Now... obviously NOT USING selection is preferred. However, let's put that aside for now because there are LOTS of these in this report and it's never had an issue until today. Is there some setting that could have gotten changed or something? It's happening on multiple computers and we're running Excel 2003 (standard) with SP3.
View Replies!
View Related
Counting Even If Doesn't Meet Criteria
I am trying to use this function to figure out averages for tests. there can be up to 25 tests in the sheet which is where the "YES" comes in. If the test booklet was used for this class then YES will be placed in the cell in field B2:B26. Now thes issue I'm having is that the formula is doing the math for the fields C2:C26 if there is a number in them if there is even if it does not say YES in the B2:B26 field. Here is the formula/function I'm using: =(COUNTA(C2:C26))/COUNTIF($B$2:$B$26,"YES")
View Replies!
View Related
Private Sub Workbook_Open() Doesn't Execute
why the code below might not execute ? It just seems like it skips over the code and doesn't run at all. Private Sub Workbook_Open() 'AssignRequestNumber() MsgBox ("Whoa") Run "AssignTrackNumber" End Sub I have a macro assigning a unique number to the file in a fixed cell whenever the .xlt file is opened from File-New.
View Replies!
View Related
Code Doesn't Loop Properly **
I have some code (probably a little inefficient, but still) that should delete any rows that contain nothing in column V. My problem is that it only deletes 1 row at a time: Dim c As Long Dim Limit As Long Limit = Cells(Rows.Count, 11).End(xlUp).Row For c = 2 To Limit If Cells(c, 22).Value = "" Then Cells(c, 22).EntireRow.Delete xlUp End If Next c
View Replies!
View Related
Programme Doesn't Work When Sent To A Different Computer
I have a programme which works fine on my computer at home but when I send it via email to work the 'sort' function messes up (there is a chance it could be the 'vlookup' function, though) and all the cells in the sort range lose their formula reference - which is of course a tad annoying. The part of the programme I'm having difficulty with is this: Range("SsNo1").Resize(d, f).Offset(a, 0).Select Selection.Sort Key1:=Range("FRSet1"), Order1:=xlDescending, Header:=xlGuess _ , OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal
View Replies!
View Related
Array Doesn't Seem To Write Out Results
I have 4000 rows of data with numbers in it (only one column...column C). These numbers are consecutive but these have breaks in between. So I am trying to find out where those breaks are and output those points in a list. So here is a macro that I came up with...but these array doesn't seem to write out results.... can anybody help please? This macro tries to compare the two adjacent cells and if the difference between the values is greater than 1 then it copies that value in an array and writes that value in a cell in the same sheet. Sub Macro1() Dim temp(1, 1000) As Integer i = 0 previouscell = 0 For Each cell In Range("C:C") currentcell = cell.Value abc = previouscell + 1 If currentcell <> abc Then temp(1, i) = currentcell i = i + 1 Range("D" & i).Value = temp(1, i) End If previouscell = cell.Value Next cell End Sub
View Replies!
View Related
Why Doesn't My Variant Array Work
The answer is probably "because I'm stupid", but I really can't get my head around it! I'm playing with variant arrays for the first (and possibly last) time, The code I have is: Sub test() Dim vSheetColours As Variant Dim iCounter As Integer vSheetColours = Range("Colours").Interior.ColorIndex For iCounter = 1 To UBound(vSheetColours, 1) MsgBox vSheetColours(iCounter, 1) Next iCounter End Sub (Obviously this code doesn't do anything useful - but if I could get it to work, I might have a chance of making my real code work!) Colours is a range of 8 cells. Each one has some text in, and has a different background colour. I'm trying to store the colours. If I run this code, I get a runtime error 13 type mismatch, and it highlights For iCounter = 1 To UBound(vSheetColours, 1) But if I replace vSheetColours = Range("Colours").Interior.ColorIndex with vSheetColours = Range("Colours").Value it works fine.
View Replies!
View Related
Delete Row If Doesn't Contain Certain Numbers
Working at a company where we every month extract our customers into a excel document. This is the CRM system , and some of the clients can't (shouldn't) be approached. In Cell C is their customer classifications. In total there is about 60 different classifications. 10 of them is clients who we can send information to. For example customers with class 11111 or 41414 or 51515 or 61616 (among with 45 others) can't be approached. I'd like a macro/script who can delete all the nonrelevant clients so that afterwards I have a list of clients who can be approached.
View Replies!
View Related
Right-click Menu Doesn't Exist
I have few questions, 1. why the "auto sum" icon grey out, and doesn't work. 2. when I right click the sheet, the menu doesn't show up. 3. Insert columns/rows, When I highlight 2 columns/rows, right-click the mouse & the menu doesn't show up for me to choose insert col/row.
View Replies!
View Related
Workbook_Open Doesn't Run On Second Open
I have noticed this on more than one workbook with an Workbook_Open macro. When you open Excel, open Workbook1, do some work , save or don't save and close, then reOpen Workbook1 without having closed the Excel application, the Workbook_Open macro in Workbook1 doesn't run. Closing Excel and reopening Workbook1 initiates the Workbook_Open macro. It's as if Excel remembers having previously opened Workbook1 and so it doesn't rerun the Workbook_Open macro the next time you open it.
View Replies!
View Related
|
TRACKER
