[VBA] If IsNull = False - Help (Offset/Shift/Move Value)

timdecker · Apr 26, 2016

I am having an issue manipulating a flat file. I sometimes have values in extended columns, rather than below onto the next row. How can I shift/offset these values to the rows below. I have 2 grids below, one with an example as it is now and another of what I would like to occur. The values that change will be in red. The values in red are sporadic, in terms of which flat file they show up in.

Thanks!

Example File:
[TABLE="class: grid, width: 721"]
<colgroup><col><col><col><col><col span="6"></colgroup><tbody>[TR]
[TD]C6=[/TD]
[TD] Peak Found[/TD]
[TD="align: right"]14.74[/TD]
[TD="align: right"]13236.29[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C6toC7[/TD]
[TD] Peak Found[/TD]
[TD="align: right"]17.37[/TD]
[TD="align: right"]773.62[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]HeptaneorC7=[/TD]
[TD] Peak Found[/TD]
[TD="align: right"]19.15[/TD]
[TD="align: right"]10800.4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]>C7[/TD]
[TD] Peak Not Found[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 3"]Extra peaks found in chromatogram[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD] [/TD]
[TD] Retention Time(min)[/TD]
[TD] Peak Area[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD] [/TD]
[TD="align: right"]2.47[/TD]
[TD="align: right"]464.01[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]6.94[/TD]
[TD="align: right"]95.23[/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]C3 042116 21.1_summary.txt[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 4"]Peaks found (and not found) in chromatogram[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD] Found/Not Found[/TD]
[TD] Retention Time(min)[/TD]
[TD] Peak Area[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Methane[/TD]
[TD] Peak Not Found[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Ethane[/TD]
[TD] Peak Found[/TD]
[TD="align: right"]2.77[/TD]
[TD="align: right"]10.92[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C5toC6[/TD]
[TD] Peak Found[/TD]
[TD="align: right"]12.76[/TD]
[TD="align: right"]993.91[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C6=[/TD]
[TD] Peak Found[/TD]
[TD="align: right"]15.04[/TD]
[TD="align: right"]906.85[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C6toC7[/TD]
[TD] Peak Found[/TD]
[TD="align: right"]17.35[/TD]
[TD="align: right"]632.29[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]HeptaneorC7=[/TD]
[TD] Peak Found[/TD]
[TD="align: right"]19.14[/TD]
[TD="align: right"]515.58[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]>C7[/TD]
[TD] Peak Not Found[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 3"]Extra peaks found in chromatogram[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD] [/TD]
[TD] Retention Time(min)[/TD]
[TD] Peak Area[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD] [/TD]
[TD="align: right"]2.46[/TD]
[TD="align: right"]423.42[/TD]
[TD] [/TD]
[TD][/TD]
[TD="align: right"]4.5[/TD]
[TD="align: right"]56.55[/TD]
[TD="align: right"]56.22[/TD]
[TD="align: right"]2.88[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]C3 042116 22.1_summary.txt[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Wanted Outcome:
[TABLE="class: grid, width: 337"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]C6=[/TD]
[TD] Peak Found[/TD]
[TD="align: right"]14.74[/TD]
[TD="align: right"]13236.29[/TD]
[/TR]
[TR]
[TD]C6toC7[/TD]
[TD] Peak Found[/TD]
[TD="align: right"]17.37[/TD]
[TD="align: right"]773.62[/TD]
[/TR]
[TR]
[TD]HeptaneorC7=[/TD]
[TD] Peak Found[/TD]
[TD="align: right"]19.15[/TD]
[TD="align: right"]10800.4[/TD]
[/TR]
[TR]
[TD]>C7[/TD]
[TD] Peak Not Found[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 3"]Extra peaks found in chromatogram[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD] [/TD]
[TD] Retention Time(min)[/TD]
[TD] Peak Area[/TD]
[/TR]
[TR]
[TD][/TD]
[TD] [/TD]
[TD="align: right"]2.47[/TD]
[TD="align: right"]464.01[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]6.94[/TD]
[TD="align: right"]95.23[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]C3 042116 21.1_summary.txt[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 4"]Peaks found (and not found) in chromatogram[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD] Found/Not Found[/TD]
[TD] Retention Time(min)[/TD]
[TD] Peak Area[/TD]
[/TR]
[TR]
[TD]Methane[/TD]
[TD] Peak Not Found[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Ethane[/TD]
[TD] Peak Found[/TD]
[TD="align: right"]2.77[/TD]
[TD="align: right"]10.92[/TD]
[/TR]
[TR]
[TD]C5toC6[/TD]
[TD] Peak Found[/TD]
[TD="align: right"]12.76[/TD]
[TD="align: right"]993.91[/TD]
[/TR]
[TR]
[TD]C6=[/TD]
[TD] Peak Found[/TD]
[TD="align: right"]15.04[/TD]
[TD="align: right"]906.85[/TD]
[/TR]
[TR]
[TD]C6toC7[/TD]
[TD] Peak Found[/TD]
[TD="align: right"]17.35[/TD]
[TD="align: right"]632.29[/TD]
[/TR]
[TR]
[TD]HeptaneorC7=[/TD]
[TD] Peak Found[/TD]
[TD="align: right"]19.14[/TD]
[TD="align: right"]515.58[/TD]
[/TR]
[TR]
[TD]>C7[/TD]
[TD] Peak Not Found[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 3"]Extra peaks found in chromatogram[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD] [/TD]
[TD] Retention Time(min)[/TD]
[TD] Peak Area[/TD]
[/TR]
[TR]
[TD][/TD]
[TD] [/TD]
[TD="align: right"]2.46[/TD]
[TD="align: right"]423.42[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4.5[/TD]
[TD="align: right"]56.55[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]56.22[/TD]
[TD="align: right"]2.88[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]C3 042116 22.1_summary.txt[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

timdecker · Apr 26, 2016

Bump before bed.

timdecker · Apr 27, 2016

Morning bump!

timdecker · Apr 27, 2016

Lunch time bump, seeing as it was on page 9 =/

skywriter · Apr 27, 2016

You are forgetting to disclose that you have this cross posted at 2 other sites.

timdecker · Apr 27, 2016

skywriter said:
You are forgetting to disclose that you have this cross posted at 2 other sites.

This was the first place I posted...so there was no cross post in the beginning...but, sorry. Only OZ had it in big bold writing. Not trying to hide anything...it's the internet.

Anyways, the others can be found here:
[VBA - Help] Offset/Move Value - Details Inside...
[VBA] Offset/Move Values

timdecker · Apr 28, 2016

This thread is currently being updated @ [VBA - Help] Offset/Move Value - Details Inside...

I will post the final script once it is completed.

timdecker · Apr 28, 2016

Well, thanks for getting me on the right track everyone, especially 6StringJazzer @ EF. Here is the final product:

Code:

Dim row, col, loopCounter As Integer
row = 1
loopCounter = 1
Do While (row <= LastRow)
    For col = 5 To lColumn
        If (col Mod 2 <> 0) Then
            If (Cells(row, col).Value <> "") Then
                'Debug.Print row & "," & col
                Cells(row, col).Offset(loopCounter).EntireRow.Insert
                Cells(row + loopCounter, 3) = Cells(row, col)
                Cells(row + loopCounter, 4) = Cells(row, col + 1)
                loopCounter = loopCounter + 1
                'Debug.Print "counter " & loopCounter
            End If
        End If
    Next col
    LastRow = LastRow + loopCounter - 1
    'Debug.Print "new last row = " & LastRow
    loopCounter = 1
    row = row + 1


Loop

timdecker · Apr 28, 2016

Sorry, left out the rest of the code:

Code:

Sub M()
     
    Dim LastRow As Long
    Dim lColumn As Long 'last column
    Dim lc As Integer
    Dim row As Integer
    Dim col As Integer
    Dim loopCounter As Integer


    lColumn = ActiveSheet.UsedRange.column + ActiveSheet.UsedRange.Columns.Count - 1
    LastRow = ActiveSheet.Cells(Rows.Count, "G").End(xlUp).row
    
    'save last column number in used range
    lc = ActiveSheet.Cells.SpecialCells(xlLastCell).column


    Columns(9).Delete
    Columns(8).Delete
    
    'Loop for each column number until it equals 0
    Do Until lc = 0
        If WorksheetFunction.CountA(Columns(lc)) = 0 Then
            Columns(lc).Delete
        End If
        'proceeds to the next lowest column number
        lc = lc - 1
        
    Loop


    row = 1
    loopCounter = 1
    Do While (row <= LastRow)
        For col = 5 To lColumn
            If (col Mod 2 <> 0) Then
                If (Cells(row, col).value <> "") Then
                    'Debug.Print row & "," & col
                    Cells(row, col).Offset(loopCounter).EntireRow.Insert
                    Cells(row + loopCounter, 3) = Cells(row, col)
                    Cells(row + loopCounter, 4) = Cells(row, col + 1)
                    loopCounter = loopCounter + 1
                    'Debug.Print "counter " & loopCounter
                End If
            End If
        Next col
        LastRow = LastRow + loopCounter - 1
        Debug.Print "new last row = " & LastRow
        loopCounter = 1
        row = row + 1
    
    Loop
    
End Sub

[VBA] If IsNull = False - Help (Offset/Shift/Move Value)

timdecker

New Member

timdecker

New Member

timdecker

New Member

timdecker

New Member

skywriter

Well-known Member

timdecker

New Member

timdecker

New Member

timdecker

New Member

timdecker

New Member

Share this page

[VBA] If IsNull = False - Help (Offset/Shift/Move Value)

New Member

New Member

New Member

New Member

Well-known Member

New Member

New Member

New Member

New Member

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock