Need help with a Concatenation and Abbreviation Macro

[rgrocks]

Help I'm working on my very first VBA Macro. I'm trying to figure out how to create a concatenated name but the data isnt very uniform. I took a 6 hour VBA Excel Macro class but I seem to be out of my depth for this.
Currently the code only works on the cell I have highlighted which is fine for my purposes, but something cleaner and more universal might be nice.
My class didnt cover anything like I want to do "/

Attached is a sample data
https://www.dropbox.com/s/zqnlujbe2sw0k15/VBA Sample to Share.xlsx?dl=0

I want to put three columns (2 of them will be modified) together to form a new name

The names will look like the following examples

&Division&_&CityAbrv&_R&3DigitAssetNo

CC_HST_PH48
SO_HLB_R050
SO_HLB_RR52
SO_WND_R086
ST_JKN_RM03

My Criteria

1) Division is taken straight from the division column
2) CityAbrv needs to be transformed from the "City" Column and abreviated using an attached list of city abreviations on the worksheet "Official City Code"
3) The last 3 characters of the Asset No. The issue with this is that the Asset No. isnt exactly uniform data I want it to take the last 3 characters.
b) if there is a "-" or " " within the last 3 characters, ignore it and dont use it but instead move on to the next charcter to the left for example, DR H 23 should just be H23
4) There is existing data that I dont want to overwrite so this macro needs to only run on the empty cells on the column I'm modifying.
5) Repeat for the remaining blank cells that have data to concatenate

Here is the code I have so far

Sub SCADALocationName()

ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[-3],""_"",RC[-1],""_R"",RIGHT(RC[-2],3))"

End Sub


Please, I understand you may not have the time to help me write it but if you could just point me to the syntax I would need and similar macro's so I could dissect it for my purposes. Just point me towards specific lessons and I could try to figure it out. Thanks

 

[Max1616]

Try this!

Sub rgrocks()

Dim lrow As Integer
Dim i As Integer
Dim CCsht As Worksheet
Dim ws As Worksheet

Set CCsht = Sheets("Official City Code")
Set ws = ActiveSheet

lrow = Cells(Rows.Count, 3).End(xlUp).Row

For i = 4 To lrow
    If Len(Cells(i, 4)) = 0 Then
        Cells(i, 4) = Cells(i, 1) & "_" & WorksheetFunction.Index(CCsht.Columns(1), WorksheetFunction.Match(ws.Cells(i, 3), CCsht.Columns(2), 0)) & "_R" & _
        Right(WorksheetFunction.Substitute(WorksheetFunction.Substitute(Cells(i, 2), " ", ""), "-", ""), 3)
    
    End If
Next i

End Sub

Seems to be working for me.

Let me know if you want an explanation.

Sincerely,
-Max

 

[Max1616]

I didn't realize that you may have wanted a formula in the cell. If so here is the excel formula I would use:
=CONCATENATE(A5,"_",INDEX('Official City Code'!A:A,MATCH('Balnk SCADA'!C5,'Official City Code'!B:B,0)),"_R",RIGHT(SUBSTITUTE(SUBSTITUTE(B5," ",""),"-",""),3))

Translating that to VBA:

        Cells(i, 4) = "=Concatenate(" & Cells(i, 1).Address(0, 0) & ",""_"",Index('Official City Code'!A:A,MATCH('Balnk SCADA'!" & _
        Cells(i, 3).Address(0, 0) & ",'Official City Code'!B:B,0)),""_R"",RIGHT(SUBSTITUTE(SUBSTITUTE(" & Cells(i, 2).Address(0, 0) _
        & ","" "",""""),""-"",""""),3))"

Here is the whole code placing the Formula in the cell instead of just the end result:

Sub rgrocks()

Dim lrow As Integer
Dim i As Integer
Dim CCsht As Worksheet
Dim ws As Worksheet

Set CCsht = Sheets("Official City Code")
Set ws = ActiveSheet

lrow = Cells(Rows.Count, 3).End(xlUp).Row

For i = 4 To lrow
    If Len(Cells(i, 4)) = 0 Then
        Cells(i, 4) = "=Concatenate(" & Cells(i, 1).Address(0, 0) & ",""_"",Index('Official City Code'!A:A,MATCH('Balnk SCADA'!" & _
        Cells(i, 3).Address(0, 0) & ",'Official City Code'!B:B,0)),""_R"",RIGHT(SUBSTITUTE(SUBSTITUTE(" & Cells(i, 2).Address(0, 0) _
        & ","" "",""""),""-"",""""),3))"
    End If
Next i

End Sub

Hope this helps! Also this assumes you are running the code while Balnk SCADA is the active sheet.

Sincerely,
-Max

 

[rgrocks]

Try this!

Sub rgrocks()

Dim lrow As Integer
Dim i As Integer
Dim CCsht As Worksheet
Dim ws As Worksheet

