Jan 06 2022 12:23 AM
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!
Jan 06 2022 02:58 AM - edited Jan 06 2022 02:59 AM
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