Solved: VBA Code to Concatenate

[Slicemahn]

Hi Everyone!

I am currently using MS Excel Version 2003 and having difficulty in writing code that would concatenate the business segment with date and time interval. In my attached spreadsheet the desired result is to have the business segment (cell B1) to concatenate with the time intervals in (b2:b49) and date for the result using today's date for 1230 AM: 0701180000300Gen_Subscriber in col A. My reason for doing this is to create data in which I can write to an Access data table.
I thank all in advance for their assistance with this challenge.

[Jacob Hilderbrand]

Try this:


MyValue = Range("B1").Value & Format(Range("B2").Value, "mmddyyhmmss")


Change the format to whatever you want.

Or if you want a formula:


=B1&TEXT(B2,"h:mm:ss")

[Slicemahn]

Try this:


MyValue = Range("B1").Value & Format(Range("B2").Value, "mmddyyhmmss")


Change the format to whatever you want.

Or if you want a formula:


=B1&TEXT(B2,"h:mm:ss")


Dr. J thanks for your quick reply. I tried your idea but with my code I am getting an invalid Next statement Compile error. Here's my code:

Sub Storming()
Dim i, j As Integer
Dim EndRow As Integer
Dim MyValue As String
Dim WorkDate As String
With ActiveSheet
EndRow = Cells(65536, 1).End(xlUp).Row
WorkDate = Now
For i = 1 To EndRow
For j = 1 To EndRow
MyValue = Format(WorkDate, "yymmdd") & Format(Range("B" & i).Value, "hhmm") _
& Range("B" & j).Value
Range("A" & (i + 1)).Value = MyValue
j = j + 49
Next i
Next j
End With
End Sub

[JimmyTheHand]

You have switched the cycle variables. Outer loop is i, inner loop is j, so j should be finished first, not i.

For i = 1 To EndRow
For j = 1 To EndRow
'....
Next i
Next j

[Charlize]

What about this one : row 1 and 2 belong together. So the concatenated name is by two rows (1/2,3/4,5/6 ...)
Sub Storming()
Dim i, j As Integer
Dim EndRow As Integer
Dim MyValue As String
Dim WorkDate As String
'check column B. row 1 and 2 are the ones to join
EndRow = Cells(65536, 2).End(xlUp).Row
WorkDate = Now
'loop until end of rows in B
For i = 1 To EndRow
'First part of the string to make
MyValue = Format(WorkDate, "yymmdd") & Format(Range("B" & i + 1).Value, "hhmm")
MyValue = MyValue & Range("B" & i).Value
'row = row + 1
i = i + 1
For j = 2 To 49
'loop through columns 2 to 49
MyValue = MyValue & Format((Cells(i, j).Value), "hhmm")
Next j
'write the value that was concatenated.
Range("A" & (i - 1)).Value = MyValue
'first time to go to next item it will be row 3 then 5 etc...
Next i
End Sub
Charlize

[Slicemahn]

Gentlemen, Thanks a lot for your help. Your ideas have helped me discover where exactly I went wrong. This is the solution that I have worked out:


Sub Performing()
Dim i, j As Integer
Dim EndRow As Integer
Dim MyValue As String
Dim WorkDate As String
With ActiveSheet
EndRow = Cells(65536, 2).End(xlUp).Row
WorkDate = Now
j = 1
For i = 1 To EndRow
Cells(i, 1) = Format(WorkDate, "yymmdd") & Format(Range("B" & i).Value, "hhmm") _
& Range("B" & j).Value
If Int(i / 49) = i / 49 Then
j = j + 49
End If
Next i
End With
For a = 1 To EndRow Step 49
Cells(a, 1) = ""
Next a
End Sub

[mdmackillop]

Hi Slicemahn,
A couple of comments
Always use Option Explicit (a is not dimmed)
You need to Dim each item specifically. Listing doesn't work.
Dim i as Integer, j as Integer.
Use Long instead of Integer as it gets converted to Long anyway.
When you use With ActiveSheet or similar, you need to use .Cells or .Range to connect the items. As ActiveSheet is the default, you won't get errors here, but you may in other situations.

Option Explicit

Sub Performing()
Dim i As Long, j As Long, a As Long
Dim EndRow As Long
Dim MyValue As String
Dim WorkDate As String
With ActiveSheet
EndRow = .Cells(65536, 2).End(xlUp).Row
WorkDate = Now
j = 1
For i = 1 To EndRow
.Cells(i, 1) = Format(WorkDate, "yymmdd") & Format(.Range("B" & i).Value, "hhmm") & _
.Range("B" & j).Value
If Int(i / 49) = i / 49 Then
j = j + 49
End If
Next i
For a = 1 To EndRow Step 49
.Cells(a, 1) = ""
Next a
End With
End Sub

[Slicemahn]

Hi MD,

Yes this code solution is much more neat and tidy. I appreciate your comments and expertise.

Cheers,
Slice