VBA check length of a cell and if conditions met, copy to another cell with additional string

[excel01noob]

Hi
I have a dataset that contains under column H a numeric value (user account).
This numeric value is usually 5 digits.
I want to create a macro that checks if the length is 5 and if yes copy the numeric value and paste it under column K but adding a character before the numeric value like this

12345 - column H
X12345 - column K

Have created the below but it does not work, can you help me out?

Sub LengthCheck()

Dim i As Long
Dim Lastrow As Long
Dim MyStr1 As String
Dim MyStr2 As String

Application.ScreenUpdating = False

Lastrow = Cells(Rows.Count, "H").End(xlUp).Row
MyStr1 = "X"
MyStr2 = ("H2:H" & Lastrow)


For i = 2 To Lastrow
If Len(Cells(i, 8)) = 5 Then
'Cells(i, 11).Value = MyStr1 + MyStr2
End If
Next

Application.ScreenUpdating = True

End Sub



In case the numeric account value is more than 5 digits, I would need to refer to another excel file dataset, look the value in that dataset (I'm using a manual VLOOKUP) and take the format from column G, would love to develop the macro for this part as well

 

[Osvaldo Palmeiro]

Hi.
For the first part of your issue try replacing ~~~> Cells(i, 11).Value = MyStr1 + MyStr2 by ~~~> Cells(i, 11).Value = MyStr1 & Cells(i, 8)

For the second part we'll need to see more info from you.