Help with VBA code if cell is blank in column C put in formula, then convert to value

[willow1985]

Hello,

I am looking to clean up some data in another Macro I have and am trying to figure out the code for one part of the clean up.
Basically in column C there are ship dates and in column B there are due dates. When there is a blank cell in column C I want it to bring over the value from column B.

The below code I have is giving me an error so I am missing something...

Dim rCell As Range

For Each rCell In Range("C2:C")

    If rCell.Value = "" Then

        rCell.Formula = "=[@[Due Date]]"
        rCell.Value = rCell.Value
        
    End If
    
Next rCell

Any help would be appreciated.

Thank you!

 

[Rick Rothstein]

I have not looked at your code in any detail, but the following caught my eye right away. This line of code...

For Each rCell In Range("C2:C")

has no row number after the second C... you cannot have an open-ended range like that. By the way for future reference, it is always a good idea to tell us the error number and its description and the line of code the program stopped on so we don't have to guess what or where your problem is.

 

[Fluff]

You need to specify the last row of data on this line

For Each rCell In Range("C2:C")

 

[willow1985]

You need to specify the last row of data on this line

For Each rCell In Range("C2:C")

the last row of data could vary. Is there a line I could write that would determine which is the last row of data to apply this to?

 

[Rick Rothstein]

Do you have a column that is guaranteed to have the last row of data in it?

 

[willow1985]

Do you have a column that is guaranteed to have the last row of data in it?

Yes, Column G

 

[Fluff]

As it appears that your data is in a table, what is the name of the table & what is the name of the column where you want the values

 

[willow1985]

As it appears that your data is in a table, what is the name of the table & what is the name of the column where you want the values

The name of the table is "SR" and
column C is: "Ship Date"
Column B is: "Due Date"
Column G is: "Part Number"

Thank you very much!

 

[Fluff]

In that case use

For Each rCell In ActiveSheet.ListObjects("SR").ListColumns("Ship Date").DataBodyRange

 

[willow1985]

In that case use

For Each rCell In ActiveSheet.ListObjects("SR").ListColumns("Ship Date").DataBodyRange

I tried the below code but it seems to freeze/take an extremely long time. My data currently has 4925 rows. Is there maybe a better way of doing this?
I was unable to test it before as I was getting an error but as is now this part will definitely slow down the macro...

Dim rCell As Range

For Each rCell In ActiveSheet.ListObjects("SR").ListColumns("Ship Date").DataBodyRange

    If rCell.Value = "" Then

        rCell.Formula = "=[@[Due Date]]"
        rCell.Value = rCell.Value
        
    End If
    
Next rCell