I am trying to concatenate A1:M1 into N1 using this macro

Dim Variable1 As Long
Dim Variable2 As Range
Set sht = Sheets("Data")
Variable1 = sht.Cells(Cells.Rows.Count, "A").End(xlUp).Row
Set MyRange = sht.Range("A1:A" & Variable1)
For Each Variable2 In MyRange
Variable2.Offset(, 13) = Variable2 & Variable2.Offset(, 1) &
Variable2.Offset(, 2)
Next

I am having two problems
1. Only A1:B1 are concatenating into N1
2. A1 and F1 have percentages inside them. When A1 concatenates (F1 will
have same problem) any numbers after the decimal point are showing up in the
concatenated cell. I only want the numbers before the decimal point to
display for A1 and F1.

I don’t know how to fix either problem. If you can you offer any
instruction thank you.

Here is one way to concatenate Columns A thru M into Column N...

Dim Variable1 As Long
Dim Variable2 As Range
Set sht = Sheets("Data")
Variable1 = sht.Cells(Cells.Rows.Count, "A").End(xlUp).Row
Set MyRange = sht.Range("A1:A" & Variable1)
For Each Variable2 In MyRange
Variable2.Offset(, 13) = Join(WorksheetFunction.Transpose( _
WorksheetFunction.Transpose(Range("A1:M1"))), "")
Next

--
Rick (MVP - Excel)



"Jazz" <(E-Mail Removed)> wrote in message
news:61B4F5C6-FCA3-4B97-B964-(E-Mail Removed)...
> I am trying to concatenate A1:M1 into N1 using this macro
>
> Dim Variable1 As Long
> Dim Variable2 As Range
> Set sht = Sheets("Data")
> Variable1 = sht.Cells(Cells.Rows.Count, "A").End(xlUp).Row
> Set MyRange = sht.Range("A1:A" & Variable1)
> For Each Variable2 In MyRange
> Variable2.Offset(, 13) = Variable2 & Variable2.Offset(, 1) &
> Variable2.Offset(, 2)
> Next
>
> I am having two problems
> 1. Only A1:B1 are concatenating into N1
> 2. A1 and F1 have percentages inside them. When A1 concatenates (F1 will
> have same problem) any numbers after the decimal point are showing up in
> the
> concatenated cell. I only want the numbers before the decimal point to
> display for A1 and F1.
>
> I don’t know how to fix either problem. If you can you offer any
> instruction thank you.
>

Sorry, I grabbed the wrong code from my test sheet. Try this instead...

Dim Variable1 As Long, Sht As Worksheet
Dim Variable2 As Range, MyRange As Range
Set Sht = Sheets("sheet1")
Variable1 = Sht.Cells(Cells.Rows.Count, "A").End(xlUp).Row
Set MyRange = Sht.Range("A1:A" & Variable1)
For Each Variable2 In MyRange
Variable2.Offset(, 13) = Join(WorksheetFunction.Transpose( _
WorksheetFunction.Transpose(Range("A1:M1"). _
Offset(Variable2.Row - 1))), "")
Next

Note that I added some variable declarations that were missing.

--
Rick (MVP - Excel)



"Rick Rothstein" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Here is one way to concatenate Columns A thru M into Column N...
>
> Dim Variable1 As Long
> Dim Variable2 As Range
> Set sht = Sheets("Data")
> Variable1 = sht.Cells(Cells.Rows.Count, "A").End(xlUp).Row
> Set MyRange = sht.Range("A1:A" & Variable1)
> For Each Variable2 In MyRange
> Variable2.Offset(, 13) = Join(WorksheetFunction.Transpose( _
> WorksheetFunction.Transpose(Range("A1:M1"))), "")
> Next
>
> --
> Rick (MVP - Excel)
>
>
>
> "Jazz" <(E-Mail Removed)> wrote in message
> news:61B4F5C6-FCA3-4B97-B964-(E-Mail Removed)...
>> I am trying to concatenate A1:M1 into N1 using this macro
>>
>> Dim Variable1 As Long
>> Dim Variable2 As Range
>> Set sht = Sheets("Data")
>> Variable1 = sht.Cells(Cells.Rows.Count, "A").End(xlUp).Row
>> Set MyRange = sht.Range("A1:A" & Variable1)
>> For Each Variable2 In MyRange
>> Variable2.Offset(, 13) = Variable2 & Variable2.Offset(, 1) &
>> Variable2.Offset(, 2)
>> Next
>>
>> I am having two problems
>> 1. Only A1:B1 are concatenating into N1
>> 2. A1 and F1 have percentages inside them. When A1 concatenates (F1
>> will
>> have same problem) any numbers after the decimal point are showing up in
>> the
>> concatenated cell. I only want the numbers before the decimal point to
>> display for A1 and F1.
>>
>> I don’t know how to fix either problem. If you can you offer any
>> instruction thank you.
>>

Try the below to concatenate the cells as displayed....

Sub MyMacro()
Dim lngRow As Long, lngCol As Long
Dim lngLastRow As Long, sht As Worksheet

Set sht = Sheets("Data")
lngLastRow = sht.Cells(Cells.Rows.Count, "A").End(xlUp).Row

For lngRow = 1 To lngLastRow
For lngCol = 1 To 13
strData = strData & Cells(lngRow, lngCol).Text
Next
Range("N" & lngRow) = strData: strData = vbNullString
Next

End Sub

--
Jacob (MVP - Excel)


