Help with Concatenating

[mju]

Please I need help! How do I make concatenated values a variable that I can
use any where in a current module? Is this the right way to use it as a
variable? Thanks alot guys

This is what I have so far…
sub concat()
L_Row = ActiveCell.SpecialCells(xlLastCell).Row
Set ST_loc_style_Color_Size = Range("ak2")
Set ST_loc_style_Color_Size_Pack = Range("ak2")
ST_loc_style_Color_Size.FormulaR1C1 =
"=CONCATENATE(RC[-23],""-"",RC[-10],""-"",RC[-9],""-"", RC[-8])"
ST_loc_style_Color_Size_Pack.FormulaR1C1 =
"=CONCATENATE(RC[-30],""-"",RC[-10],""-"",RC[-9],""-"", RC[-8],""-"",RC[-7])"
Range("ak2").FormulaR1C1 =
"=CONCATENATE(RC[-23],""-"",RC[-10],""-"",RC[-9],""-"", RC[-8])"

'ST_loc_style_Color_Size_Pack.FormulaR1C1 =
"=CONCATENATE(RC[-30],""-"",RC[-10],""-"",RC[-9],""-"", RC[-8],""-"",RC[-7])"

pack = MsgBox("Did you want include the pacl?", vbYesNo)

If pack = vbYes Then
ST_loc_style_Color_Size_Pack.autofill Destination:=Range("ak2:ak" & L_Row)
Range("AK2:AK" & L_Row).Copy
Range("AK1").pastespecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("AK2:AK" & L_Row).sort Key1:=Range("AK1"), Order1:=xlAscending,
header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("AL1").Select
Columns("AK:AK").AdvancedFilter action:=xlFilterCopy,
CopyToRange:=Range( _
"AL1"), Unique:=True
Total_Count = Application.WorksheetFunction.CountA(Range("AL:AL"))
Else
ST_loc_style_Color_Size.autofill Destination:=Range("ak2:ak" & L_Row)
Range("AK2:AK" & L_Row).Copy
Range("AK1").pastespecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("AK2:AK" & L_Row).sort Key1:=Range("AK1"), Order1:=xlAscending,
header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("AL1").Select
Columns("AK:AK").AdvancedFilter action:=xlFilterCopy,
CopyToRange:=Range( _
"AL1"), Unique:=True
Total_Count = Application.WorksheetFunction.CountA(Range("AL:AL"))

End If
end sub

 

[Patrick Molloy]

use the ampersand, & eg "ABC" & "DEF" results in "ABCDEF"

..FormulaR1C1 = "=RC[-30] & RC[-9] & RC[-8]"

 

[andy the pugh]

Is this the right way to use it as a
variable? Thanks alot guys

It doesn't look right, I can't even figure out what you are trying to
do.

Why are you using CONCATENATE rather than "&" ? You use & later on in
the code.
It is very rarely necessary to use copy and paste, you can either
assign to a (variant) variable, or transfer directly;

RandomVariable = Sheet1.Range("A1:A3").Value
Sheet1.Range("A1:A3").Value = Sheet1.Range("B2:B3").Value

Both work.

 

[mju]

Thanks Patrick.

The problem that I have is each cells have different values but it keep
copying same value to the end of the cell.

use the ampersand, & eg "ABC" & "DEF" results in "ABCDEF"

.FormulaR1C1 = "=RC[-30] & RC[-9] & RC[-8]"

Please I need help! How do I make concatenated values a variable that I
can
use any where in a current module? Is this the right way to use it as a
variable? Thanks alot guys

This is what I have so far…
sub concat()
L_Row = ActiveCell.SpecialCells(xlLastCell).Row
Set ST_loc_style_Color_Size = Range("ak2")
Set ST_loc_style_Color_Size_Pack = Range("ak2")
ST_loc_style_Color_Size.FormulaR1C1 =
"=CONCATENATE(RC[-23],""-"",RC[-10],""-"",RC[-9],""-"", RC[-8])"
ST_loc_style_Color_Size_Pack.FormulaR1C1 =
"=CONCATENATE(RC[-30],""-"",RC[-10],""-"",RC[-9],""-"",
RC[-8],""-"",RC[-7])"
Range("ak2").FormulaR1C1 =
"=CONCATENATE(RC[-23],""-"",RC[-10],""-"",RC[-9],""-"", RC[-8])"

'ST_loc_style_Color_Size_Pack.FormulaR1C1 =
"=CONCATENATE(RC[-30],""-"",RC[-10],""-"",RC[-9],""-"",
RC[-8],""-"",RC[-7])"

pack = MsgBox("Did you want include the pacl?", vbYesNo)

If pack = vbYes Then
ST_loc_style_Color_Size_Pack.autofill Destination:=Range("ak2:ak" &
L_Row)
Range("AK2:AK" & L_Row).Copy
Range("AK1").pastespecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("AK2:AK" & L_Row).sort Key1:=Range("AK1"), Order1:=xlAscending,
header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("AL1").Select
Columns("AK:AK").AdvancedFilter action:=xlFilterCopy,
CopyToRange:=Range( _
"AL1"), Unique:=True
Total_Count = Application.WorksheetFunction.CountA(Range("AL:AL"))
Else
ST_loc_style_Color_Size.autofill Destination:=Range("ak2:ak" & L_Row)
Range("AK2:AK" & L_Row).Copy
Range("AK1").pastespecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("AK2:AK" & L_Row).sort Key1:=Range("AK1"), Order1:=xlAscending,
header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("AL1").Select
Columns("AK:AK").AdvancedFilter action:=xlFilterCopy,
CopyToRange:=Range( _
"AL1"), Unique:=True
Total_Count = Application.WorksheetFunction.CountA(Range("AL:AL"))

End If
end sub

 

[andy the pugh]

Thanks Patrick.

The problem that I have is each cells have different values but it keep
copying same value to the end of the cell.

Dim q as String
q = Sheet1.Range("A3").Value & Sheet1.Cells(X+Y*Z, A*B+C).Value &
"Random Text" & Sin (Pi())

Works fine....