Filling in a column based on certain criteria

Originally Posted by Crusaders43

Hey everyone,

I am trying to auto-fill a column based on certain criteria - in other words, fill in blank cells based on a cell above, just a little more complex version. I have added an attachment to give everyone a visual of what I am describing and then maybe this explanation will make a little more sense.

First, if you open the attachment, the left hand side is a small example of what I have. The middle is the condition. And the right hand side is what I'd like to see happene.

In the example, I am matching up a time and when I hit that time, I am adding on a certain number of miles per hours based on the condition and the cell above. Meanwhile, I am filling in the blank cells with the condition until the condition changes. So a cell is incremented based on the condition and gets its value from the cell above.

Is there a way to do this using VBA? I'm seeking a VBA solution simply because my data set could get rather large. I've tried a number of things but can't quite get it to work yet. Any help is appreciated!! Thanks!

Again, hopefully this makes sense after viewing the attachment. If it is still unclear, please let me know. Thanks!

Hi,

Give the following names to your cells
"DataTop" - the first time value in your list in the example this is A2
"Increase" - this is the small table of Increase rates in example H7:I9
"DataList" - the first cell in which you want to create your expanded list. I used E2

Now add the following procedure in the VBE

Please Login or Register  to view this content.

Regards

Richard Buttrey,

Let me start off by thanking you!!!!
Wow, that was great....it worked perfectly...

I just have 3 questions....

First, if I wanted to add a column to everything with the same concept...in other words, in column "C" I had a category labeled 'wind' and it also had an increase rate column added to the increase rate table. Assuming the increment of both wind and mph occur at similar times, how would I add another column to this code?

Also, I've been playing with the code a bit and don't want to bunk it up. But how do I take off the 'mph' part of it?

Finally, what if I had numbers such as 745 instead of 7:45?



I have added another file that sums up these 3 questions into one example.
Please take a look.
I really do appreciate the help, I'm very grateful. Thanks again

Originally Posted by Crusaders43

Richard Buttrey,

Let me start off by thanking you!!!!
Wow, that was great....it worked perfectly...

I just have 3 questions....

First, if I wanted to add a column to everything with the same concept...in other words, in column "C" I had a category labeled 'wind' and it also had an increase rate column added to the increase rate table. Assuming the increment of both wind and mph occur at similar times, how would I add another column to this code?

Also, I've been playing with the code a bit and don't want to bunk it up. But how do I take off the 'mph' part of it?

Finally, what if I had numbers such as 745 instead of 7:45?



I have added another file that sums up these 3 questions into one example.
Please take a look.
I really do appreciate the help, I'm very grateful. Thanks again

Hi,

Use the modified code below.

Re the mph.
Because you had the 'mph' in the first occurrence, the original code needed to slice this text string with the LEFT() bit of code, and pick the characters ignoring the last 4 (i.e. the leading space and 'mph'). Now that you no longer have mph as a bit of text, this bit of code is not necessary.

Re the formatting.
Originally you were formatting the time numbers to look like a traditional presentation of time i.e. 7:45 and hence the code needed the bit about Format(....the lookup function, "hh : mm"). It's actually easier if you just have the raw numbers as per this second example since no special formatting is needed - hence I've left that bit out too.

Please Login or Register  to view this content.

Regards

Originally Posted by Crusaders43

Richard,

Again, thanks for the help.

Aftern naming the appropriate cells, I ran the code and it worked for one row and then produced a "Run-time error '1004' Application-defined or object-defined error"

Any idea of what would produce this?

Hi,

That's odd since I've just tested it again and it works fine here.

However there's a clue. Since it worked for the first row, that suggests that it's something in the ELSE part of the IF..THEN condition. The main difference here of course is the VLOOKUP of the range called 'Increase'

Did you remember to extend the range of the Name 'Increase' to E15:G17, now that there is an extra 'Wind' Column?
The original range was just a two column range and if you haven't changed this then the VLOOKUP won't be able to find the third column and it will complain and fall over.

