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

 

[LazyBug]

Try this with active A1 cell:

Sub x()
Dim c As Range, t$
    Set c = ActiveCell: t = ActiveCell.Value

    If IsEmpty(c(1, 2)) Then
        c(1, 2).Value = Replace(t, "_", " ") & " x " & c(2, 1).Value & "mm"
    Else
        MsgBox "Output cell is not empty."
    End If
End Sub

 

[Xalova]

Try this with active A1 cell:

Sub x()
Dim c As Range, t$
    Set c = ActiveCell: t = ActiveCell.Value

    If IsEmpty(c(1, 2)) Then
        c(1, 2).Value = Replace(t, "_", " ") & " x " & c(2, 1).Value & "mm"
    Else
        MsgBox "Output cell is not empty."
    End If
End Sub

is there a possibility without an active cell?

 

[mohadin]

Hi
Pick one

Sub test1()
    Dim a As Variant
    Dim i, k
    a = Cells(2, 1).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
    Cells(2, 2).Resize(UBound(b)) = Application.Transpose(b)
End Sub

Sub test2()
    Dim a As Variant
    Dim i
    a = Cells(2, 1).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
    Cells(2, 2).Resize(UBound(b)) = Application.Transpose(b)
End Sub

 

[Xalova]

Hi
Pick one

Sub test1()
    Dim a As Variant
    Dim i, k
    a = Cells(2, 1).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
    Cells(2, 2).Resize(UBound(b)) = Application.Transpose(b)
End Sub

Sub test2()
    Dim a As Variant
    Dim i
    a = Cells(2, 1).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
    Cells(2, 2).Resize(UBound(b)) = Application.Transpose(b)
End Sub

Ok, so... Im a newbie to VBA alltogether. Can you tell me where the cells are defined exactly? Or is it relative to my current active cell?

 

[mohadin]

OK
Your data in range("A2") dowm
The result in B2 down since you have headers

 

[mohadin]

Book1
	A	B
1	Inputs	out
2	ISO_4014	ISO 4014 x 50mm
3	50
4	ISO_4015	ISO 4015 x 51mm
5	51
6	ISO_4016	ISO 4016 x 52mm
7	52
8	ISO_4017	ISO 4017 x 53mm
9	53
10	ISO_4018	ISO 4018 x 54mm
11	54
12	ISO_4019	ISO 4019 x 55mm
13	55
14	ISO_4020	ISO 4020 x 56mm
15	56
16	ISO_4021	ISO 4021 x 57mm
17	57
18	ISO_4022	ISO 4022 x 58mm
19	58
Periode

Or

Book1
	A	B
1	Inputs	out
2	ISO_4014	ISO 4014 x 50mm
3	50	ISO 4015 x 51mm
4	ISO_4015	ISO 4016 x 52mm
5	51	ISO 4017 x 53mm
6	ISO_4016	ISO 4018 x 54mm
7	52	ISO 4019 x 55mm
8	ISO_4017	ISO 4020 x 56mm
9	53	ISO 4021 x 57mm
10	ISO_4018	ISO 4022 x 58mm
11	54
12	ISO_4019
13	55
14	ISO_4020
15	56
16	ISO_4021
17	57
18	ISO_4022
19	58
Periode

 

[Xalova]

OK
Your data in range("A2") dowm
The result in B2 down since you have headers

But where is the range "A2" defined in your VBA Code? Is it "Cells(2,1)? Is it in the RC format?

 

[mohadin]

Cells(2,1) same Range("A2")

BTW

a = Cells(1, 1).CurrentRegion.Offset(1)

a = Cells(2, 1).CurrentRegion.Offset(1)

Both gives same result

 

[Xalova]

Cells(2,1) same Range("A2")

So could theoretically Range("A2") and Cells(2,1) be replaced with eachother?