Forum Discussion

Rory123908's avatar
Rory123908
Copper Contributor
Jan 06, 2022

Combining the date and time using VBA

Hi everyone. I have a problem. A VBA problem. I have a list of times every 30 seconds and I have a list of dates that correspond next to the times. I'm looking to create a function in VBA that allows me to combine the 2 categories so that they sit within one catagory as yyyy:mm:dd hh:mm(:ss). I have created the following code although it seems to fail on the final line..... Ill paste it here. 

Dim x%
Dim rangee As Range
Dim rangeee As Range
Dim rangeeee As Range
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")
    
     Set rangee = GetCellFromUser("Please select the row containing our variable headers", "Selecting headers (PWD DATA)", ActiveCell)

    Set rangeee = GetCellFromUser("Please select the cell on the First row of data that represents time", "Selecting time cells (PWD DATA)", ActiveCell)

        Set rangeeee = GetCellFromUser("Please select the cell on the First row of data that represents date", "Selecting date cells (PWD DATA)", ActiveCell)

    If rangee Is Nothing Then
        'user cancelled
    Else
        'range was selected, now do something, like select all cells to the bottom of that range:
     Set rangee = rangee.Resize(rangee.End(xlDown).Row - rangee.Row + 1)
    
     
     If rangeee Is Nothing Then
        'user cancelled
    Else
        'range was selected, now do something, like select all cells to the bottom of that range:
     Set rangeee = rangeee.Resize(rangeee.End(xlDown).Row - rangeee.Row + 1)
     
     
     If rangeeee Is Nothing Then
        'user cancelled
    Else
        'range was selected, now do something, like select all cells to the bottom of that range:
     Set rangeeee = rangeeee.Resize(rangeeee.End(xlDown).Row - rangeeee.Row + 1)
     
     
      
ws.Range("Rangeeee") = Application.WorksheetFunction.Text(ws.Range("rangeee"), "dd/mm/yyyy") & "" & Application.WorksheetFunction.Text(ws.Range("rangeee"), "hh:mm:ss")

The intention was to select the time, select the date and then merge them. It appears it wont work. I wonder if its because we have to do them one at a time (e.g row 1, then row 2, then row 3 etc), but I wasnt sure how to do this. If anyone has any idea and can help me that would be much appreciated. I essentially need to combine the date and time into 1 column for a long list. Thanks for all your help! 

excel
Formulas and Functions
Macros and VBA

1 Reply

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Jan 06, 2022

    Rory123908 

    Try this:

     

    Sub Combine()
    Dim rangee As Range
    Dim rangeee As Range
    Dim rangeeee As Range
    Dim m As Long

    Set rangeee = GetCellFromUser("Please select the cell on the First row of data that represents time", "Selecting time cells (PWD DATA)", ActiveCell)
    If rangeee Is Nothing Then
        'user cancelled
        Beep
        Exit Sub
    Else
        'range was selected, now do something, like select all cells to the bottom of that range:
        m = rangeee.End(xlDown).Row - rangeee.Row + 1
    End If

    Set rangeeee = GetCellFromUser("Please select the cell on the First row of data that represents date", "Selecting date cells (PWD DATA)", ActiveCell)
    If rangeeee Is Nothing Then
        'user cancelled
        Beep
        Exit Sub
    End If

    Set rangee = GetCellFromUser("Please select the row containing our variable headers", "Selecting headers (PWD DATA)", ActiveCell)
    If rangee Is Nothing Then
        'user cancelled
        Beep
        Exit Sub
    Else
        'range was selected, now do something, like select all cells to the bottom of the first range:
        Set rangee = rangee.Resize(m)
    End If

    With rangee
        .Formula = "=" & rangeeee.Address(False, False) & "+" & rangeee.Address(False, False)
        .NumberFormat = "dd/mm/yyyy hh:mm:ss"
    End With
End Sub

     

Resources