0

I have an Excel Table, with some columns. But at the moment a have a problem with column Duration.

When I scrolled down the table, i have unexpectedly noticed, that many IDs have empty lines, and only one line of this ID has an actual value.

Is it possible to fill up other empthy lines with this only one existing value using VBA? That means, that all empty values for ID6979960 should be filled up with a value 42:15:56, and so on.

Without that, my other calculations in my table, don't work properly.

I don't know exactly how copying of values works in VBA.

Table Example:ID,Duration

R3uK
  • 14,417
  • 7
  • 43
  • 77
Izzy Schneider
  • 17
  • 5

2 Answers2

1
    Public Sub FillEmpty()
       Dim finded As Range
       Dim Sheet As Worksheet
       Set Sheet = ActiveSheet 'or any other sheet -> .Sheets("")
       With Sheet
          lastrow = .Cells(1, 1).End(xlDown).Row
          For i = 1 To lastrow
          If StrComp(.Cells(i, 2).Value, "") = 0 Then
            Set finded = .Columns(2).Find("*", after:=.Cells(i, 2), LookIn:=xlValues)
            ID = .Cells(finded.Row, 1).Value
            Filler = .Cells(finded.Row, 2).Text
            Else
                 ID = .Cells(i, 1).Value
                 Filler = .Cells(i, 2).Text
          End If
          Index = i
          While ID = .Cells(Index, 1).Value
            .Cells(Index, 2).Value = Filler
            Index = Index + 1
          Wend
          Next i
      End With
End Sub

Made it real quick so not the most optimal solution. I tested it with your example and it works. Not sure with many more rows. Check it and let me know if it works for you.

AngelMdez
  • 170
  • 1
  • 10
  • See this for finding last row `xlDown` is not the best option! ;) http://stackoverflow.com/a/11169920/4628637 and http://stackoverflow.com/documentation/excel-vba/918/methods-for-finding-the-last-used-row-or-column-in-a-worksheet#t=201702151106496482673 – R3uK Feb 15 '17 at 11:07
  • 1
    Wow, i was doing it the entire time at work using the xlDown method. You just make me recode a lot of stuff hahaha – AngelMdez Feb 15 '17 at 11:14
  • @AngelMdez I have tried this code, it gives me an error "Next without for". Maybe I should add End If anywhere? – Izzy Schneider Feb 15 '17 at 11:16
  • @Izzy Schneider Yeah i missclick and delete an "End If" just before "Index = i" and edited in the code. You can just put the End If there or copy the new edited code. Let me know how it goes! – AngelMdez Feb 15 '17 at 11:17
  • @AngelMdez well, it worked only with the first ID with empty rows, then I have checked if Macro, filled other IDs up, but it didn't. I also deleted again some of the values in other ID, so it's the first ID with empty values, and it worked again, but after that it doesn't work. – Izzy Schneider Feb 15 '17 at 11:26
  • @Izzy Schneider Add this: Else ID = .Cells(i, 1).Value Filler = .Cells(i, 2).Text Just before the "End If". I edit the new code. – AngelMdez Feb 15 '17 at 11:39
  • @AngelMdez nice, now it works exactly as I expected. Thanks for your effort. – Izzy Schneider Feb 15 '17 at 11:44
  • @AngelMdez could you please tell me, what should i change in code, if for example my data not in column B but in column F. Everything what is 2 I have to change it to 6? – Izzy Schneider Feb 15 '17 at 13:42
  • @Izzy Schneider yeah,numers "1" and "2" reference collums. Change 1 if you change your "ID", change "2" if you change your "29:28:12" data. (Fe) In your case if you change 2 for 6 it should work. – AngelMdez Feb 16 '17 at 10:33
0
Sub fillerv2()
rowscnt = 1000
tmi = 1
tm = ""
For i = 1 To rowscnt
If tm <> Cells(i, 1).Value Then
    For o = tmi To i - 1
        If IsEmpty(Cells(o, 2).Value) = False Then
            Pattern = Cells(o, 2).Value
            Exit For
        End If
    Next o
    For o = tmi To i - 1
        Cells(o, 2).Value = Pattern
    Next o
    tm = Cells(i, 1).Value
    tmi = i
End If
Next i
Artemi A.
  • 36
  • 5
  • 1
    This isn't what the OP asked for, it will fill all blank cells with a pre set value, not with the correct value for that ID. – Tim Wilkinson Feb 15 '17 at 10:20
  • Yes, this is not actually what I have asked for. – Izzy Schneider Feb 15 '17 at 10:25
  • oh, you mean, that if ID7777 has value "1" in third row for example, all other rows with ID7777 must have value "1"? There s no problem, if you use vba, but there one question for optimization issue: Is all same IDs sorted and arranged one by one? – Artemi A. Feb 15 '17 at 10:41
  • @ArtemiA. Yes, that's what I meant. As you said, this only value has to be copied in other rows with the same ID. And yes the are sorted and arranged one by one. – Izzy Schneider Feb 15 '17 at 10:47
  • Edited my answer. That will works, if you have problems with speed of alghoritm, use arrays instead of cells – Artemi A. Feb 15 '17 at 11:46