Variable Error '1004' - Application-defined or object-defined error

I wrote the following code and am trying to use a variable as the lookup value in a vlookup function. But, every time the macro runs, an error occurs with the variable bolded below.

Does anyone know why this is occurring and how to fix it?


Private Sub Worksheet_Change(ByVal Target As Range)

Dim cellvalue As Variant
cellvalue = Format(ActiveCell.Offset(0, -1).Value, "00000000000")

If Not Intersect(Target, Range("A10:A110")) Is Nothing Then
Target.Offset(0, 1).ClearContents
ActiveCell.Formula = "=vlookup(" & cellvalue & ",'Project # and Name'!A1:B2,2,FALSE)"
End If

End Sub


Thank you!!

You're changing values in Column A? Offset(0, -1) means zero rows and one column to the left of the Active Cell, which if it's in column A doesn't have anything to the left of it, so it will throw an error.

Ah, that would be it. I thought I tested to see which cell became the active cell once the Target.Offset(0,1).ClearContents line was completed. I had a message box pop up and I thought it said B2, but I guess I was mistaken. Thanks for catching that!

As a follow up question, the value in the active cell is going to be input as C0000000001, C0000000002, etc (basically starting with a letter and then followed by ten digits. The problem is that the code is chopping off the zeroes and thinks I'm referring to a cell name, such as C1 or C2 instead of the text string in the active cell. I had researched this earlier and was told that the Format function would correct this, but it doesn't seem to be. I've also tried inputting a Text function in the vlookup around the cellvalue variable, but it didn't work either.

Do you have any suggestions for that?

That seems to be the final problem standing in the way of this working correctly...for now at least.

Ah, that would be it. I thought I tested to see which cell became the active cell once the Target.Offset(0,1).ClearContents line was completed. I had a message box pop up and I thought it said B2, but I guess I was mistaken. Thanks for catching that!

As a follow up question, the value in the active cell is going to be input as C0000000001, C0000000002, etc (basically starting with a letter and then followed by ten digits. The problem is that the code is chopping off the zeroes and thinks I'm referring to a cell name, such as C1 or C2 instead of the text string in the active cell. I had researched this earlier and was told that the Format function would correct this, but it doesn't seem to be. I've also tried inputting a Text function in the vlookup around the cellvalue variable, but it didn't work either.

Do you have any suggestions for that?

That seems to be the final problem standing in the way of this working correctly...for now at least.

Oh, wait, I just tried ActiveCell.Address in place of the cellvalue variable and it's working correctly now!