Re: Can you pass a variable in a workbook_open event using application.ontime?
Good idea. I hadn't thought of that kind of work flow. Cheers!
Re: Can you pass a variable in a workbook_open event using application.ontime?
Good idea. I hadn't thought of that kind of work flow. Cheers!
Hi there. I have a very simple workbook_open event to determine a random time (within half an hour of a given time) to start and end a process. I would like to pass the start and finish times to the next sub which is in a module. However, once the workbook_open event ends, the variables empty. How can I pass these variables to the next sub using application.ontime?
Option Explicit
Public RandomStartTime As Date 'Actual time to start
Public RandomFinishTime As Date 'Actual time to end
Private Sub Workbook_Open()
Dim RandomNumber As Integer 'Randomly generated number of seconds to wait
Dim StartDate As Date 'Date the process was started
Dim StartTime As Date 'Earliest time to start
Dim FinishTime As Date 'Earliest time to end
StartDate = Date 'Set the date the process was started
StartTime = TimeValue("07:45:00") 'Earliest time to start
FinishTime = TimeValue("16:45:00") 'Earliest time to end
RandomNumber = Int((1800 - 1) * Rnd + 1) 'Randomly generated number of seconds to wait
RandomStartTime = StartTime + TimeSerial(0, 0, RandomNumber) 'Actual time to start
RandomNumber = Int((1800 - 1) * Rnd + 1) 'Randomly generated number of seconds to wait
RandomFinishTime = FinishTime + TimeSerial(0, 0, RandomNumber) 'Actual time to end
Application.OnTime TimeValue("11:25:00"), "ProcessBegin" 'Call the next module
End Sub
Display More
Re: Quickly compare two columns of data
Exactly what I was in the process of doing. Thank you. One other question though, what if the two columns are in different worksheets? I'm getting type mismatch errors in the code I've tried.
Edit: Nevermind. I figured it out. I gave a variable an incorrect data type. Thanks for your help. I just ran it on 400,000 rows and it took about half a second. Awesome!
Re: Quickly compare two columns of data
Good stuff again. Thanks KjBox. Fortunately, I only need to know if they differ. If they do, the process will be stopped. I had a wrench thrown in the works with my data set, so I won't be able to test this until I get that resolved. I appreciate your time.
Re: Quickly compare two columns of data
KjBox, thank you. I will give that a shot in the next couple days. I think it will do the trick.
yegarboy, thanks, but I can't loop through 50,000+ rows as mentioned in my original post. In terms of usability, the amount of time it takes to loop through that many cells makes it unusable.
Hi, everyone.
I'm looking for a very quick (in terms of computations) way of comparing two columns. In a nutshell, I want to compare alpha-numeric data in column A to column B. I need to know if they are exactly the same (same number of rows, same values in each cell, sorted the same, etc). The catch is that since there are around 50,000 rows, I can't use loops in VBA. It takes too long to iterate through all the rows.
Any suggestions?
Cheers,
jj
Hello.
I have just stumbled onto the weird and wonderful tools available in 'Power View' in Excel 2013. One of those things is geo mapping. Does anyone know if there is a limit to the number of points that will plot on a map?
Cheers,
JJ
Re: Finding the earliest date a target number was reached
Bump. Does anyone have any thoughts on this?
Hey, everyone.
Is it possible through formulas (not VBA) to find the earliest date a target was reached given the below example? The best I could think of was building a helper table that did a cumulative sum by month, but I would like to avoid that. Does anyone have any ideas?
[TABLE="width: 1002"]
Customer
[/td]Target
[/td]Jan-14
[/td]Feb-14
[/td]Mar-14
[/td]Apr-14
[/td]May-14
[/td]Jun-14
[/td]Jul-14
[/td]Aug-14
[/td]Sep-14
[/td]Oct-14
[/td]Nov-14
[/td]Dec-14
[/td]Should be
[/td]1
[/td]500
[/td]151
[/td]86
[/td]196
[/td]137
[/td]113
[/td]93
[/td]51
[/td]45
[/td]173
[/td]140
[/td]111
[/td]198
[/td]Apr-14
[/td]2
[/td]300
[/td]74
[/td]50
[/td]95
[/td]182
[/td]164
[/td]139
[/td]14
[/td]128
[/td]91
[/td]159
[/td]1
[/td]70
[/td]Apr-14
[/td]3
[/td]900
[/td]40
[/td]177
[/td]148
[/td]195
[/td]158
[/td]21
[/td]55
[/td]170
[/td]153
[/td]113
[/td]98
[/td]118
[/td]Aug-14
[/td]4
[/td]1200
[/td]27
[/td]113
[/td]30
[/td]113
[/td]114
[/td]119
[/td]53
[/td]33
[/td]4
[/td]163
[/td]131
[/td]15
[/td]#N/A
[/td]5
[/td]200
[/td]141
[/td]11
[/td]123
[/td]114
[/td]45
[/td]1
[/td]159
[/td]97
[/td]124
[/td]178
[/td]160
[/td]161
[/td]Mar-14
[/td]
[/TABLE]
Cheers,
JJ
Re: Array Formulas - Can you search a range for a range of values?
That is one heck of a formula. Every time I start to think I've got most of Excel covered, something like this comes along. Thank you. Why do you recommend avoiding it? Processing overhead?
Cheers,
JJ
Re: Array Formulas - Can you search a range for a range of values?
That worked a treat. Thank you. Is it possible to include multiple rows and columns in the lookup array?
=SUM(IF(ISNUMBER(MATCH(Sales!$A$3:$A$1000,$A$2:$K$100,0)),IF(YEAR(Sales!$D$1:$FC$1)=$T2,Sales!$D$3:$FC$1000)))
A quick test gave returned a zero result which is not correct, but I would love to incorporate this.
Hi there.
I have a relatively simple array formula as follows:
=SUM(IF(Sales!$A$3:$A$1000=$A$2,IF(YEAR(Sales!$D$1:$FC$1)=$T2,Sales!$D$3:$FC$1000,0)))
In a nutshell, I'm summing D3:FC1000 on the Sales worksheet if A3:A1000 on the sales worksheet is equal to cell A2 and if the year of the dates in D1:FC1 is equal to cell T2. This formula works fine.
My question is, how can I use an array formula to look at a multiple criteria at once? In other words, instead of only checking A3:A1000 against cell A2, I would like to check A3:A1000 against all values in cells A2:K2. Theoretically, the formula would look like this. Note the bold section:
=SUM(IF(Sales!$A$3:$A$1000=$A$2:$K$2,IF(YEAR(Sales!$D$1:$FC$1)=$T2,Sales!$D$3:$FC$1000,0)))
Obviously, this formula doesn't work. I've tried all manners of OR's, AND's, and FIND's, but I'm coming up empty. Does anyone have any idea how (or if) I can do this?
Cheers,
JJ
Re: Worksheet Protection: How many editable ranges can I have and what are their limi
Thanks, Roy. I successfully built the macro to generate editable ranges. There was no way I was going to manually set up 2,138 individual ranges that the macro did for me.
Hey, everyone.
I am creating a macro to generate ranges that are allowed to be edited in a protected worksheet. I have two questions on this:
1. Is there a limit to how many defined ranges I am allowed to have?
2. Is there a limit to how long the range reference can be?
Cheers,
JJ
Re: Macro works stepping through, but not when run on its own
Thank you. That seems to work, but why? Isn't my code doing the exact same thing as yours except you've used a 'With' statement? What happened in Excel 2013 to make this not work like it did in 2007? It's unfortunate that my three lines of code are now four. I realise it's only one line, but given how many times I do stuff like this, it adds up.
Hi there.
I have a very simple macro that copies data from one worksheet to the other. This macro worked fine in Excel 2007. I have since migrated to to 2013. The newly arisen problem is that when I run the macro, the data is not copied to the 'Previous Part Data' worksheet. However, if I step through the code line by line, it works fine.
Here is the code:
Sheets("Current Part Data").Select 'Select the 'Current Part Data' worksheet
'Copy the data to the 'Previous Part Data' worksheet then clear the data
Range("A4:V" & CurrentPartDataFinalRow).Copy
Sheets("Previous Part Data").(Range("A4").PasteSpecial Paste:=xlValues
Range("A4:V" & CurrentPartDataFinalRow).ClearContents
Again, run on its own, it does not paste the data into "Previous Part Data', but stepping through line by line works just fine. I need to only paste the values which is why I did not use 'copy destination: xxx'. I found that if I select the worksheet before pasting, then the code works fine as seen below:
Sheets("Current Part Data").Select 'Select the 'Current Part Data' worksheet
'Copy the data to the 'Previous Part Data' worksheet then clear the data
Range("A4:V" & CurrentPartDataFinalRow).Copy
Sheets("Previous Part Data").Select 'Select the 'Previous Part Data' worksheet
Range("A4").PasteSpecial Paste:=xlValues
Sheets("Current Part Data").Select 'Select the 'Current Part Data' worksheet
Range("A4:V" & CurrentPartDataFinalRow).ClearContents
Display More
I have two questions:
1. Why did this work in 2007 and not in 2013?
2. Is there something I can do to get rid of the sheet selection statements to avoid cluttering my code?
Cheers,
JJ
Re: Excel 2013 graph is showing #N/A?
Thanks, guys. Changing to a scatter works, but I was hoping there was a way to get the line graphs to work. I'm 100% positive that I did similar things in Excel 2007 and it never gave me this problem. For now, this will have to work. Thank you.
Re: Excel 2013 graph is showing #N/A?
Thanks for your input. I figured I was simply missing something minor and wouldn't require the workbook. Long story short, I need to reference a static range of cells and have the graph omit (both values and labels) instances of #N/A. Excel 2007 did this without any special workarounds.
Cheers,
JJ
Re: Excel 2013 graph is showing #N/A?
Bump. Anyone have any ideas?
Hi there. I just migrated from Excel 2007 to 2013. In 2013, the graphs are plotting my #N/A values ( using =NA() ). 2007 automatically ignored these. How do I get my graph to ignore #N/A?
[ATTACH=CONFIG]61164[/ATTACH]
Cheers,
JJ