How can I automatic insert tekst from excelsheet using macro

[Guest]

I'm using excel for my planning and using macro's to make it more easy to
plan. I can already automaticly insert tekst I put into the macro, but I also
want to automatic insert starting times and ending times in those macro's.
These times are in a timeline in the top of my sheet (Row 4) and the
selection of those times vary, because nobody works the same hours every day.
These times should come in the tekst as mentioned in the last line of the
macro.
So it should state: "Starting time - Ending time herman" so "07:00 - 14:15
herman"
The macro I'm using is copied under this tekst.

I hope somebody can help me!!

Thanks in advance

Paul.

Sub herman()
'
' herman Macro
' Macro recorded 7/17/02 by c8226929
'
' Keyboard Shortcut: Ctrl+m
'
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlTop
.WrapText = False
.Orientation = 0
.IndentLevel = 0
.ShrinkToFit = False
.MergeCells = True
End With
With Selection.Interior
.ColorIndex = 53
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
ActiveCell.FormulaR1C1 = " - herman"
End Sub

 

[Ardus Petus]

Assuming your starting/ending hours are in A4 and B4:


Sub herman()
ActiveCell.Value = _
Format(Range("A4"), "hh:mm") & _
" - " & _
Format(Range("B4"), "hh:mm") & _
" herman"

End Sub

HTH

 

[Guest]

Thanks for your quick response,

My timeline is located between E4 and CA4, E4 starts with 06:00 and CA4 ends
with 00:30 divided for each cell betweed those 2 with 15 min. What I would
like to accomplish is when I for herman, who is located in row 5, select J5
(07:15) through S5 (09:30) that these times are automaticly insert in those
selected cells, ofcourse including the other tekst as well. But when I make a
different selection, say M5 (08:00) through V5 (10:15), that the times
automaticly are adjusted to that selection. Can you help me with that?

Thanks

Paul Tikken.