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


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?

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.