Help with error run-time error '9" (subscript out of Range)

[thong127]

Hello All,

this is in regards to my previous post

https://www.excelforum.com/excel-gen...heet-name.html
Im using the code below but when Im Adding, Deleting rows it keep showing me the error.

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("$B:$C")) Is Nothing Then Exit Sub

Dim Lot1() As String
Dim Lot2() As String


Lot1 = Split(Cells(Target.Row, "B"), " ")
Lot2 = Split(Cells(Target.Row, "C"), " ")
Sheets(1).Name = Lot1(UBound(Lot1)) & " vs " & Lot2(UBound(Lot2))

End Sub

Please help me to remove the popping up error.
Attached is the file that Im using.


Thank you

[tim201110]

Lot1 = Split(Cells(Target.Row, "B").value, " ")

[Fluff13]

Maybe

If Intersect(Target, Range("$B:$C")) Is Nothing Then Exit Sub
If Range("B" & Target.Row) = "" Or Range("C" & Target.Row) = "" Then Exit Sub

[thong127]

Hi Tim,

Thanks for the quick reply. I tried putting your code but still giving me the same error.

I would appreciate if you can make work around.

Thank you.

[thong127]

Hi Fluff13,

I tried to insert your code

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("$B:$C")) Is Nothing Then Exit Sub
If Range("B" & Target.Row) = "" Or Range("C" & Target.Row) = "" Then Exit Sub

Dim Lot1() As String
Dim Lot2() As String


Lot1 = Split(Cells(Target.Row, "B"), " ")
Lot2 = Split(Cells(Target.Row, "C"), " ")
Sheets(1).Name = Lot1(UBound(Lot1)) & " vs " & Lot2(UBound(Lot2))

End Sub

but when I tried to copy and paste some data to column C it gives me a Run-time error 1004.

thank you

[Fluff13]

If your are pasting the numbers try

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("$B:$C")) Is Nothing Then Exit Sub
If Range("B" & Target.Row) = "" Or Range("C" & Target.Row) = "" Then Exit Sub

Dim Lot1() As String
Dim Lot2() As String


Lot1 = Split(Cells(Target.Row, "B"), " ")
Lot2 = Split(Cells(Target.Row, "C"), " ")
Sheets(1).Name = Format(Lot1(UBound(Lot1)), "#,##0.00") & " vs " & Format(Lot2(UBound(Lot2)), "#,##0.00")

End Sub

[thong127]

Thanks Fluff13, It works like a charm!

[Fluff13]

Glad to help & thanks for the feedback