Macro runs correctly on every second run

[cmf30]

Hi,

I am a complete beginner so I assume this is something obvious but I haven't been able to figure it out.

Essentially, the below code runs correctly every second time I run it. What the code does is

1) Copies a number of columns from one sheet to another
2) Formats the TitleCode Column to text in both sheets
3) Uses a Vlookup with Worksheet wsTitre to get a title that corresponds to a title code.

It is the third step that causes the issue. The title only gets populated on every second run, would it be something to do with the loop? This is the loop code.

For i = 2 To intLoopNumber

On Error Resume Next

    strTitleCode = trg.Range("E" & i).Value
    strFullTitle = Application.WorksheetFunction.VLookup(strTitleCode, wsTitre.Range("$A$2:$B$30"), 2, False)
    trg.Range("F" & i).Value = strFullTitle

Next i

On Error GoTo 0

The full code is copied below in case it helps.

Sub CreateRegister()

Dim src As Worksheet
Dim trg As Worksheet
Dim wsTitre As Worksheet
Dim strFullTitle As String
Dim strTitleCode As String
Dim intLoopNumber As Integer


'Set relevant worksheet variables
Set src = ThisWorkbook.Worksheets("Faculty")
Set trg = ThisWorkbook.Worksheets("ExpertRegister")
Set wsTitre = ThisWorkbook.Worksheets("TITREFAC")

'Copy relevant columns over to ExpertRegister Sheet from Faculty Sheet
src.Range("P1:P250").Copy Destination:=trg.Range("E1")
trg.Range("E1").Value = "Title Code"
trg.Range("F1").Value = "Full Title" 'Blank column for now.

src.Range("X1:X250").Copy Destination:=trg.Range("G1")
trg.Range("G1").Value = "Active"


'Format the Title Code to text in both ExpertRegister and TITREFAC sheets
   wsTitre.Select
   wsTitre.Range("A2:A30").Select
   Selection.NumberFormat = "@"

   trg.Select
   trg.Range("E1:E250").Select
   Selection.NumberFormat = "@"
    
'Get rows to iterate though
intLoopNumber = Cells(trg.Rows.Count, 3).End(xlUp).Row

For i = 2 To intLoopNumber

On Error Resume Next

    strTitleCode = trg.Range("E" & i).Value
    strFullTitle = Application.WorksheetFunction.VLookup(strTitleCode, wsTitre.Range("$A$2:$B$30"), 2, False)
    trg.Range("F" & i).Value = strFullTitle

Next i

On Error GoTo 0
    
End Sub

 

[jasonb75]

Without seeing the sheet to confirm any potential issues, the only reasons that I can see for any problems would be,

strTitleCode not found in wsTitre.Range(A2:A30)

Merged cells in column E

 

[cmf30]

Thank you for this, I will check for these things.