"Jazz" wrote:

> I am trying to concatenate A1:M1 into N1 using this macro
>
> Dim Variable1 As Long
> Dim Variable2 As Range
> Set sht = Sheets("Data")
> Variable1 = sht.Cells(Cells.Rows.Count, "A").End(xlUp).Row
> Set MyRange = sht.Range("A1:A" & Variable1)
> For Each Variable2 In MyRange
> Variable2.Offset(, 13) = Variable2 & Variable2.Offset(, 1) &
> Variable2.Offset(, 2)
> Next
>
> I am having two problems
> 1. Only A1:B1 are concatenating into N1
> 2. A1 and F1 have percentages inside them. When A1 concatenates (F1 will
> have same problem) any numbers after the decimal point are showing up in the
> concatenated cell. I only want the numbers before the decimal point to
> display for A1 and F1.
>
> I don’t know how to fix either problem. If you can you offer any
> instruction thank you.
>

Thank you Rick. Your input is very helpful. This program works very well.

"Rick Rothstein" wrote:

> Sorry, I grabbed the wrong code from my test sheet. Try this instead...
>
> Dim Variable1 As Long, Sht As Worksheet
> Dim Variable2 As Range, MyRange As Range
> Set Sht = Sheets("sheet1")
> Variable1 = Sht.Cells(Cells.Rows.Count, "A").End(xlUp).Row
> Set MyRange = Sht.Range("A1:A" & Variable1)
> For Each Variable2 In MyRange
> Variable2.Offset(, 13) = Join(WorksheetFunction.Transpose( _
> WorksheetFunction.Transpose(Range("A1:M1"). _
> Offset(Variable2.Row - 1))), "")
> Next
>
> Note that I added some variable declarations that were missing.
>
> --
> Rick (MVP - Excel)
>
>
>
> "Rick Rothstein" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > Here is one way to concatenate Columns A thru M into Column N...
> >
> > Dim Variable1 As Long
> > Dim Variable2 As Range
> > Set sht = Sheets("Data")
> > Variable1 = sht.Cells(Cells.Rows.Count, "A").End(xlUp).Row
> > Set MyRange = sht.Range("A1:A" & Variable1)
> > For Each Variable2 In MyRange
> > Variable2.Offset(, 13) = Join(WorksheetFunction.Transpose( _
> > WorksheetFunction.Transpose(Range("A1:M1"))), "")
> > Next
> >
> > --
> > Rick (MVP - Excel)
> >
> >
> >
> > "Jazz" <(E-Mail Removed)> wrote in message
> > news:61B4F5C6-FCA3-4B97-B964-(E-Mail Removed)...
> >> I am trying to concatenate A1:M1 into N1 using this macro
> >>
> >> Dim Variable1 As Long
> >> Dim Variable2 As Range
> >> Set sht = Sheets("Data")
> >> Variable1 = sht.Cells(Cells.Rows.Count, "A").End(xlUp).Row
> >> Set MyRange = sht.Range("A1:A" & Variable1)
> >> For Each Variable2 In MyRange
> >> Variable2.Offset(, 13) = Variable2 & Variable2.Offset(, 1) &
> >> Variable2.Offset(, 2)
> >> Next
> >>
> >> I am having two problems
> >> 1. Only A1:B1 are concatenating into N1
> >> 2. A1 and F1 have percentages inside them. When A1 concatenates (F1
> >> will
> >> have same problem) any numbers after the decimal point are showing up in
> >> the
> >> concatenated cell. I only want the numbers before the decimal point to
> >> display for A1 and F1.
> >>
> >> I don’t know how to fix either problem. If you can you offer any
> >> instruction thank you.
> >>
> .
>

Jacob your procedure is perfect. Thank you for your help. I am very
appreciative.

"Jacob Skaria" wrote:

> Try the below to concatenate the cells as displayed....
>
> Sub MyMacro()
> Dim lngRow As Long, lngCol As Long
> Dim lngLastRow As Long, sht As Worksheet
>
> Set sht = Sheets("Data")
> lngLastRow = sht.Cells(Cells.Rows.Count, "A").End(xlUp).Row
>
> For lngRow = 1 To lngLastRow
> For lngCol = 1 To 13
> strData = strData & Cells(lngRow, lngCol).Text
> Next
> Range("N" & lngRow) = strData: strData = vbNullString
> Next
>
> End Sub
>
> --
> Jacob (MVP - Excel)
>
>
> "Jazz" wrote:
>
> > I am trying to concatenate A1:M1 into N1 using this macro
> >
> > Dim Variable1 As Long
> > Dim Variable2 As Range
> > Set sht = Sheets("Data")
> > Variable1 = sht.Cells(Cells.Rows.Count, "A").End(xlUp).Row
> > Set MyRange = sht.Range("A1:A" & Variable1)
> > For Each Variable2 In MyRange
> > Variable2.Offset(, 13) = Variable2 & Variable2.Offset(, 1) &
> > Variable2.Offset(, 2)
> > Next
> >
> > I am having two problems
> > 1. Only A1:B1 are concatenating into N1
> > 2. A1 and F1 have percentages inside them. When A1 concatenates (F1 will
> > have same problem) any numbers after the decimal point are showing up in the
> > concatenated cell. I only want the numbers before the decimal point to
> > display for A1 and F1.
> >
> > I don’t know how to fix either problem. If you can you offer any
> > instruction thank you.
> >