Using VBA to concatenate two columns

[Belinda]

Hello,

I have an Excel table that has a fixed number of columns but the number of rows can vary depending on the data update. The columns have a header row.

I want to use VBA code to concatenate the text contents of Column F and H with an underscore in between and place the contents in Column J.

E.g. Cell F2 has "FC TAB" and Column H2 has "Allegra Plus". The result in Cell J2 would be "FC TAB_Allegra Plus"

Thank you.

 

[Capell]

I think the compatibility fix for IE11 might have worked, so let's give it a go.
I'm trying to convert a spreadsheet of weather station observations into a format that will work with WindRose3 (Enviroware). The program does what it says on the tin - rather well, actually.
The conversions are really quite simple, as can be seen by this code, which works properly and I think is self explanatory:

Rowcount = Cells(1048576, 2).End(xlUp).Row
For Count = 2 To Rowcount
Cells(Count, 4) = Cells(Count, 4) * 0.44704
Cells(Count, 6) = Cells(Count, 6) * 0.44704
Cells(Count, 5) = Application.WorksheetFunction.VLookup(Cells(Count, 5), Worksheets("Wind").Range("B4:C24"), 2)
Cells(Count, 7) = Application.WorksheetFunction.VLookup(Cells(Count, 7), Worksheets("Wind").Range("B4:C24"), 2)
Cells(Count, 1) = Format(Cells(Count, 2), "dd/mm/yyyy") & " " & Format(Cells(Count, 3), "hh:mm")
Next

Column 2 contains observation dates in format "dd/mm/yyyy" format.
But as an exercise I'd like convert this to range handling rather than cell by cell manipulation, if only because that is so much faster.

I've got most of this converted nicely:

Dim rngData     As Range
Set rngData = Range(Cells(2, 4), Cells(Rowcount, 4))
        rngData = Evaluate(rngData.Address & "*0.44704")
        rngData.NumberFormat = "0.00"
    Set rngData = Range(Cells(2, 6), Cells(Rowcount, 6))
        rngData = Evaluate(rngData.Address & "*0.44704")
        rngData.NumberFormat = "0.00"
    Set rngData = Range(Cells(2, 5), Cells(Rowcount, 5))
    rngData = Application.WorksheetFunction.VLookup(rngData, Worksheets("Wind").Range("B4:C21"), 2, False)
    Set rngData = Range(Cells(2, 7), Cells(Rowcount, 7))
    rngData = Application.WorksheetFunction.VLookup(rngData, Worksheets("Wind").Range("B4:C21"), 2, False)

But how do you convert the concatenation line to work properly?

Range("A2:A" & Rowcount).Value = Evaluate("=B2:B" & Rowcount & "&""""&" & "C2:C" & Rowcount)

Gives dates that are far in the future

Range("A2:A" & Rowcount).Formula = "=B2 & C2"

Gives apparent Julian based numbers which have obviously converted the date section using "mm/dd/yyyy" format.

So how/can you make this conversion using Range handling?

 

[Peter_SSs]

So, all sorted in relation to typing responses in the forum now?

 

[Capell]

So, all sorted in relation to typing responses in the forum now?

Yes, I think so. Thanks for the help.

 

[Peter_SSs]

Yes, I think so. Thanks for the help.

Great. No problem.

 

[jawohl]

I know this thread is ancient but it looks like you are still very active here. I wanted to use this snippet of code for my project but wanted to use R1C1 notation so I can loop through this more than once. Nothing seems to work for me. Here is what I am trying: (just trying Column "C" for now before inserting variables)

With Range(Cells(1, 3), Range(Cells(rows.Count, 3)).End(xlUp))
        .Offset(, 2).Value = Evaluate(.Address & " & " & .Offset(, 1).Address)
End With

I thought I could just exchange the A1 notation with Cells but I get run-time error '1004': Method 'Range' of object '_Global' failed.

Any help or even a resource that might help would be greatly appreciated. Thanks!

 

[Fluff]

Try

With Range(Cells(1, 3), Cells(Rows.Count, 3).End(xlUp))