Filling in a column based on certain criteria

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

[Richard Buttrey]

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

Sub IncrementMPH()
Dim x As Integer, iIncrRate As Integer, stSpeed As String, iSpeed As Integer, dTime As Double
x = Range(Range("DataTop"), Range("DataTop").End(xlDown)).Rows.Count

For x = 1 To Range(Range("DataTop"), Range("DataTop").End(xlDown)).Rows.Count
    If Range("datatop").Offset(x - 1, 1) <> "" Then
        stSpeed = Range("datatop").Offset(x - 1, 1)
        iSpeed = Left(stSpeed, Len(stSpeed) - 4)
        Range("datalist").Offset(x - 1, 0) = Format(Range("datatop").Offset(x - 1, 0), "hh:mm")
        Range("datalist").Offset(x - 1, 1) = stSpeed
        'x = x + 1
    Else
        dTime = Range("datatop").Offset(x - 1, 0)
        iIncrRate = Application.WorksheetFunction.VLookup(dTime, Range("Increase"), 2)
        Range("datalist").Offset(x - 1, 0) = Format(Range("datatop").Offset(x - 1, 0), "hh:mm")
        Range("datalist").Offset(x - 1, 1) = iSpeed + iIncrRate
        iSpeed = iSpeed + iIncrRate
    End If
    
Next x

End Sub

Regards

[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

[Richard Buttrey]

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.

Sub IncrementMPH()
Dim x As Integer, iIncrRate As Integer, iIncrWind As Integer, stSpeed As String, iSpeed As Integer, iWind As Integer, dTime As Double
x = Range(Range("DataTop"), Range("DataTop").End(xlDown)).Rows.Count

For x = 1 To Range(Range("DataTop"), Range("DataTop").End(xlDown)).Rows.Count
    If Range("datatop").Offset(x - 1, 1) <> "" Then
        iSpeed = Range("datatop").Offset(x - 1, 1)
        iWind = Range("datatop").Offset(x - 1, 2)
        Range("datalist").Offset(x - 1, 0) = Range("datatop").Offset(x - 1, 0)
        Range("datalist").Offset(x - 1, 1) = Range("datatop").Offset(x - 1, 1)
        Range("datalist").Offset(x - 1, 1) = iSpeed
        Range("datalist").Offset(x - 1, 2) = iWind
    Else
        dTime = Range("datatop").Offset(x - 1, 0)
        iIncrRate = Application.WorksheetFunction.VLookup(dTime, Range("Increase"), 2)
        iIncrWind = Application.WorksheetFunction.VLookup(dTime, Range("Increase"), 3)
        Range("datalist").Offset(x - 1, 0) = Range("datatop").Offset(x - 1, 0)
        Range("datalist").Offset(x - 1, 1) = Range("datatop").Offset(x - 1, 1)
        Range("datalist").Offset(x - 1, 1) = iSpeed + iIncrRate
        Range("datalist").Offset(x - 1, 2) = iWind + iIncrWind
        iSpeed = iSpeed + iIncrRate
        iWind = iWind + iIncrWind
    End If
    
Next x

End Sub

Regards

[Crusaders43]

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.

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

[Richard Buttrey]

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