Fill in blank cells with adjacent cell value

[MichalK86]

Hi Everyone,

I am pretty new to Excel and VB (got some basics ).
I was wondering if someone can help.


So I have a column G containing cells of different values(times); some of those cells are blank. I also have an adjacent column F that doesn't have any blank values (all filled with time values)


What I am trying to do is use VBA or formula to fill in the empty cells in column G with the value of their adjacent column F cells. As below:

Column F Column G
11 Dec 2018 17:00 11 Dec 2018 18:40
26 Nov 2018 16:54 26 Nov 2018 18:40
18 Jan 2019 11:59
17 Jan 2019 17:06
17 Jan 2019 17:02 17 Jan 2019 17:58
16 Jan 2019 16:47 16 Jan 2019 16:50


I know it was mentioned before in this forum, but wanted someone to talk me through if possible.

Thanks in advance

 

[MARK858]

Maybe (if the cells are truly blank)...

Sub FillG()
    Columns("G:G").SpecialCells(4).FormulaR1C1 = "=RC[-1]"
End Sub

or

Sub FillG2()
    With Columns("G:G").SpecialCells(4)
        .FormulaR1C1 = "=RC[-1]"
        .Value = .Value
        .NumberFormat = "DD/MM/YYYY HH:MM"
    End With
End Sub

 

[MichalK86]

Hi thank you for such a quick reply. the cells are blank sorry about the formatting of the table, poor example.
should insert the code in a specific place in the document? sorry about the silly question

 

[MARK858]

In a regular module i.e. ALT + F11, paste the code in the empty window (if the window isn't empty click Insert - Module).
To run click anywhere inside the code and press F5.

 

[MichalK86]

In a regular module i.e. ALT + F11, paste the code in the empty window (if the window isn't empty click Insert - Module).
To run click anywhere inside the code and press F5.

Code works a treat. Just trying to figure out how to insert this code while recording a Macro thanks again

 

[MARK858]

Just trying to figure out how to insert this code while recording a Macro

I take you mean how to insert the code into a macro after you have recorded it

 

[MichalK86]

mo i wanted to add the code while recording.. but i now know its not possible to do that i will have to find the way of adding this code to the recorded macro. Ive done VB course 10 years ago and started to work with Excel just few months ago so it will be a long and fun journey but i fell like i know a lot more than a month ago and learning process is fun really appreciate your help

 

[MARK858]

If you get stuck post back both codes (and a description of what you want to happen) and we will have a look at it.

Btw, welcome to the forum

 

[MichalK86]

thanks again, i wont be bothering you guys with silly stuff time to refresh my knowledge and learn a bit more about excel first

 

[MichalK86]

If you get stuck post back both codes (and a description of what you want to happen) and we will have a look at it.

Btw, welcome to the forum

One More questions if thats ok

Created this Macro in Excel to sort some data in columns and calculate simple times but every time i run it on a new worksheet the Column which suppose to be sorted is changing so instead of Column D it now sort C.
no idea why

Also is there any way to run the macro automatically when you pasting the data from another workbook?

Thanks

Sub finaltest()
'
' finaltest Macro
'


'
    
    With Columns("G:G").SpecialCells(4)
        .FormulaR1C1 = "=RC[-1]"
        .Value = .Value
        .NumberFormat = "DD/MM/YYYY HH:MM"
    End With
    
    Columns("C:C").Select
    Application.CutCopyMode = False
    Selection.ClearContents
    Selection.ColumnWidth = 14.14
    Columns("D:D").Select
    ActiveWorkbook.Worksheets("Sheet4").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet4").Sort.SortFields.Add Key:=Range("D1"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet4").Sort
        .SetRange Range("D2:H1943")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Selection.ColumnWidth = 10.71
    Columns("E:E").ColumnWidth = 14.43
    Columns("E:E").ColumnWidth = 18.14
    Columns("F:F").ColumnWidth = 17.14
    Columns("G:G").ColumnWidth = 15.71
    Columns("H:H").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("H1").Select
    ActiveCell.FormulaR1C1 = "Contractor Hours"
    Range("H2").Select
    Columns("H:H").ColumnWidth = 17.57
    Range("H2").Select
    ActiveCell.FormulaR1C1 = ""
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "Full Name "
    Range("C2").Select
    ActiveCell.FormulaR1C1 = "=RC[-2]&"" """
    Range("C2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=RC[-2]&"" ""&RC[-1]"
    Range("C2").Select
    Selection.AutoFill Destination:=Range("C2:C1943")
    Range("C2:C1943").Select
    Range("H2").Select
    ActiveCell.FormulaR1C1 = "=RC[-3]"
    Range("H2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=RC[-1]-RC[-3]"
    Range("H2").Select
    Selection.AutoFill Destination:=Range("H2:H1943")
    Range("H2:H1943").Select
    Columns("H:H").Select
    Selection.NumberFormat = "[h]:mm:ss"
    Columns("H:H").Select
    Selection.Font.Bold = False
    Selection.Font.Bold = True
    
' extra line


    ActiveWorkbook.Worksheets("Sheet6").Sort.SortFields.Add Key:=Range("D1"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        
    With Selection.Interior
        .PatternColor = 16777215
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.149998474074526
        .PatternTintAndShade = 0
    End With
    Columns("A:A").Select
    Selection.EntireColumn.Hidden = True
    Columns("B:B").Select
    Selection.EntireColumn.Hidden = True
    Columns("C:C").Select
    With Selection.Interior
        .PatternColor = 16777215
        .ThemeColor = xlThemeColorAccent6
        .TintAndShade = 0.399975585192419
        .PatternTintAndShade = 0
    End With
    Rows("1:1").Select
    Range("C1").Activate
    With Selection.Interior
        .PatternColor = 16777215
        .ThemeColor = xlThemeColorAccent1
        .TintAndShade = 0.399975585192419
        .PatternTintAndShade = 0
    End With
    Range("M9").Select
    ActiveWindow.SmallScroll Down:=-33
    Range("F11").Select
    Selection.Subtotal GroupBy:=4, Function:=xlSum, TotalList:=Array(8), _
        Replace:=True, PageBreaks:=True, SummaryBelowData:=True
    Range("J13").Select
    ActiveWindow.SmallScroll Down:=-39
End Sub [/End Code]