Community discussion forum

Back to discussion

excell question

Last post was 21 Oct 2009 at 13:55
Tags: excell, help, vba Syracuse, United States
  • 2 months 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?

    Post was edited on 23/09/2009 19:33:35 Report abuse
  • 2 months 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!

    Report abuse
  • 1 month ago

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

    Report abuse
  • 1 month 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

    Report abuse
  • 1 month ago

    obviously my typing skills match my VB skills

    Report abuse
  • 1 month 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

    Post was edited on 05/10/2009 20:44:38 Report abuse
  • 1 month ago

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

    etc

    Report abuse
  • 1 month 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
    Report abuse
  • 1 month ago

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

    Report abuse
  • 1 month ago

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

    Report abuse
  • 1 month 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.

    Report abuse
  • 1 month 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
    Report abuse

Post a reply

Enter your message below

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

Ask a question

Related discussion

Quick links

Recent activity

Languages

Web Development

Frameworks & Architecture

Other major sections

Cancel
We'd love to hear what you think! Submit ideas or give us feedback