Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Help with Concatenating
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
mju mju is offline
external usenet poster
  
Posts: 29
Default Help with Concatenating
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
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,049
Default Help with Concatenating
use the ampersand, & eg "ABC" & "DEF" results in "ABCDEF"

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




"mju" wrote in message
...
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

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
mju mju is offline
external usenet poster
  
Posts: 29
Default Help with Concatenating
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.


"Patrick Molloy" wrote:

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

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




"mju" wrote in message
...
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

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 12
Default Help with Concatenating
On Jun 2, 4:00*pm, mju wrote:
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....


Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 12
Default Help with Concatenating
On Jun 2, 2:52*pm, mju wrote:
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.
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
concatenating TED Excel Worksheet Functions 3 February 13th 09 01:03 AM
concatenating Yosh Excel Discussion (Misc queries) 7 February 7th 07 09:03 PM
Concatenating % confused Excel Discussion (Misc queries) 2 September 21st 06 03:49 AM
Concatenating a zero T De Villiers[_39_] Excel Programming 6 July 21st 06 03:44 PM
Concatenating Himu Excel Programming 7 July 23rd 05 08:58 PM


All times are GMT +1. The time now is 01:02 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement