Formatting combined numbers in Excel

Question

I have a table of data in Excel which has wind direction in one column and wind speed in another. I want to combine the wind speed and direction and then format it.

So for example

WD	WS	COMBINED
050	12.23	50 12.23
360	21.54	360 21.54

I need the combined data to look like: 050 12 and 360 21

I combined the data in a new column. I cannot format the numbers how I need them.

Public Sub CombineWind()

    Dim xy As Worksheet

    Set xy = Worksheets("combine")
    Dim LRow As Long
    With ActiveSheet
        LRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        .Columns(8).Insert
        xy.Range("H3:H" & LRow).Formula = "=B3 & "" "" & C3"
    End With

    With ActiveSheet
        LRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        .Columns(9).Insert
        xy.Range("I3:I" & LRow).Formula = "=D3 & "" "" & E3"
    End With

    With ActiveSheet
        LRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        .Columns(10).Insert
        xy.Range("J3:J" & LRow).Formula = "=F3 & "" "" & G3"
    End With

    Range("H2") = "Average"
    Range("I2") = "Min"
    Range("J2") = "Max"
End Sub

Answer 1

The snippet below, while not ready to paste and perform, should help you on your way.

Dim Arr As Variant

With ActiveSheet
    ' take the value of a range from A1 to end x 7 columns
    Arr = .Range(.Cells(1, 1), .Cells(.Rows.Count, "A").End(xlUp)).Resize(, 7).Value
End With

With xy
    For R = 2 To .Cells(.Rows.Count, "A").End(xlUp).Row
        .Cells(R, "H").Value = Format(Arr(3, 2), "000 ") & Format(Arr(3, 3), "hh:mm")
    Next R
End With

The use of ActiveSheet is always dangerous. Better name the sheet in the code.

The snippet above reads the enter sheet into an array. The important thing is to start the array in A1 so that Arr(1, 1) = A1 and, therefore, Arr(3, 2) = B3. It's easy to read without further help.

.Cells(R, "H") identifies the correct cell to set the Value property of but Format(Arr(3, 2), "000 ") & Format(Arr(3, 3), "hh:mm") only specifies B3 and C3 of the taken array which must be made dynamic by probably making only the row numbers adjust in some relationship to R. The point is to show how to set the value of the "Combined" column.