Set CCsht = Sheets("Official City Code")
Set ws = ActiveSheet

lrow = Cells(Rows.Count, 3).End(xlUp).Row

For i = 4 To lrow
    If Len(Cells(i, 4)) = 0 Then
        Cells(i, 4) = Cells(i, 1) & "_" & WorksheetFunction.Index(CCsht.Columns(1), WorksheetFunction.Match(ws.Cells(i, 3), CCsht.Columns(2), 0)) & "_R" & _
        Right(WorksheetFunction.Substitute(WorksheetFunction.Substitute(Cells(i, 2), " ", ""), "-", ""), 3)
    
    End If
Next i

End Sub

Seems to be working for me.

Let me know if you want an explanation.

Sincerely,
-Max

Thank you so much! This works amazingly well. I'm going to try to explain your code and correct me where I'm wrong so I can better understand it.

First you declared your variables

Then you set what your worksheet variables were.

You assigned "lrow" to be the number of rows that have data in column 3 (Cells(Rows.Count, 3)), however I don't understand what ".End(xlUp).Row" does

next you stated "i" at 4 because that's where the first data set starts on row 4 to "lrow" so the macro doesnt do it on every empty cell on column 4

Then you started your if statement. I'm not exactly sure what the "len" function does but you said that if =0 concatenate it using the formula you laid out.
Cells(i, 1) is the Division
WorksheetFunction.Index(CCsht.Columns(1), WorksheetFunction.Match(ws.Cells(i, 3), CCsht.Columns(2), 0)) was how you matched the city with its abbreviation. Can you explain more thoroughly how this is done, I'm confused within all the embedded statements.
& "_R" & _ this part confuses me because if I'm reading this it says to concatenate _R_ but when the macro is run the second _ isnt there (which I dont want it to) I'm just confused why the "& _" at the end is there
Right(WorksheetFunction.Substitute(WorksheetFunction.Substitute(Cells(i, 2), " ", ""), "-", ""), 3) This also confuses me. I think this is saying take the right 3 characters from the right but only after you substitute the " " and the "-" with "".


Can you also modify it a little bit. I noticed that if a city listed isnt in the city code the macro stops, is there a way we can make it so if it faces that error that it just skips entering a value in column 4 and moves on to the next i?


Thank you so much, VBA is powerful and I cant wait to write it as easily as you do.

 

[rgrocks]

OMG Thanks for all youre help. I was able to modify the code for my needs.

Sub rgrocks()

Dim lrow As Integer
Dim i As Integer
Dim CCsht As Worksheet
Dim ws As Worksheet


Set CCsht = Sheets("Official City Code")
Set ws = ActiveSheet


lrow = Cells(Rows.Count, 3).End(xlUp).Row


For i = 4 To lrow
    If Len(Cells(i, 4)) = 0 Then
    If Not IsEmpty(Cells(i, 3)) Then
    
    On Error Resume Next
    
    Cells(i, 4) = Cells(i, 1) & "_" & WorksheetFunction.Index(CCsht.Columns(1), WorksheetFunction.Match(ws.Cells(i, 3), CCsht.Columns(2), 0)) & "_R" & _
        Right(WorksheetFunction.Substitute(WorksheetFunction.Substitute(Cells(i, 2), " ", ""), "-", ""), 3)
    
    End If
    End If
Next i


End Sub

Thank you so much!

 

[Max1616]

Hello! Very good job at reading the code. Here is an explanation on the areas you were unclear on:

You assigned "lrow" to be the number of rows that have data in column 3 (Cells(Rows.Count, 3)), however I don't understand what ".End(xlUp).Row" does

You are partially correct here. Cells(rows.count,3)) alone gives me Cells C1048576. What the ".End(xlUp).Row" is similart to the shift+up keys on your keyboard. It starts at Cells C1048576 and moves up until it finds a cell that contains a value. So if your data set in column C ends in row 100, this function would return C100:

[I]Cells(Rows.Count, 3)[/I][I].End(xlUp)[/I]

You can do a smiliar action using down, right and left (notice that right in left are worded a little differently):

[I]Cells(1, 3)[/I][I].End(xldown)[/I]
[I]Cells(1, 3)[/I][I].End(xltoRight)[/I]
[I]Cells(Columns.Count, 3)[/I][I].End(xltoLeft)[/I]

Adding the ".Row" at the end just returns the row value instead of the cell reference. So if "Cells(Rows.Count, 3).End(xlUp)" returned C100 then adding the ".Row" Would give us 100.

These are tricks every vba coder should know as they are very useful!

Then you started your if statement. I'm not exactly sure what the "len" function does but you said that if =0 concatenate it using the formula you laid out.

The Len function is another great tool to have in your arsenal. What this does is counts the number of characters in a cell or a string of text (this function is available as an excel formula as well). So when I say len(cells(i,4)) = 0, I am saying when cells(i,4) is blank (or has 0 characters in the cell) then do this...

