I want to copy a range of cells (custom format) filled with time data (e.g. 8:00, 7:30, 5:45, ...) as text to write to another program through Application.SendKeys. When I grab the cells as they are, they're written out as e. g. 1.041666666 instead of 7:30. How do I copy or convert them to text? Trying to get the value/text from the whole range as I copy won't work, neither did attempts at looping through afterwards to change the values separately. There might be workarounds using clipboard, but I want to leave it untouched. Code right now:
DayArray = Application.ActiveSheet.Range("A1:E4")
For j = 1 To UBound(DayArray)
For k = 1 To 5 'fixed column count
DayArray(j, k)= DayArray(j, k).Text
Next k
Next j
(... SendKeys example)
Application.SendKeys DayArray(1, 1), True
CodePudding user response:
Use the Format()
Function. It returns a string in the format desired:
DayArray = Application.ActiveSheet.Range("A1:E4")
For j = 1 To UBound(DayArray)
For k = 1 To 5 'fixed column count
DayArray(j, k)= Format(DayArray(j, k),"h:mm")
Next k
Next j