How to copy information into a column corresponding to what is found in another column?

Question

I have wrote a macro that I want to copy a path location for a saved email and its attachments into column B, but only for when no .csv file is found. Here is my code below:

Dim csv_ap As String
Dim path_report As String

Sheets("Mail Report").Activate
csv_ap = Range("C65000").End(xlUp).Value

If csv_ap = "NO" Then
    path_report = MAIN_PATH & "For Resolution\" & Format(today, "dd_mm_yy") & "manual_handling_" & Range("A65000").End(xlUp).Value

    Range("B65000").End(xlUp).Offset(1).Value = path_report

End If

Here is the resulting spreadsheet after the macro has run. As you can see the paths are not aligned with the "NO"s in column C. I need the macro to realize the paths need to be aligned with the correct row. How can I do this? (the picture below is an example in which will be different in other cases)

Answer 1

There are many different (and possibly better) ways to approach this, but with making minimal change to your existing code you could do this:

Dim csv_ap As Range
Dim path_report As String

Sheets("Mail Report").Activate
set csv_ap = Range("C65000").End(xlUp)

If csv_ap.Value = "NO" Then
    path_report = MAIN_PATH & "For Resolution\" & Format(today, "dd_mm_yy") & "manual_handling_" & Range("A65000").End(xlUp).Value

    csv_ap.Offset(0, -1) = path_report

End If

Instead of returning a string as csv_ap, it returns a range. It then tests the value of the cell and if it equals "NO" returns the path report into the cell to the left.