Excel Macro to expand acronyms

[jaaimee]

Hi everyone,

I am working on a project that requires me to expand the acronym in column A of excel and copy the expanded term to column B. The acronyms i am working on mainly revolves around the engineering mechanical side. An example would be:

Column A Column B
AHU-L8-01 Air Handling Unit
AHU-L9-01 Air Handling Unit
AHU-L10-01 Air Handling Unit

FCU-L8-01 Fan Coil Unit
FCU-L9-01 Fan Coil Unit
FCU-L10-01 Fan Coil Unit

It revolves alot of repetitive work therefore i hope to find a macro that can cut down the time taken on this.
I really hope there is someone who is able to provide some direction as to where should i start as i am a beginner in VBA.

Any help is greatly appreciated! Thank you in advance!

Jaime

 

[jolivanes]

Please don't quote if not absolutely required. Refer to a post number if needed.

It would be smart to use a code you understand in case you want/need to make changes at a later stage.
Since you have an option, it should be an easy choice.
Yes, LEN gives you the length of the string/amount of characters.
The 2nd code gets the value at the same "row" in the next "column" in the array, just like in the sheet really.
This would be a similar approach but with 2 arrays instead of one.

Sub Maybe_C()
Dim acrArr, fnArr, c As Range, i As Long
    acrArr = Sheets("Sheet3").Range("A1:A" & Sheets("Sheet3").Cells(Rows.Count, 1).End(xlUp).Row).Value
    fnArr = Sheets("Sheet3").Range("B1:B" & Sheets("Sheet3").Cells(Rows.Count, 2).End(xlUp).Row).Value
        For Each c In Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)
            For i = LBound(acrArr) To UBound(acrArr)
                If Left(c, InStr(c, "-") - 1) = acrArr(i, 1) Then c.Offset(, 1) = fnArr(i, 1): Exit For
            Next i
        Next c
End Sub

 

[jaaimee]

Oops! Sorry! Will take note of it!

For #19 , the code you posted with Instr, it cannot run as long as there is empty rows in between.
I tried to use If acrArr(i) > 0 ? (pardon if this code is totally out of logic)
I am not sure whether if this is correct, but it didnt work too.

An example of the table i am working on would be something like this.

[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]Equipment Code[/TD]
[TD]Level[/TD]
[TD]Asset Code[/TD]
[/TR]
[TR]
[TD]ACMV System[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Air Handling Unit[/TD]
[TD]L9[/TD]
[TD]AHU-L9-01[/TD]
[/TR]
[TR]
[TD]Fan Coil Unit[/TD]
[TD]L9[/TD]
[TD]FCU-L9-02[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Fire Protection System[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Hose Reel Pump[/TD]
[TD]L10[/TD]
[TD]HRP-L10-05[/TD]
[/TR]
</tbody>[/TABLE]

So the code will always stop at 4th row, since the 5th row is empty. And it will prompt an error saying "invalid procedure call or argument".

 

[jolivanes]

You could use an If statement like

If Len(c) > 0 Then
......
......
......
End If

or something similar.

 

[jaaimee]

Yes! Thank you! I managed to get the correct code to make it work!
Yet i also realise that when i have "Option Explicit" at the top of the coding, the code does not work.
And in my understanding, Option Explicit actually forces you to declare all variables.
But i dont quite understand the definition of "variables" as there are dim statements in the code, so doesn't this count?