Macro To Combine 2 Cells In Value Format

gerardf · Jun 8th 2007

I have 2 cells that dump data in a column format. I need to combine these two cells with a ' after the data in the column. This macro will loop down until both column J and K are empty,
I need a Macro that
1. Will combine both cells
2. Data will be value format not formula
3. Macro Will run as soon as workbook is opened

Ex.

F J K
1 Prop MD MD Objective
2 1' Test Data 1 Test Data
3 235' next data 235 next data

JimFuller1 · Jun 8th 2007

Re: Macro To Combine 2 Cells In Value Format

gerardf,

That's a little confusing. Can you make a small sample worksheet to post that has sample data and the expected results?

Jim

gerardf · Jun 8th 2007

Re: Macro To Combine 2 Cells In Value Format

I habe attached an example with the results highlighted in yellow.

JimFuller1 · Jun 8th 2007

Re: Macro To Combine 2 Cells In Value Format

Try something like this:

Code

Sub Macro2()


Dim rCell As Range


For Each rCell In Range("F2", Range("F" & Range("J65536").End(xlUp).Row))
    rCell.FormulaR1C1 = "=RC[4]&""' ""&RC[5]"
    rCell = rCell.Value
Next


End Sub

Display More

Jim

rbrhodes · Jun 10th 2007

Re: Macro To Combine 2 Cells In Value Format

Hi gerard,

here's yours, fixed

Code

Sub LoopRange1()


Dim x As Long


    'Have x start at row 2
    x = 2
    'Loop Through Target Depth & Objective until Blank Row is Found
    Do While Cells(x, 10).Value <> ""
        'This will put the values of the tenth and eleventh column
        'together with a ' after column ten and a space in between
        Cells(x, 6).Value = Cells(x, 10) & "' " & Cells(x, 11).Value
        x = x + 1
    Loop


End Sub

Display More

Macro To Combine 2 Cells In Value Format

Participate now!

Share