IF Cell is BLANK Copy & Paste Value From Another Cell With a MACRO

[Buns1976]

Good Morning Folks,
In my worksheet(SHIFT) I have a range of cells(A4-A35) that are imported. Occasionally there are cells that are BLANK. I need to run a formula in a macro that tests A4-A35 for BLANKS. Example if A4 were BLANK, It copies N4 and paste special, value in A4 and so forth to A35.

Any help would be appreciated.

Thanks,
B

 

[rollis13]

This macro with loop could be a simple solution as long as the blank cells in column A are 'really' empty. Imported cells may contain non visible characters if massively imported.

Option Explicit
Sub CopyOnBlanks()
    Dim x      As Long
    For x = 4 To 35
        With Sheets("SHIFT")
            If .Cells(x, "A") = "" Then .Cells(x, "A") = .Cells(x, "N").Value
        End With
    Next x
End Sub

 

[Buns1976]

This macro with loop could be a simple solution as long as the blank cells in column A are 'really' empty. Imported cells may contain non visible characters if massively imported.

Option Explicit
Sub CopyOnBlanks()
    Dim x      As Long
    For x = 4 To 35
        With Sheets("SHIFT")
            If .Cells(x, "A") = "" Then .Cells(x, "A") = .Cells(x, "N").Value
        End With
    Next x
End Sub

That works well! Thank you!! I wonder if we could add this to the macro after the copy and paste? In column H4-H35 I have this formula =MID(A4,10,3). If cell A4 is blank and the macro does the copy & paste then change the formula in H4 to =MID(A4,10,3)+1 and so forth to H35?

 

[rollis13]

Do you mean like this ?

Option Explicit
Sub CopyOnBlanks()
    Dim x      As Long
    For x = 4 To 35
        With Sheets("SHIFT")
            If .Cells(x, "A") = "" Then
                .Cells(x, "A") = .Cells(x, "N").Value
                .Cells(x, "H").FormulaR1C1 = "=MID(RC[-7],10,3)+1"
            End If
        End With
    Next x
End Sub

 

[Buns1976]

Do you mean like this ?

Option Explicit
Sub CopyOnBlanks()
    Dim x      As Long
    For x = 4 To 35
        With Sheets("SHIFT")
            If .Cells(x, "A") = "" Then
                .Cells(x, "A") = .Cells(x, "N").Value
                .Cells(x, "H").FormulaR1C1 = "=MID(RC[-7],10,3)+1"
            End If
        End With
    Next x
End Sub

Perfect!! Thank you so much!!

 

[rollis13]

Thanks for the positive feedback, glad having been of some help.

Ps. sure you got the solution mark on the right post ?

 

[Buns1976]

Thanks for the positive feedback, glad having been of some help.

Ps. sure you got the solution mark on the right post ?

I Jumped the gun! The formula in the macro (.Cells(x, "H").FormulaR1C1 = "=MID(RC[-7],10,3)+1") needs be the + the value in J4-J35 instead of "+1". Is that doable?

 

[rollis13]

Certainly, instead of +1 use +RC[2]

 

[Buns1976]

I Jumped the gun! The formula in the macro (.Cells(x, "H").FormulaR1C1 = "=MID(RC[-7],10,3)+1") needs be the + the value in J4-J35 instead of "+1". Is that doable?

Disregard for now. I believe I got it! Thanks again!

 

[Buns1976]

Certainly, instead of +1 use +RC[2]

Yep I got it. Thank you!