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

[timdecker]

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:

C6=	Peak Found	14.74	13236.29
C6toC7	Peak Found	17.37	773.62
HeptaneorC7=	Peak Found	19.15	10800.4
>C7	Peak Not Found
Extra peaks found in chromatogram
		Retention Time(min)	Peak Area
		2.47	464.01			6.94	95.23
C3 042116 21.1_summary.txt
Peaks found (and not found) in chromatogram
Name	Found/Not Found	Retention Time(min)	Peak Area
Methane	Peak Not Found
Ethane	Peak Found	2.77	10.92
C5toC6	Peak Found	12.76	993.91
C6=	Peak Found	15.04	906.85
C6toC7	Peak Found	17.35	632.29
HeptaneorC7=	Peak Found	19.14	515.58
>C7	Peak Not Found
Extra peaks found in chromatogram
		Retention Time(min)	Peak Area
		2.46	423.42			4.5	56.55	56.22	2.88
C3 042116 22.1_summary.txt

<colgroup><col><col><col><col><col span="6"></colgroup><tbody>
</tbody>

Wanted Outcome:

C6=	Peak Found	14.74	13236.29
C6toC7	Peak Found	17.37	773.62
HeptaneorC7=	Peak Found	19.15	10800.4
>C7	Peak Not Found
Extra peaks found in chromatogram
		Retention Time(min)	Peak Area
		2.47	464.01
		6.94	95.23
C3 042116 21.1_summary.txt
Peaks found (and not found) in chromatogram
Name	Found/Not Found	Retention Time(min)	Peak Area
Methane	Peak Not Found
Ethane	Peak Found	2.77	10.92
C5toC6	Peak Found	12.76	993.91
C6=	Peak Found	15.04	906.85
C6toC7	Peak Found	17.35	632.29
HeptaneorC7=	Peak Found	19.14	515.58
>C7	Peak Not Found
Extra peaks found in chromatogram
		Retention Time(min)	Peak Area
		2.46	423.42
		4.5	56.55
		56.22	2.88
C3 042116 22.1_summary.txt

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

 

[timdecker]

Bump before bed.

 

[timdecker]

Morning bump!

 

[timdecker]

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

 

[skywriter]

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

 

[timdecker]

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]

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

I will post the final script once it is completed.

 

[timdecker]

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

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]

Sorry, left out the rest of the 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