Let me know if this doesn't solve it - or indeed if it does.

Rgds

Richard,

You are a wise man and very helpful! If I have any other questions, I'll let you know. But this works perfectly! Thanks for all the help, time and effort.

Richard,

Yup, sorry I couldn't delete that comment before you saw it. It was simply extending the 'Increase' range to account for the extra column.

On another note, I do have another problem with an attached document...

In Sheet 1, I will have a long list of times, mph, and wind. In Sheet 2, I have copied and pasted (using VBA) the time column from Sheet 1. Now, I'd like to return the corresponding mph and wind of the last un-black cell into Sheet 2.

Basically, Sheet 1 consists of Actual data and Sheet 2 consists of Reported data. So as you can see on Sheet 2, at 70001 I reported mph of 1 and wind of 2 and at 70018, 9.66667 and 19. I then used a VLOOKUP to get those values to appear next to the associated time in Sheet 2. Now my problem is, I need the last time in Sheet 2 to have a value. So I'd like to pull the values from Sheet 1 that corresponds with the time.

Since the time could range, I need can't hard copy the code. In other words, the data goes to row 40 in this example but could go to 100 or 200 or .....

Any idea how to do this??

Originally Posted by Crusaders43

Richard,

Yup, sorry I couldn't delete that comment before you saw it. It was simply extending the 'Increase' range to account for the extra column.

On another note, I do have another problem with an attached document...

In Sheet 1, I will have a long list of times, mph, and wind. In Sheet 2, I have copied and pasted (using VBA) the time column from Sheet 1. Now, I'd like to return the corresponding mph and wind of the last un-black cell into Sheet 2.

Basically, Sheet 1 consists of Actual data and Sheet 2 consists of Reported data. So as you can see on Sheet 2, at 70001 I reported mph of 1 and wind of 2 and at 70018, 9.66667 and 19. I then used a VLOOKUP to get those values to appear next to the associated time in Sheet 2. Now my problem is, I need the last time in Sheet 2 to have a value. So I'd like to pull the values from Sheet 1 that corresponds with the time.

Since the time could range, I need can't hard copy the code. In other words, the data goes to row 40 in this example but could go to 100 or 200 or .....

Any idea how to do this??

Hi,

I don't understand your problem. Why do you think the VLOOKUP() won't work on row 40. These two formulae return 15 & 10 respectively in F&G 40

Please Login or Register  to view this content.

Am I missing something.....?

Rgds

Richard,

For this example, I'd like to take the last non-blank cell. In my example, the last row is 40. I could have a data fiel that runs to row 100, or 125, or so on....

In other words, I may not always know the last row the data goes too. If I use that code and the data goes to row 78, I'd only get the return from 40. Do you get what I am saying?

I'd like a universal code to pull the last row if possible, not a hard copy that pulls a specific row. Because I the number of rows will contiually change.

I hope that is understandable but please let me know if it is not.
Once again, thanks for the effort!

Originally Posted by Crusaders43

Richard,

For this example, I'd like to take the last non-blank cell. In my example, the last row is 40. I could have a data fiel that runs to row 100, or 125, or so on....

In other words, I may not always know the last row the data goes too. If I use that code and the data goes to row 78, I'd only get the return from 40. Do you get what I am saying?

I'd like a universal code to pull the last row if possible, not a hard copy that pulls a specific row. Because I the number of rows will contiually change.

I hope that is understandable but please let me know if it is not.
Once again, thanks for the effort!

Are you saying that Sheet1 might have say 100 rows, but the list on sheet 2 only 40 rows?

In which case it's presumably just a question of establishing the largest value in A on sheet1 since they are arranged incrementally. So the following VLOOKUP() should work.

Let me know if not

Please Login or Register  to view this content.

Richard,

Looking at the equation, it looks like it will work....well, it makes sense. I haven't played with it yet but will in a moment.

