Copying cell content if target cell is blank

KoE

Board Regular
Joined
Dec 6, 2004
Messages
51
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
BDO
5Muhammad Fadhli bin Mat SutrisPenolong Pendakwa Raya Unit Pendakwaan Negeri Johor (Mahkamah Sesyen Muar) Gred L41 (Kontrak)*
6Natassa binti ZainiPenolong Pendakwa Raya Unit Pendakwaan Negeri Johor (Mahkamah Sesyen Kota Tinggi) Gred L41 (Kontrak)*
7Zurina binti SidikPenolong 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)
8Nor Hafizah binti AbdullahPenolong 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 :)
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
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.
 
Upvote 0
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 ?
 
Upvote 0
here's the solution that i've received from another board. Thought it might help someone else here :)

Code:
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
 
Upvote 0
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:

Code:
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!
 
Upvote 0
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
 
Upvote 0
Greg,

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

Here's the codes after Greg's input

Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,553
Messages
6,114,279
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top