Fill in blank cells with adjacent cell value

MichalK86

New Member
Joined
Jan 26, 2019
Messages
6
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
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,049
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Maybe (if the cells are truly blank)...
Code:
Sub FillG()
    Columns("G:G").SpecialCells(4).FormulaR1C1 = "=RC[-1]"
End Sub
or
Code:
Sub FillG2()
    With Columns("G:G").SpecialCells(4)
        .FormulaR1C1 = "=RC[-1]"
        .Value = .Value
        .NumberFormat = "DD/MM/YYYY HH:MM"
    End With
End Sub
 

MichalK86

New Member
Joined
Jan 26, 2019
Messages
6
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

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,049
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
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

New Member
Joined
Jan 26, 2019
Messages
6

ADVERTISEMENT

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

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,049
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
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 :confused:
 

MichalK86

New Member
Joined
Jan 26, 2019
Messages
6

ADVERTISEMENT

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

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,049
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
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 :biggrin:
 

MichalK86

New Member
Joined
Jan 26, 2019
Messages
6
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

New Member
Joined
Jan 26, 2019
Messages
6
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 :biggrin:


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

Code:
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]
 

Watch MrExcel Video

Forum statistics

Threads
1,129,569
Messages
5,637,099
Members
416,957
Latest member
Brovashift

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
Top