excell question

• • Reply

  11 years ago

  by leeav3

  lee abrams

  Gloversville, United States

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

  Report abuse
• • Reply

  11 years ago

  by Uncle

  Uncle

  England, United Kingdom

  Joined 19 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!

  Report abuse
• • Reply

  11 years ago

  by leeav3

  lee abrams

  Gloversville, United States

  Joined 11 years ago

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

  Report abuse
• • Reply

  11 years ago

  by leeav3

  lee abrams

  Gloversville, United States

  Joined 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

  Report abuse
• • Reply

  11 years ago

  by leeav3

  lee abrams

  Gloversville, United States

  Joined 11 years ago

  obviously my typing skills match my VB skills

  Report abuse
• • Reply

  11 years ago

  by leeav3

  lee abrams

  Gloversville, United States

  Joined 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

  Report abuse
• • Reply

  11 years ago

  by leeav3

  lee abrams

  Gloversville, United States

  Joined 11 years ago

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

  etc

  Report abuse
• • Reply

  11 years ago

  by Uncle

  Uncle

  England, United Kingdom

  Joined 19 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
  

  Report abuse
• • Reply

  11 years ago

  by leeav3

  lee abrams

  Gloversville, United States

  Joined 11 years ago

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

  Report abuse
• • Reply

  11 years ago

  by leeav3

  lee abrams

  Gloversville, United States

  Joined 11 years ago

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

  Report abuse
• • Reply

  11 years ago

  by leeav3

  lee abrams

  Gloversville, United States

  Joined 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.

  Report abuse
• • Reply

  11 years ago

  by Uncle

  Uncle

  England, United Kingdom

  Joined 19 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
  

  Report abuse