Copying cell content if target cell is blank

[KoE]

Greetings,

I'm looking to copy cell content from cells in column D into column O if target cell is blank.

I tried <code>=IF (O2="";D2;O2)</code> but that doesn't work. Do I need vba?

Sample data as below:

Excel Workbook
	B	D	O
5	Muhammad Fadhli bin Mat Sutris	Penolong Pendakwa Raya Unit Pendakwaan Negeri Johor (Mahkamah Sesyen Muar) Gred L41 (Kontrak)	*
6	Natassa binti Zaini	Penolong Pendakwa Raya Unit Pendakwaan Negeri Johor (Mahkamah Sesyen Kota Tinggi) Gred L41 (Kontrak)	*
7	Zurina binti Sidik	Penolong Pendakwa Raya Unit Pendakwaan Negeri Johor (Mahkamah Sesyen Johor Bahru) Gred L41 (Kontrak)	Penolong Pendakwa Raya Unit Pendakwaan Negeri Johor (Mahkamah Sesyen Batu Pahat) Gred L41 (Kontrak)
8	Nor Hafizah binti Abdullah	Penolong Pendakwa Raya Unit Pendakwaan Negeri Johor (Mahkamah Majistret Batu Pahat) Gred L41 (Kontrak)	Penolong Pendakwa Raya Unit Pendakwaan Negeri Johor (Mahkamah Sesyen Muar) Gred L41 (Kontrak)
johor

Thanks a millions

 

[Greg Truby]

Yes, you need to use VBA. Your example formula would create a circular reference. Give the programmign a shot and let us know if you get stuck.

 

[KoE]

Greg,

thanks for a quick reply

i know there's no free lunch...but trying to construct one based on excel 2007 vba programming for dummies makes me even dumber.

would you mind helping me on this pls ?

 

[KoE]

here's the solution that i've received from another board. Thought it might help someone else here

Sub COPYDO() 
    Application.ScreenUpdating = False 
    Dim LastRow As Long 
    With ActiveSheet 
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row 
        For i = LastRow To 1 Step -1 
             
            If .Cells(i, "O").Value = "" Then 
                .Cells(i, "O").Value = .Cells(i, "D").Value 
            End If 
             
        Next i 
    End With 
    Columns("O:O").Select 
    Selection.WrapText = True 
    Application.ScreenUpdating = True 
End Sub

 

[KoE]

The above code works well for a single worksheet but not when i tried to span across several selected sheets.

I changed With-End construct with For Each-Next and I got stuck there

I'd be highly grateful if someone would point to my mistakes.

Below are the errorneous codes:

Option Explicit

Sub CopyPosting()
    Application.ScreenUpdating = False
    Dim sh As Worksheet
    Dim i As Long
    Dim LastRow As Long
    On Error GoTo 0
    
    For Each sh In ActiveWorkbook.Sheets(Array("johor", "pulau pinang", "sabah", "sarawak", "selangor", "terengganu", "kedah", "kelantan", "melaka", "negeri sembilan", "pahang", "perak", "perlis", "ibu pejabat", "wp kl"))
        LastRow = Cells(Rows.Count, "A").End(xlUp).Row
        For i = LastRow To 1 Step -1
             
            If Cells(i, "O").Value = "" Then
                Cells(i, "O").Value = Cells(i, "D").Value
            End If
             
        Next i
    
        Columns("O:O").Select
        Selection.WrapText = True
        Application.ScreenUpdating = True
    
    Next

End Sub

Thanks!

 

[Greg Truby]

You were very close. Go back to your original code and just change activesheet to sh and then drop the whole With/End With inside your For Each/Next

Also, JFYI, you do not need to loop backward here. Looping backwards is only needed if you are deleting rows/columns/cells etc. In this instance you could simply have written For i = 1 to lastrow

 

[KoE]

Greg,

Thanks a million Wasn't a cake to comprehend but finally...success LOL!

Here's the codes after Greg's input

Sub CopyPosting()
    Application.ScreenUpdating = False
    Dim sh As Worksheet
    Dim i As Long
    Dim LastRow As Long
    On Error GoTo 0
    
    For Each sh In ActiveWorkbook.Sheets(Array("johor", "pulau pinang", "sabah", "sarawak", "selangor", "terengganu", "kedah", "kelantan", "melaka", "negeri sembilan", "pahang", "perak", "perlis", "ibu pejabat", "wp kl"))
        With sh
            LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
                For i = 1 To LastRow
                    If .Cells(i, "O").Value = "" Then
                    .Cells(i, "O").Value = .Cells(i, "D").Value
                    End If
                Next i
        End With
    
    Columns("O:O").Select
    Selection.WrapText = True
    Application.ScreenUpdating = True
    
    Next

End Sub