Back to the 'Increase rate' example, it works great just I believe it is rounding. If I use a small number such as 0.02, it seems to round down to 0. So the value goes unchanged regardless of the time. At least that is what I am getting....do you know what I am talking about?

If not, I'll add an attachment. But is there some way to midigate this problem?

Thanks again...

Originally Posted by Crusaders43

Richard,

Looking at the equation, it looks like it will work....well, it makes sense. I haven't played with it yet but will in a moment.

Back to the 'Increase rate' example, it works great just I believe it is rounding. If I use a small number such as 0.02, it seems to round down to 0. So the value goes unchanged regardless of the time. At least that is what I am getting....do you know what I am talking about?

If not, I'll add an attachment. But is there some way to midigate this problem?

Thanks again...

Hi,

I've not tested this with the actual data, but I think this is probably due to declaring the VBA variables as integers. Apart from the Loop counter variable 'x', try changing the others to be 'Double' variables. i.e. making then floating point decimals rather than integer variables.

So in the 'Dim' statement, instead of

iIncrRate As Integer

use

dIncrRate As Double

Although it's not strictly necessary, I always try and preface the variable name with something that identifies what type of variable it is. e.g. i for Integer, d for Double, st for String etc.

Regards

Richard,

Am yet to try the LargeVLOOKUP but as far as the changing Integer to Double.

At first, I changed Integer to Long because when I ran the program, I got an error saying 'overflow.' I know have changed the Long to Double and it works perfectly.

You have been of much help to me. Thank you so much, I really appreciate the time and effort!! :up:

Originally Posted by Richard Buttrey

Are you saying that Sheet1 might have say 100 rows, but the list on sheet 2 only 40 rows?

In which case it's presumably just a question of establishing the largest value in A on sheet1 since they are arranged incrementally. So the following VLOOKUP() should work.

Let me know if not

Please Login or Register  to view this content.

Richard, The code works great, but how could I translate this into VBA? The "large" function is throwing me off.....?

Much thanks!

Originally Posted by Crusaders43

Richard, The code works great, but how could I translate this into VBA? The "large" function is throwing me off.....?

Much thanks!

Hi,

I'm not quite sure what you mean by translating to VBA

VBA has a statement along the lines of:

Please Login or Register  to view this content.

But pushing this VBA VLOOKUP version into a range in the s/s isn't achieving anything more than you can do by manually entering it in the s/s.

Why is the 'Large' Function of concern? All it's doing is picking the last (hence largest) number in your list of dates, rather than you hard coding the cell value in the formula.

Perhaps I'm missing something vital.

Rgds

Originally Posted by Crusaders43

Richard, actually the VLOOKUP worked quite well.

I now am stuck on what happens if I use the VLOOKUP and my times are as follows,

235958
235959
000000
000001


In other words, the time has crossed over midnight. If this was the case, I'd like to return the values next to 000001 but with the VLOOKUP, I'd get the values next to 235959.

So I introduced something to signify a new day. So when a new day comes about, I would get this::

time: day:
235958
235959
000000 1
000001 1

And a '1' appears next to all the new times. The most times I would cross midnight is once so I use "" to signify day 1 and a '1' to signify that I have crossed into day 2.

I then used the equation: =IF(Max(day)="",*insert LargeVLOOKUP*, __ )

So the above says, if the maximum of my 'day' column equals "" then I can run the VLOOKUP that you made and it works great. Because if the Max od my day = "" then I never crossed into day 2. So I can get what I need. But I don't have a False statement for the above. In other words, if the Max(day) = 1, I'd like to return the max time where the day equals 1. I'd like to use the same VLOOKUP concept just when the criteria = 1.

Does that make sense?? Thanks in advance....

Why don't you ignore all that and simply record the full date/time decimal number. And if you only want to see the time in hrs/mins/secs, just format it as "hhmmss"

The full DateTime number is what matters, and though the hhmmss have turned round the corner to the next day and appears to be a lower number, XL nevertheless knows this is still the largest number.

HTH