excell question

help , vba , excell Syracuse, United States
  • 11 years ago

    I was wondering if it possible to create a macro, that would run down a column, check if data ia sequential or not, if so move the last number of the sequential list to a specific colum and delete the numbers in between?

  • 11 years ago

    This code will extract the sequential numbers of form AB001, AB002 etc. from the first 10 rows of column 1 and put them in column 4. You should be able to adapt it to the number format you're using, then delete column 1 when you're finished.

    Sub SequencerMacro()
    
    Dim i As Integer
    Dim j As Integer
    Dim intCount As Integer
    Dim intValue As Integer
    Dim intStart As Integer
    
    
    intCount = 1
    For i = 1 To 10
        'find first number in sequence
        If Left(Cells(i, 1).Value, 2) = "AB" Then
            'get numeric part
            intValue = CInt(Right(Cells(i, 1).Value, 3))
            'copy value to new column
            Cells(intCount, 4).Value = Cells(i, 1).Value
            'increment counters
            intCount = 2
            intStart = i
            Exit For
        End If
    Next i
    
    'now find the rest of the values and copy as necessary
    For i = intStart To 10
        If Left(Cells(i, 1).Value, 2) = "AB" Then
            If CInt(Right(Cells(i, 1).Value, 3)) = intValue + 1 Then
                Cells(intCount, 4).Value = Cells(i, 1).Value
                intCount = intCount + 1
                intValue = intValue + 1
            End If
        End If
    Next i
    
    End Sub
    

    Hope this helps!

  • 11 years ago

    This is awesome thanks. I definitely give me a great starting point.

  • 11 years ago

    This is awesome thanks. I definitely give me a great starting point. The only think i run into is if there is a break insequence, it doesn not restat at the next value conating AB

  • 11 years ago

    obviously my typing skills match my VB skills

  • 11 years ago

    Ok he're my example data:

    NAT 229534 444 229535 NAT 229536 NAT 229537 444 229538 NAT 229539 444 229540 NAT 229541 NAT 229542 444 229543 NAT 229544 444 229545 444 229546

    so if a1 = a2 and b1 and b2 are sequential then move the last sequential value to c(x) where x is the row of the 1st value in the range and remove row (y) being the row of the last value of the range. Repeat through Range A:A. I'm not really vb savvy, I can read what you are doing, but don't know the commands to do what I want. Thanks

  • 11 years ago

    that was supposed to columnar data NAT 229534 444 229535 444 229536

    etc

  • 11 years ago

    I'm not 100 per cent sure what you mean, but this will take each cell that starts with NAT and copy the NAT and the second column of data to columns 4 and 5:

    Sub SequencerMacro()
    
    Dim i As Integer
    Dim j As Integer
    Dim intCount As Integer
    Dim lngValue As Long
    
    intCount = 1
    For i = 1 To 10
        'find cells with NAT in col 1
        If Left(Cells(i, 1).Value, 3) = "NAT" Then
            'copy values to new column
            Cells(intCount, 4).Value = Cells(i, 1).Value
            Cells(intCount, 5).Value = Cells(i, 2).Value
            'increment counter
            intCount = intCount + 1
        End If
    Next i
    
    End Sub
    
  • 11 years ago

    nat 225234 nat 225235 nat 225236 478 123456 478 123457 345 234567 345 234569

  • 11 years ago

    nat 225234 nat 225235 nat 225236 478 123456 478 123457 345 234567 345 234569

  • 11 years ago

    unfortuenatly i can't get my reply to show in columns, so'it s a bit difficult to show the original then what i want to do with the data. The original code was great in that i was able to acomplish much the same thing; however, when i would come a break in the sequence , the code would stop. So if I did for i = 1 to 21 and only 1 to 10 were in sequence it would stop with 10. So 11 is not in sequnece, but 12 through 14 may be in sequnce. I don't know if that explains it any more. I'm thinking there should be a nested loop somewhere that resetart for every out odf sequence cell. I'm not sure if that explination just makes everything more murky.

  • 11 years ago

    You can use the second one to extract the relevant cells to columns D and E then sort on column E:

    Sub SequencerMacro()
    
    Dim i As Integer
    Dim j As Integer
    Dim intCount As Integer
    Dim lngValue As Long
    
    intCount = 1
    For i = 1 To 10
        'find cells with NAT in col 1
        If Left(Cells(i, 1).Value, 3) = "NAT" Then
            'copy values to new column
            Cells(intCount, 4).Value = Cells(i, 1).Value
            Cells(intCount, 5).Value = Cells(i, 2).Value
            'increment counter
            intCount = intCount + 1
        End If
    Next i
    
        Columns("D:E").Select
        Selection.Sort Key1:=Range("E1"), Order1:=xlAscending, Header:=xlGuess, _
            OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
            DataOption1:=xlSortNormal
    
    End Sub
    

Post a reply

Enter your message below

Sign in or Join us (it's free).

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“Measuring programming progress by lines of code is like measuring aircraft building progress by weight.” - Bill Gates