Formula Stops Working After Performing Certain Tasks
Nov 21, 2006
I was given the following formula by Domenic some time ago that I use along with VBA code supplied by Fin Fan Foom to open do a lookup on a closed workbook:
=If(ISNUMBER(MATCH(MIN(If( Date=D2,If(ABS(Time-E2)<"0:30:30"+0,ABS(Time-E2)))),If(Date=D2,ABS(Time-E2)),0)),INDEX(Contact,MATCH(MIN(If(Date=D2,If(ABS(Time-E2)<"0:30:30"+0,ABS(Time-E2)))),If(Date=D2,ABS(Time-E2)),0)),"")
Everything works fine, until I attempt to…
- cut & paste a block of cells, or
- do a SaveAs, or
- change a worksheet name
The formula will no longer function after performing any of these tasks (the cells go blank), and I have to close the workbook without saving to preserve the original workbook functionality. All other tasks, including even importing of different files into the workbook have NO negative effect.
The formula uses an “INDIRECT” named range. Sheet name changes are reflected in the named ranges, but any change of names somehow causes a malfunction (no sheet names are referenced in the VBA).
I thought that the large lookup workbook (40,000 rows) may be a problem, but even after deleting most of the data and retaining only a few rows, the problem still remained.
After the formula cuts out (after performing the above tasks), if I open up the lookup file, then the lookup will work again, but if I close the lookup file, it will stop working.
I doubt the problem is in the VBA code since IT WORKS, and besides, I have other simple VLookups formulas that also uses the same process (and VBA code) and they continue to work just fine when that one formula gives out. The only thing I can think of is that the complexity of that one formula may be an issue.
Anyway, I the original thread is long, so I’m starting a new one here. The original thread is here (Domenic’s formula, pg 7; FFF’s code, pg 8):
Dedicated Cell To Choose Lookup Table
View 9 Replies
ADVERTISEMENT
Jun 17, 2013
I have two subroutines. One subroutine updates some figures on spreadsheet 1, and then calls a subroutine that updates some figures on spreadsheet 2. Spreadsheet 2 is supposed to save and close, and return to spreadsheet 1, but it only gets as far as opening spreadsheet 2 and updating the figures. These are the two subroutines:
Code:
Sub UpdateLegalAndMeans()
Dim wBook As Workbook
Dim count As Integer ' counter for counting down backlog figures
[Code]....
View 4 Replies
View Related
Dec 2, 2011
I have this macro which sits in the workbook module:
Code:
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Excel.Range)
Static OldRng As Range
On Error Resume Next
If Application.CutCopyMode = xlCopy Or Application.CutCopyMode = xlCut Then
OldRng.EntireColumn.FormatConditions(1).Delete
OldRng.EntireRow.FormatConditions(1).Delete
[Code] .......
It's used to highlight the selected/active row.
But, as soon as I run another macro, it simply stops working entirely and I'm left with a row permanently highlighted until you manually remove the conditional formatting. All formats and cell colours controlled in the other macro stop working as well. This is the other (edited for publishing) macro, which is used to send an email, and is found in a module:
Code:
Sub Send_Mail()
'this sends an email that sends a text message
Dim OutApp As Object
Dim OutMail As Object
Dim MyButton As String
Dim strTo As String
[Code] .......
I didn't write the first macro so I'm not sure where the problem is? I'm guessing there is something in the code that is stopping it from working (i.e. running conditional formats) as soon as another macro is run? Is that was is happening?
View 5 Replies
View Related
Feb 6, 2008
i have a worksheet named for example 'allocation 1' this is a master document and is opened and modified and 'saved as' under a customer name. This then stops a few important macros working properly because they refer to the original title and not the new saved title. Is there any code that will let the macro recognise any new title it is saved under?
View 10 Replies
View Related
Nov 23, 2008
I'm using Excel 2007 and my s/s is 360000 rows deep.
To cut+paste formulas+formats from one column to another I'm using the following
Sub move_formula_and_formats_from_I_to_L()
Dim cell As Range
Application.ScreenUpdating = False
For Each cell In Range("I1", Cells(Rows.Count, "I").End(xlUp))
With cell
If .HasFormula And Not .Offset(1).HasFormula Then
.Cut Destination:=.Offset(1, 3)
End If
End With
Next cell
Application.ScreenUpdating = True
End Sub
The code stops working after 159000 rows and highlights (in yellow) the line:
.Cut Destination:=.Offset(1, 3)
View 9 Replies
View Related
Jun 13, 2009
I have a command button on worksheet2 of my spreadsheet, which has a macro assigned that will take the user to worksheet1 when it is clicked.
My problem is that when I hide worksheet1 using xlSheetVeryHidden - the maro seems to die and the command button no longer works.
View 9 Replies
View Related
May 16, 2006
I have joined out of desperation relating to a problem with an Active X control inserted into a userform. This control accesses and returns data from an instrument on the RS232 PC comms port. All works well until I try to save, copy or print the workbook using usual VBA code, then mysteriously the code just stops, and Excel needs to be restarted again. It seems to just get lost!
View 2 Replies
View Related
May 21, 2013
I have a conditional formula that highlights dates red when it meets a certain criteria. The file named MS Working, the conditional formatting formula (formula below) works as it should with no issues. The other file MS NOT Working, the conditional formatting formula (formula below) has stopped working as it should. What I did? I inserted a new column to the left. Where the MS Working file has only 1 column to the far left, the MS NOT Working file now has 2 columns to the far left.
It seems as if the formula adjusted itself when I inserted the new column; however, its not working.
MS Working
Conditional Formula: =AND(TODAY()>B3,NOT(ISODD(COLUMN())),B3<>"",OR(C3="",C3=0),B3<>0)
MS NOT Working
Conditional Formula: =AND(TODAY()>C3,NOT(ISODD(COLUMN())),C3<>"",OR(D3="",D3=0),C3<>0)
View 3 Replies
View Related
Jan 28, 2014
Excel 2013
Our company has a shipping report spreadsheet that has the job number in column A and the ship date in column F. I want to track other things on a separate spreadsheet. So far, we are only down to row 1440 so in order to account for future jobs, my code on the other spreadsheet is:
=VLOOKUP(A2, '[Shipping Report.xlsx]Monthly shipping schedule'!$A$1:$F$5000, 6, FALSE)
Also, the jobs are arranged by ship date, not by number order.
This has worked perfectly for months but for some reason, it will no longer recognize job numbers greater than 1331. The format is the same between the working and non-working cell. Is there a max limit for vlookups that I am not aware of? If this was a sudden failure, I could nail it down but there has to be an underlying reason I'm not seeing.
Here is a sample of my spreadsheet:
Ship Date
1325 12/27/13
1326 3/10/14
1327 1/4/14
1328 1/31/14
1329 3/3/14
1330 1/22/14
1331 1/15/14
1332 #N/A
1333 #N/A
1334 #N/A
1335 #N/A
1336 #N/A
1337 #N/A
1338 #N/A
1339 #N/A
1340 #N/A
View 9 Replies
View Related
Apr 27, 2007
I am using a BeforeDoubleClick event to look for a worksheet name and if it exists, go to the sheet. If the worksheet does not exist, it is creates a new sheet by copying a hidden sheet and naming it using a name in a cell reference. It works for the most part, but I am finding that it is having an intermittant issue. Sometimes, when I double-click, it double-clicks the target cell for editing, and then it renames the active sheet to the cell reference rather than copying the hidden sheet. It seems to happen when I reach 35 sheets created, but not always. Here is the
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim WSname As String
Dim WScheck As Worksheet
Dim WScheckname As String
If Not Intersect(Target, Range("A5:L85")) Is Nothing Then
WSname = Range("L" & Target.Row)
On Error Resume Next
Set WScheck = Sheets(WSname)
If WScheck Is Nothing Then 'Doesn't exist so create it
Sheets("Master_SLP").Copy Before:=Sheets(Worksheets.Count)
ActiveSheet.Name = WSname.........................
View 5 Replies
View Related
Jan 4, 2012
I have a simple list of data (Name, Room, etc.) in a spreadsheet. Each day I click on the spreadsheet tab and create a "copy" and (move to end). This creates a spreadsheet for the next day's data.
My problem is that the macro I use to sort this data never works in the newly created spreadsheet within the same workbook. As usual, I'm sure it is something relatively simple that I am overlooking.
View 1 Replies
View Related
Jul 12, 2013
I'm a fairly new user to using VBA and I'm currently running into a little snag. I'm trying tu create userform which will let me know if a person already exists in my sheet by looking up the last name in "A", then looking up his first name in "B", if such entry exists then display a message saying " Yes". If entry user has not been entered in the sheet then use a combox, to select the reason , then choose a date ( i have a working date picker ). Then entry should be entered in sheet (Name, Surname, Reason, date).
Whats working, I can use all of the above to enter all the info into the sheet, what I need to do is verify if user is alread in there, if not, then add it. The so on and so forth
View 3 Replies
View Related
Feb 26, 2008
i've managed to get a set of cells to show (with comments) what the last weeks data looked like. (so cell value = this week, comment contains last week)
What i now want to do is to use conditional formatting to highlight any major differences between the figures... so:
My comment i'm sure is stored as text in the format:
Previous value was
xxxxx
So the first thing i'd need to do is to to strip out the text at the start, and then perform the calculation.
View 9 Replies
View Related
Aug 5, 2008
I have the following code which previously was showing an error on the Do While line. It was suggested that I change the line too Range("B2").Text. However, the loop is not deleting the rows based on "#N/A". Can anyone offer any suggestions?
Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlGuess .....
View 9 Replies
View Related
Mar 13, 2009
I have a very large grid of data, about 400Rx200C, which contains a lot of blank fields. All of this data is currently in values from 0-100 representing percentages, but not formatted as such. I need to make all of these values into Excel Percentages so naturally used Paste Special and Divide from a field with the value 100 in it and then simply format the cells as %.
However, this fills in all of the original empty cells with a 0.000% which I do not want, I need them to stay blank. And some of the cells that do have values also have 0.000% so I cannot just delete all of these values with a VBA script. I had been hoping the Skip Blanks option would do this, but it does not unfortunately.
View 3 Replies
View Related
Sep 22, 2009
I am trying to write a Macro that will do the following.
1. Using a new workbook
2. Open a file in a in a directory. Then
3. Run a vlookup using the opened file as the filename in the vlookup
4. Paste the results of the vlookup into the original file next to the file name.
5. Close the file and open the next one in the folder.
The folder contains about 2k files that it needs to loop through. I found a macro on this site created by Ravishankar and attempted to add to it. Not going so well.
View 2 Replies
View Related
Oct 30, 2007
I have searched and think I have found what I need to create Outlook tasks from my Excel spreadsheet. I'm hoping that someone here can help me pull it together into something that will be usable in my project tracking spreadhsheet...
I try to keep track of milestones... these milestones are listed in column B of my spreadsheet and are in rows 10 through about 105
For each of my customers I then keep 4 columns D, E, F, G - H, I, J, K - L,M,N,O - etc. Each group of 4 is a different customer....
The customer's first column (D, H, L, etc.) is a Due date of the Milestone that is found in column B.
The second column is a check box that I use to indicate when the milestone is complete
The 3rd column is the completion date
The last colum is just a filler space that helps to separate each customer.
What I would like to do is insert a button at the bottom of EACH grouping of 4 that when pressed would look at the first column for the customer group and for ANY Milestone that has a DUE date process the stuff in the VB below to create an OUTLOOK task for each item that has a DUE date. (It would be conceivable that as it stepped through each row for a customer group of columns that I could have it create 90+ Tasks in Outlook.
A couple of additional things... in row 110 for the first column of the customer I have a DATE or a blank. So for example in D110 it might be blank or a date like 10/29/2007. If it is blank then when I press the button it would loop through ADD the tasks for each row with a due date. If row 110 has a DATE I would get a popup indicating that the tasks are already in OUTLOOK and that they were added on whatever the date is in row 110... This is kind of the safeguard that I don't get the same group of tasks multiple times by pressing the button by mistake.
View 11 Replies
View Related
Jan 20, 2010
I want to use excel to update the tasks in outlook. All I have is a due date (from A2) and a task (from B2) Here is the code I have got so far
View 2 Replies
View Related
Apr 6, 2009
Im having trouble with getting a macro to run at a specific time and day (Every Thursday at 7PM).
Here's what I have done so far..
1. I added my excel file to the task scheduler
2. created a class module and place the code below into it.
In testing the workbook opened but it asked if i wanted to enable or disable the macros - how do I get around this? And it does seem to kick off the macro (create_all).
Private Sub Workbook_Open()
If Hour(Now) = 1130 And Weekday(Now, vbMonday) < 7 Then
' TheNameOfYourCurrentProcedureHere
create_all
If Workbooks.Count = 1 Then
Me.Save
Application.Quit
Else
Me.Close True 'save the changes and close the workbook
End If
End If
End Sub
View 9 Replies
View Related
Mar 28, 2014
I am creating a range of numbers from 1 to 2000 with increments of 0.5. Then I have to subtract certain range, for example, 100 to 350. So, the result should display, 1 to 95.5 and 350.5 to 2000.
View 4 Replies
View Related
Jan 19, 2012
The data is a copy of CSV file in to a spreadsheet. Below is what the paste looks like:
100100 Books
1234.ab
1345.bf
1456
1567
100100 Books
100200 Magazines
100300 Videos
1234.ab
1345.bf
1456
1567
100300 Videos
All of the numbers between the 100100 Books, 100300 Videos belongs to that category. This repeats hundreds of rows down. I need the column to the left of this range to display which division it under. I parse this in excel so Books, Videos, Magazines will be in their own column. I am not exporting the CSV so have to manipulate it in excel. I need to have the column to the left combine the whole identifier so I can perform a VLOOKUP on it. It should look as follows:
100100.1234.ab
100100.1345.bf
etc.
My approach was to get the column to the left of the range to display the correct division, then have another column combine by using (cell)&"."&(cell).
View 1 Replies
View Related
Nov 20, 2006
Office 2003 with Windows XP
This is my first post so i may not have done everything correctly. I tried doing google searches to see if anyone else has done something similar but its a tricky thing to search for. I have found pages dealing with manipulating time but none seem to be what I want
I have an Excel sheet with data
A1=date
b1=source
C1-h1 all contain data (name, location, etc, etc) but I am not interested in those.
A1 (the date cell) is formatted like: m/d/yyyy" "h:mm:ss AM/PM
[Sample]
a2 = 8/1/2006 12:20:23 AM b2 = phone
a3 = 8/1/2006 12:21:13 AM b3 = email
a4 = 8/1/2006 12:33:03 AM b4 = phone
This is what I am trying to accomplish:
I want to break each hour, whether it contains data or not and get a count of the different types of source
my desired output would be:
time Phone email
12:00AM
to 2 3
12:30AM
My problem occurs because I am unsure how to break the times down into intervals. In my head, I would create a new column and try to break the time up into an interval. So for a2 (8/1/2006 12:20:23 AM), I think I would need to break the date and time up and then determine which interval would be needed based on the time.
I was going to perform the following:
=text(a2, "h" + if("mm:ss">30, 30,00)
Basically, pull the hour from a2 and evaluate the mins/secs.
If the min/sec is bigger than 30, put a 30 in the box. Less than 30, put double zero in the box. So for 8/1/2006 12:20:23 AM, b2 would have 12:00 since it falls in the 12 to 12:30 interval.
Once I have all the intervals, I would also need to preform a count on the source cell but I think I can use a vlookup to get the count for the interval.
View 9 Replies
View Related
Aug 7, 2009
I have a worksheet where I calculate the duration it takes for an operation. the format of the worksheet is as folows:
The worksheet is composed of Start and Finish times in this format: 2009/06/02 5:32:19 , so for example,
- Row1 of the worksheet will have "Start" in column H1 and the start time in column B1
- Row 2 of the worksheet directly below has the "Finish" time, with the Finish time information in column B2
So with the help of excelforums I was able to implement a macro to extract the finish time cell found in row B2 and offset it to C1 and do a simple calculation to get the duration in minutes and display it in column D of the same row as "Start".
View 12 Replies
View Related
Feb 2, 2009
I have 10 columns (1 through 10) called TASKS and 2 rows for each task
Row 1, has a 'start date' in the form of 1/1/2010
Row 2, is duration
the duration of each task may or may not overlap with other durations of other tasks.
how can i find out the total duration of ALL my tasks.....without any overlaping.
any formula that can calculate this? ( i need one number at the end!)
View 9 Replies
View Related
Nov 19, 2009
I have an array with dimensions (5000,30). I want to perform a worksheet function "Percentile" on specific columns within the array. So for instance I may want to know the element falling at the 50th percentile in column 5 of the 30 column array. Is there a way to do this without having to place the array onto a worksheet?
View 4 Replies
View Related
Apr 8, 2014
I have used a vlookup code from a past post (Vlookup multiple values in one cell) in VBA and successfully retrieved my information to one cell.
However, I would now like to remove any duplicates in this cell; preferably while running the same code.
--vlookup code
Public Function mVLOOKUP(mlookup_value As Range, mtable_array As Range, mcol_index_num As Long)
Dim r As Range
[Code].....
View 2 Replies
View Related
May 11, 2007
I have a set of data with names in col A, Currency in col B, Amount in Col C. I want to convert all the amounts in NON-USD based on exchange rate I input daily into column D.
How do i search down the row for the next name that is denominated in GBP / YEN etc and keep converting until I dont find any more other currencies in it. I tried to improvise on the finding nth but couldnt get anything out of it. Have attached a spreadsheet.
View 9 Replies
View Related
Sep 10, 2013
I'm working on a project that will will have varying tasks & estimating the total duration to complete these tasks.
For example:
If estimating that the min-project will take 90 days & I have 17 tasks, how can I distribute the 90 days evenly amongst the 17 tasks?
View 5 Replies
View Related
Nov 1, 2009
I want to put a value in a single cell - A1 is going to be (say) 5.
View 5 Replies
View Related
Jan 28, 2003
I have a worksheet that runs a query from an external data program. I have made a macro that refreshes the worksheet and this works ok. Is there a way I can get Scheduled tasks to automatically update this workbook eg overnight. I do not want to leave the workbook open either. Can task scheduler open the workbook, refresh the data, then close the workbook again.
View 9 Replies
View Related
