Find last cell in a column with a value and move values to new columns/cells

Question

I have a macro I'm working on for my job, and I'm stumped.

In my Excel sheet, I have a column (column IE) which will have either "Monthly", "Bimonthly", or "Quarterly" in the cells in that column, depending on the account in that row (each row is a different account, and that account could be paying in any one of the 3 different ways). There can possibly be null values in some of the cells, and each time I run this macro, there will be a different amount of rows with values.

I need to move the cell values from column IE into corresponding "Monthly" (column B), "Bimonthly" (column C) and "Quarterly" (column D) columns, in the same row.

Is there a way to:

1. get to / find the end of the data / rows, in column IE (not just stopping at the first null value)

2. determine which of the 3 values is in each row of column IE

3. move those values to the correct column of that same row, depending on the value (Monthly - column B, Bimonthly - column C, or Quarterly - column D)?

Could I do something like:

Dim lastRow As Long

lastRow = ActiveSheet.Range("IE" & Rows.Count).End(xlUp).Row
Do While ActiveCell.Row <=lastRow

And then add my code to do the "cell value movement" part?

Answer 1

You could just put an IF statement into each of the columns looking at column IE, such as in B2:

=IF($IE2 = "Monthly",$IE2,"")

Then you can modify this in C and D for the other two values.

Answer 2

try something like this:

Sub yourcode()

LastRow = Range("IE50000").End(xlUp).Row

'I assume values begin at row 2

For i = 1 To LastRow
    Select Case True

    Case IsError(Cells(1 + i, "IE"))
     Nerrors = Nerrors + 1
    Case Cells(1 + i, "IE") = ""
     NBlanks = NBlanks + 1

    Case Cells(1 + i, "IE").Value = "monthly"
        m = m + 1
        Cells(1 + m, "B") = Cells(1 + i, "IE")

    Case Cells(1 + i, "IE").Value = "bimonthly"
         b = b + 1
         Cells(1 + b, "C") = Cells(1 + i, "IE")

    Case Cells(1 + i, "IE").Value = "quarter"
        q = q + 1
        Cells(1 + q, "D") = Cells(1 + i, "IE")

    End Select

Next i

End Sub

I think that's what you wanted.

Answer 3

Run the following with the sheet in question active.

Private Sub Process()

    Dim v As Variant, vOut As Variant
    Dim i As Long, j As Long, lLastRow As Long

    Const COL = 239 'IE

    With ActiveSheet
        lLastRow = .Cells(.Rows.Count, COL).End(xlUp).Row
        v = .[a1:ie1].Resize(lLastRow).Value2
        ReDim vOut(1 To lLastRow, 1 To 3)
        For i = 1 To UBound(v)
            If Len(v(i, 2)) Then vOut(i, 1) = v(i, 2)
            If Len(v(i, 3)) Then vOut(i, 2) = v(i, 3)
            If Len(v(i, 4)) Then vOut(i, 3) = v(i, 4)
            If Len(v(i, COL)) Then
                Select Case LCase$(v(i, COL))
                    Case "monthly":   vOut(i, 1) = v(i, COL)
                    Case "bimonthly": vOut(i, 2) = v(i, COL)
                    Case "quarterly": vOut(i, 3) = v(i, COL)
                End Select
            End If
        Next
        .[a1].Offset(, 1).Resize(lLastRow, 3) = vOut
    End With

End Sub