Applying a function to selected cells

[jay starz]

Hi,

I'm so glad I found this. This is a very useful service indeed.

Let me get into my question. Assume I have two columns A & B and I have any type of data in both. For example a person's last name in the first column and a person's first name in column B. Now in column C I want to combine the information so that it reads "Last Name, First name."

I know the formula for each cell would be =(Ax&", "&Bx) with x being the cell number. My question is this how do I apply the formula for each of the cells without having to copy and paste for each cell in column C and then putting in the value for 'x' depending on what cell I'm in. For example if I'm in cell C15 x would be 15 using the formula above.

How do I index 'x' and make it go through all the cells required by putting in the answers for each cell?

for(x=1; x<50; x++)
column_C_complete_name_x = column_A_last_name_x, column_B_first_name_x;

I want to do something like above for column C for each of the cells.

Does anyone know how I can do this in excel or if it's possible?

Thanks!

 

[bk]

I can't follow how you're trying to do this. Are you trying to use VBA?

If so, you could do a For-Next loop, similar to what you said and begin with your first row and end with your last one. Concatenation will put the formula in appropriatley for you. Something along these lines:

For myRow = firstRow to lastRow
  myCell = "C" & myRow
  ActiveSheet.Range(myCell).Formula = "=A" & myRow & "&B" & myRow" 
Next myRow

HTH

 

[Scott Huish]

or you could avoid the loop and do this:

Sub test()
Range("C1:C50").FormulaR1C1 = "=RC[-2] & " & Chr(34) & ", " & Chr(34) & "& RC[-1]"
End Sub