WorksheetFunction.Index(CCsht.Columns(1), WorksheetFunction.Match(ws.Cells(i, 3), CCsht.Columns(2), 0)) was how you matched the city with its abbreviation. Can you explain more thoroughly how this is done, I'm confused within all the embedded statements.

Here I am using "WorksheetFunction" which allows me to use an excel formula and VBA objects together. The formula I am using here an index match formula (which is similar to a vlookup but a bit more flexible).

The index match is set up like this in an excel formula: =INDEX ( Column I want a return value from , MATCH ( My Lookup Value , Column I want to Lookup against , Enter “0” ))

Here is a good resource to learn about index match: How to Use INDEX MATCH

So the worksheetfunction I used follows this exact set up using VBA. (also CCsht is the sheet variable looking at your CityCodeSheet).

Does this all make sense?

& "_R" & _ this part confuses me because if I'm reading this it says to concatenate _R_ but when the macro is run the second _ isnt there (which I dont want it to) I'm just confused why the "& _" at the end is there
Right(WorksheetFunction.Substitute(WorksheetFunction.Substitute(Cells(i, 2), " ", ""), "-", ""), 3) This also confuses me. I think this is saying take the right 3 characters from the right but only after you substitute the " " and the "-" with "".

You nailed this one on the head! I'm pulling the right 3 digits from a string of text here. The string of text is your original string but substituting all " " and "-" with a blank value, "". Good Job!

For the "_R" & _, when we use "_" without quotations around it it acts as a line break within the code so you don't have a long string of code that you have to scroll right to read. So all that is saying is "_R" & "move down to the next line of code and continue the code". Does this make sense? You can see excel automatically do the same thing when you record macros that require quite a bit of code. If you record making a pivot table you will see a lot of these _, which don't really mean anything except for code organization.


If you are still having issues with it stopping on an error you can use this an place it right above the code that is failing:
On Error Resume Next

Let me know if you have any more questions! Sorry for the delayed response, I was int he hospital after breaking my ankle while playing basketball haha.

Sincerely,
-Max

 

[rgrocks]

Hello! Very good job at reading the code. Here is an explanation on the areas you were unclear on:



You are partially correct here. Cells(rows.count,3)) alone gives me Cells C1048576. What the ".End(xlUp).Row" is similart to the shift+up keys on your keyboard. It starts at Cells C1048576 and moves up until it finds a cell that contains a value. So if your data set in column C ends in row 100, this function would return C100:

[I]Cells(Rows.Count, 3)[/I][I].End(xlUp)[/I]

You can do a smiliar action using down, right and left (notice that right in left are worded a little differently):

[I]Cells(1, 3)[/I][I].End(xldown)[/I]
[I]Cells(1, 3)[/I][I].End(xltoRight)[/I]
[I]Cells(Columns.Count, 3)[/I][I].End(xltoLeft)[/I]

Adding the ".Row" at the end just returns the row value instead of the cell reference. So if "Cells(Rows.Count, 3).End(xlUp)" returned C100 then adding the ".Row" Would give us 100.

These are tricks every vba coder should know as they are very useful!




The Len function is another great tool to have in your arsenal. What this does is counts the number of characters in a cell or a string of text (this function is available as an excel formula as well). So when I say len(cells(i,4)) = 0, I am saying when cells(i,4) is blank (or has 0 characters in the cell) then do this...



Here I am using "WorksheetFunction" which allows me to use an excel formula and VBA objects together. The formula I am using here an index match formula (which is similar to a vlookup but a bit more flexible).

The index match is set up like this in an excel formula: =INDEX ( Column I want a return value from , MATCH ( My Lookup Value , Column I want to Lookup against , Enter “0” ))

Here is a good resource to learn about index match: How to Use INDEX MATCH

So the worksheetfunction I used follows this exact set up using VBA. (also CCsht is the sheet variable looking at your CityCodeSheet).

Does this all make sense?


You nailed this one on the head! I'm pulling the right 3 digits from a string of text here. The string of text is your original string but substituting all " " and "-" with a blank value, "". Good Job!

For the "_R" & _, when we use "_" without quotations around it it acts as a line break within the code so you don't have a long string of code that you have to scroll right to read. So all that is saying is "_R" & "move down to the next line of code and continue the code". Does this make sense? You can see excel automatically do the same thing when you record macros that require quite a bit of code. If you record making a pivot table you will see a lot of these _, which don't really mean anything except for code organization.


If you are still having issues with it stopping on an error you can use this an place it right above the code that is failing:
On Error Resume Next

Let me know if you have any more questions! Sorry for the delayed response, I was int he hospital after breaking my ankle while playing basketball haha.

Sincerely,
-Max

Thank you so much for the explanation. I will definitely add this to my vba tool kit