Mixing text and times

[paulsolar]

Hi All

I'm have a problem combining two columns into one. The code below does what I want

Sheets("Calculations").Range("K2") = Sheets("Calculations").Range("J2").Value & Sheets("Calculations").Range("I2").Text

J2 contains a value and I2 contains the time and works as in it displays:- Commences: 07:31 which is what i want to achieve.

How do i convert the below to make it put the correct values in all the rows ie: K3=J3 & J4.........

Sheets("Calculations").Range("K2:K" & LastRow2) = Sheets("Calculations").Range("J2").Value & Sheets("Calculations").Range("I2").Text (obviously this puts the J2 and I2 values in every row)

I have tried doing it with formula, the problem is that it changes back to time format as a number and I have tried everything i know to sort this out without any luck.

Any help would be appreciated

cheers

Paul

 

[FormR]

I have tried doing it with formula, the problem is that it changes back to time format as a number and I have tried everything i know to sort this out without any luck.

Hi, if you are still interested in pursuing a formula solution - you could try like this:

Excel 2013/2016
	I	J	K
2	07:31	Commences:	Commences:07:31
Sheet1

Cell Formulas
Range	Formula
K2	=J2&TEXT(I2,"hh:mm")

 

[Fluff]

How about

Sub paulsolar()
   With Range("K2:K10")
      .Formula = "=j2&text(i2,""hh:mm"")"
      .Value = .Value
   End With
End Sub

 

[paulsolar]

thats brilliant, i tried every variation of your formula(including text), but not at the same time

thanks for your help, i cant believe how simple it should have been for me

 

[Fluff]

As you've decided to ignore me (as we posted at the same time) all I can say is

Come on Fiji

 

[paulsolar]

OMG Fluff, I missed your post it must have crossed with my reply.

i get down on my knees and beg your forgiveness , please can you withdraw that dreadful statement and say COME ON YOU WELSH and I'll same come on England (admittedly very quietly) when we meet you in the finals ray:

best of luck

cheers

Paul

 

[Fluff]

I think that's pushing it a bit, but maybe
Come on you Reds