combining cell values and adding a custom text to a cell value via VBA

[Xalova]

So i have a table created in Excel. I want to have a macro which allows me to fill a certain cell (lets say "B1") with the cell values from cell "A1" and "A2". Also i want that at the end of the cell there always is a "mm", if that makes any sense.

Basicly what i want is the excel formula

=SUBSTITUTE(A1;"_";" ")&" x "&A2&"mm"

as a macro.

What i mean is:

Inputs	Outputs
ISO_4014	ISO 4014 x 50mm
50

 

[mohadin]

sure

Sub test1()
    Dim a As Variant
    Dim i, k
    a = Range("A1").CurrentRegion.Offset(1)
    ReDim b(1 To UBound(a) / 2)
    For i = 1 To UBound(a) - 1 Step 2
        b(k + 1) = WorksheetFunction.Substitute(a(i, 1), "_", " ") & " x " & a(i + 1, 1) & "mm"
        k = k + 1
    Next
    Range("B2").Resize(UBound(b)) = Application.Transpose(b)
End Sub

Sub test()
    Dim a As Variant
    Dim i
    a = Range("A1").CurrentRegion.Offset(1)
    ReDim b(1 To UBound(a))
    For i = 1 To UBound(a) - 1 Step 2
        b(i) = WorksheetFunction.Substitute(a(i, 1), "_", " ") & " x " & a(i + 1, 1) & "mm"
    Next
    Range("B2").Resize(UBound(b)) = Application.Transpose(b)
End Sub

 

[Xalova]

sure

Sub test1()
    Dim a As Variant
    Dim i, k
    a = Range("A1").CurrentRegion.Offset(1)
    ReDim b(1 To UBound(a) / 2)
    For i = 1 To UBound(a) - 1 Step 2
        b(k + 1) = WorksheetFunction.Substitute(a(i, 1), "_", " ") & " x " & a(i + 1, 1) & "mm"
        k = k + 1
    Next
    Range("B2").Resize(UBound(b)) = Application.Transpose(b)
End Sub

Sub test()
    Dim a As Variant
    Dim i
    a = Range("A1").CurrentRegion.Offset(1)
    ReDim b(1 To UBound(a))
    For i = 1 To UBound(a) - 1 Step 2
        b(i) = WorksheetFunction.Substitute(a(i, 1), "_", " ") & " x " & a(i + 1, 1) & "mm"
    Next
    Range("B2").Resize(UBound(b)) = Application.Transpose(b)
End Sub

sure

Sub test1()
    Dim a As Variant
    Dim i, k
    a = Range("A1").CurrentRegion.Offset(1)
    ReDim b(1 To UBound(a) / 2)
    For i = 1 To UBound(a) - 1 Step 2
        b(k + 1) = WorksheetFunction.Substitute(a(i, 1), "_", " ") & " x " & a(i + 1, 1) & "mm"
        k = k + 1
    Next
    Range("B2").Resize(UBound(b)) = Application.Transpose(b)
End Sub

Sub test()
    Dim a As Variant
    Dim i
    a = Range("A1").CurrentRegion.Offset(1)
    ReDim b(1 To UBound(a))
    For i = 1 To UBound(a) - 1 Step 2
        b(i) = WorksheetFunction.Substitute(a(i, 1), "_", " ") & " x " & a(i + 1, 1) & "mm"
    Next
    Range("B2").Resize(UBound(b)) = Application.Transpose(b)
End Sub

soooo, is it too much to ask if you can evaluate this code and explain it to me?

for example: What does resize and UBound do? And why the offset?

 

[mohadin]

Hi
About what you are asking:
-The line

a = Range("A1").CurrentRegion.Offset(1)

To allocate the data to an array (a): starting from Range("A1") to the Current region where the data available the shift down one row to escape the Headers,
so now we have an array with the data it has 2 dimensions, we can read them as Ubounds And Lbound OK
-About Resize issue

Range("B2").Resize(UBound(b))

Means from range("B2") and go down number of cells equal to the (b) array size
Hope this help