How to write code to check if a column contains a value, then put a specified value in another cell

Question

Ok so I have a timesheet spreadsheet that I use and I've added some code to it (a while back now) to summarise my arrival and leaving times for the week. I'm now trying to update it so that it looks for a certain value within a column ("9df"), and if it contains it the put "9df" instead of the time.

This is what I currently have, I tried the commented bit on the end but it doesn't work. Thinking I'm going to have to do an If Else? Just can't get my head around it yet so any help would be appreciated:

    Sub LeaveArriveTime()
    Sheets("Time Log").Select
    Range("B58").Select
    Selection.End(xlUp).Select
    ActiveCell.Offset(1, -1).Activate
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("StartEnd Times").Select
    Range("B3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Selection.NumberFormat = "h:mm"
        Sheets("Time Log").Select
        Range("D58").Select
        Selection.End(xlUp).Select
        ActiveCell.Offset(1, -3).Activate
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("StartEnd Times").Select
        Range("C3").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Application.CutCopyMode = False
        Selection.NumberFormat = "h:mm"
            Sheets("Time Log").Select
            Range("F58").Select
            Selection.End(xlUp).Select
            ActiveCell.Offset(1, -5).Activate
            Application.CutCopyMode = False
            Selection.Copy
            Sheets("StartEnd Times").Select
            Range("D3").Select
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                :=False, Transpose:=False
            Application.CutCopyMode = False
            Selection.NumberFormat = "h:mm"
                Sheets("Time Log").Select
                Range("H58").Select
                Selection.End(xlUp).Select
                ActiveCell.Offset(1, -7).Activate
                Application.CutCopyMode = False
                Selection.Copy
                Sheets("StartEnd Times").Select
                Range("E3").Select
                Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                    :=False, Transpose:=False
                Application.CutCopyMode = False
                Selection.NumberFormat = "h:mm"
                    Sheets("Time Log").Select
                    Range("J58").Select
                    Selection.End(xlUp).Select
                    ActiveCell.Offset(1, -9).Activate
                    Application.CutCopyMode = False
                    Selection.Copy
                    Sheets("StartEnd Times").Select
                    Range("F3").Select
                    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                        :=False, Transpose:=False
                    Application.CutCopyMode = False
                    Selection.NumberFormat = "h:mm"
    Sheets("Time Log").Select
    Range("B1").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(0, -1).Activate
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("StartEnd Times").Select
    Range("B2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Selection.NumberFormat = "h:mm"
        Sheets("Time Log").Select
        Range("D1").Select
        Selection.End(xlDown).Select
        ActiveCell.Offset(0, -3).Activate
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("StartEnd Times").Select
        Range("C2").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Application.CutCopyMode = False
        Selection.NumberFormat = "h:mm"
            Sheets("Time Log").Select
            Range("F1").Select
            Selection.End(xlDown).Select
            ActiveCell.Offset(0, -5).Activate
            Application.CutCopyMode = False
            Selection.Copy
            Sheets("StartEnd Times").Select
            Range("D2").Select
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                :=False, Transpose:=False
            Application.CutCopyMode = False
            Selection.NumberFormat = "h:mm"
                Sheets("Time Log").Select
                Range("H1").Select
                Selection.End(xlDown).Select
                ActiveCell.Offset(0, -7).Activate
                Application.CutCopyMode = False
                Selection.Copy
                Sheets("StartEnd Times").Select
                Range("E2").Select
                Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                    :=False, Transpose:=False
                Application.CutCopyMode = False
                Selection.NumberFormat = "h:mm"
                    Sheets("Time Log").Select
                    Range("J1").Select
                    Selection.End(xlDown).Select
                    ActiveCell.Offset(0, -9).Activate
                    Application.CutCopyMode = False
                    Selection.Copy
                    Sheets("StartEnd Times").Select
                    Range("F2").Select
                    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                        :=False, Transpose:=False
                    Application.CutCopyMode = False
                    Selection.NumberFormat = "h:mm"
'    If Sheets("Time Log").Range("B2:B58").Value = "9df" Then
'    Sheets("StartEnd Times").Range("B2:B3").Value = "9df"
'    End If
End Sub

Thanks in advance!

You can't compare a multi-cell range to text. Use `Application.Match` or `Find` or `Application.CountIf` to test if that range contains the text "9df". — BigBen, Nov 14 '19 at 14:51
Also, see [How to avoid using Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). — BigBen, Nov 14 '19 at 14:53

score 0 · Accepted Answer · answered Nov 20 '19 at 10:42

Ok I solved it by changing the way I use the sheet. By always starting the 9df at a time I never start work at, I can now search a cell for that time and amend it to 9df if it's found. Using this:

Sub Monday9df()
    Sheets("Time Log").Range("B3").Select
    ActiveCell.FormulaR1C1 = "9df"
    Selection.AutoFill Destination:=Range("B3:B18"), Type:=xlFillCopy
End Sub

How to write code to check if a column contains a value, then put a specified value in another cell

1 Answers1