Posts by jjsande1492

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

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

Target

Jan-14

Feb-14

Mar-14

Apr-14

May-14

Jun-14

Jul-14

Aug-14

Sep-14

Oct-14

Nov-14

Dec-14

Should be

1

500

151

86

196

137

113

93

51

45

173

140

111

198

Apr-14

2

300

74

50

95

182

164

139

14

128

91

159

1

70

Apr-14

3

900

40

177

148

195

158

21

55

170

153

113

98

118

Aug-14

4

1200

27

113

30

113

114

119

53

33

4

163

131

15

#N/A

5

200

141

11

123

114

45

1

159

97

124

178

160

161

Mar-14

[/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

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