Combine 1 cell with multiple cells

[Phlip183]

I wanted to know how to write a script so it would Concatenate a value in 1 cell that is fixed, and multiple submissions in another column such as:

A1: Sample

B1: 1
B2: 2
B3: 3

C1: Sample 1
C2: Sample 2
C3: Sample 3

Thanks.

 

[atawil]

Try this formula

=A1&" "&B1

 

[Scott Huish]

=$A$1&" "&B1

Copy down.

 

[Rick Rothstein]

Put this in C1 and copy down....

=A$1&" "&B1

Note the absolute row reference (the $ sign) for the A1 cell reference.

 

[Phlip183]

Thanks alot guys, it worked perfectly.
Now in my actual script i have the Column A in one sheet and Column B and C in another sheet. That part isnt a big deal because i could just tweak it. Heres what i ahve currently:
Sub Macro2()
'
' Macro2 Macro
'
'
Range("B1").Select
ActiveCell.FormulaR1C1 = "='Info for Script'!R1C1&""""&'New Sumission'!RC[-1]"
Range("B1").Select
Selection.AutoFill Destination:=Range("B1:B3"), Type:=xlFillDefault
Range("B1:B3").Select
End Sub

So in the second sheet its writing the formula in Column B, selecting the word in the worksheet call "Info for Script" combining it with the entries in column A in the sheet called "New Submission" and then dragging it down to do all the cells that is in column A, in this case its only 3 entries, but it be any number of entries, and i dont want it to drag it down, i want it to copy and paste it every time in B1 but just changing the second part of the formula to move down one until it reaches a blank cell.

I hope this is clear, Im sorry for the story. Help would be greatly appreciated, ive been at this for 2 weeks. Thanks again

 

[Scott Huish]

First off, it is very rare that you need to Select anything in VBA. You can work with ranges directly.

Can you provide a sample from both sheets and expected results.

<span style="font-family:Arial; font-size:9pt; font-weight:bold;background-color:#ffffff; color:#000000; ">Excel tables to the web >> </span><a style ="font-family:Arial; font-size:9pt; color:#fcf507; background-color:#800040; font-weight:bold;" href="http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4 </a>

 

[Phlip183]

blank* have to repost sorry

 

[Phlip183]

Thanks for the response, but there are 3 tabs:

DataSheet
Info for Script sheet
New Submission Sheet

Data Sheet is filled with data
Info for script has the word "Sample" in A1
New Submission sheet will have any number of values in column A. So it can have #'s in the first 5 rows in column A or 500 rows in column A.

I have it combining Sample from Info for Script Sheet and combining it with the number in column A but having the formula written in Column B, row 1. Such as this:

Column A
1
2
3
4...
X

Column B
='Info for Script'!R1C1&""""&'New Sumission'!RC[-1]"

This gives the result: "Sample 1"

Which is good... But i need it to cycle through all the numbers Column A, that is in New Submission Sheet so it can copy and paste each entry that is resulted in Column B into a selected spot in the Data sheet.

I wish i was able to just do screen shots, it would make everything alot easier.

 

[Rick Rothstein]

I'm not sure how the Data sheet fits into your overall need (if you can describe it in more detail, we may be able to write directly to it instead of filling in Column B on the New Submission sheet); however, I think this macro will fill Column B on the New Submission sheet the way you want...

Sub CombineAndCopyDown()
  Dim Info As Worksheet, Submit As Worksheet, LastRow As Long
  Set Info = Worksheets("Info for Script")
  Set Submit = Worksheets("New Submission")
  LastRow = Submit.Cells(Rows.Count, "A").End(xlUp).Row
  Submit.Range("B1").Value = "=""" & Info.Range("A1").Value & " ""&" & Mid(Submit.Range("B1").Formula, 2)
  Submit.Range("B1:B" & LastRow).Value = Submit.Range("B1").Formula
End Sub

 

[Scott Huish]

I wish i was able to just do screen shots, it would make everything alot easier.

I posted the link to what will allow you to do that. Excel Jeanie HTML 4