none
Code Help Needed? RRS feed

  • Question

  • Hello =]

    I have written some code to filter & extract needed data from pasted PDF information, I have been able to code a macro to text2columns the information, filter and delete all unnecessary rows of information, and what I am left with is Column A & B with needed data, then this s where I get stuck. I think I know what I want VBA to do just not how to write it.

    The structure of what I am trying to do will always remain the same just with different data and amounts of data.

    Thus far the macros I have written collect all data that is in the same column, to take it to the next step the data will vary in placement within the row. I have tried tutorial videos and searching help forms but can’t find a similar example, just tad bits here and there that I have been unable to tie together to complete.

    There may be an easier way, but this is the best I have been able to come up with. In the row of data the last 2 cells (always side by side) containing numbers are what I need. Some rows they will be the last 2 cells containing data. Other rows the last cell will be a word and it will be the first cell to the left that contains a number (always) and the third case the last 2 cells in the row may contain words - but the first cell to the left that contains a number will be the needed data.

    I will try to explain what I am going for: Note*BB2 is a reference point that the data will never pass.

    Range ("BB2").End (xlToLeft).Select

    From there I would like to test if the cell. Value is a number > if true Then > select that cell and the cell to the left of it. Copy > and paste to C2

    Else if not a number > select the next cell to the left and run the same test > and do untill a number is found.

    I would want this to loop until the value of A is blank.

    Thank you ever so much! 

    Example of data:

    274660 1 FINN CRSP MLTIGRAIN CRSP BREAD / FINN CRISP PAIN MULTIGRAIN Unit Front 5.55 175.0G W 9 13 CODE FLIP
    387979 1 RYVITA CRISP BREAD HIGH FIBRE / RYVITA PAIN CROU HTE TEN/FIBRE Unit Front 7 250.0G W 12 18 CODEFLIP
    204751 1 RYVITA CRISP BREAD SESAME RYE / RYVITA PAIN CROUST SEIGLE SES. Unit Front 7.05 250.0G W 12 18  
    602523 1 RYVITA CRISP BREAD MULTIGRAIN / RYVITA PAIN CROUSTILLANT MULTI Unit Front 7 250.0G W 12 18 ARTICLE SWAP  
    901805 1 RYVITA SNACKBREAD MULTIGRAIN / RYVITA PAIN CROUST SNACK GRAIN Unit Front 90 5.12 125.0G W 8 9    
    247212 1 RYVITA SNACKBREAD / RYVITA PAIN CROUST. SNACKBREAD Unit Front 90 5.12 125.0G W 8 9 NEW      





















    Figured this much out!!

    Sub LastCol()
        
        Dim Number As Integer
                  
        Range("BB2").End(xlToLeft).Select
            If Selection.Value = Number Then
                Selection.Copy
            Else
                Selection.Offset(0, -1).Select
                If Selection.Value = Number Then
                    Selection.Copy
                Else
                Selection.Offset(0, -1).Select
                End If
                
            End If
            
    End Sub




    Wednesday, July 9, 2014 2:50 AM

Answers

  • This will insert a new column A and B, and place the values of interest in those two columns:

    Sub LastCol2()
        Dim lngR As Long
        Dim i As Integer
        
        Range("A:B").Insert
        
        For lngR = 2 To Cells(Rows.Count, "C").End(xlUp).Row
            For i = Cells(lngR, Columns.Count).End(xlToLeft).Column To 3 Step -1
                If IsNumeric(Trim(Cells(lngR, i).Value)) Then
                    Cells(lngR, i - 1).Resize(1, 2).Copy Cells(lngR, 1)
                    GoTo FoundNum
                End If
            Next i
    FoundNum:
        Next lngR
    End Sub

    • Marked as answer by Myles.at.Work Wednesday, July 9, 2014 3:31 PM
    Wednesday, July 9, 2014 3:20 PM

All replies

  • This will insert a new column A and B, and place the values of interest in those two columns:

    Sub LastCol2()
        Dim lngR As Long
        Dim i As Integer
        
        Range("A:B").Insert
        
        For lngR = 2 To Cells(Rows.Count, "C").End(xlUp).Row
            For i = Cells(lngR, Columns.Count).End(xlToLeft).Column To 3 Step -1
                If IsNumeric(Trim(Cells(lngR, i).Value)) Then
                    Cells(lngR, i - 1).Resize(1, 2).Copy Cells(lngR, 1)
                    GoTo FoundNum
                End If
            Next i
    FoundNum:
        Next lngR
    End Sub

    • Marked as answer by Myles.at.Work Wednesday, July 9, 2014 3:31 PM
    Wednesday, July 9, 2014 3:20 PM
  • Here i was trying over and over again to write this massive code!

    You kind sir are a life saver! =]

    Thank you ever so much!

    Wednesday, July 9, 2014 3:32 PM