Run time error 91 - Method variable or with block variable not set

[danbates]

Hi,

I have moved a section of coding from the sheet1 module to just a module.
(I want to try and tidy my coding).

The code was working fine in the sheet module but now I have moved it, it comes up with this 91 error.

I am fairly sure it is to do with the sheet name or the "activesheet" part but I can't figure out how to sort it.

This is the part it is highlighting:

sValueA = Trim(ActiveSheet.Cells(target.Row, "A").Value)

Please can someone help me and any help would be much appreciated.

Regards

Dan

 

[Joe4]

The "ActiveSheet" part is not necessary. If you leave it off, it will default to the ActiveSheet.

However, the error message you listed may not have anything to do with that line of code.
Can you post your complete code?
If you hit "debug", what line does it highlight?

 

[danbates]

Hi Joe,

I removed the ActiveSheet part and it come up with an compile error and highlighted the .cells part after the deleted Activesheet.

Here is my complete code, it is a very rough code so please be kind lol

Sub ColumnA()

Dim sValueA  As String
Dim lrcd As Long
Dim target As Range

'Update Only if Column 'A' is NOT BLANK
sValueA = Trim(ActiveSheet.Cells(target.Row, "A").Value)
If Len(sValueA) = 0 Then
GoTo HERE
Else
If Len(sValueA) < 8 Then
 
Application.EnableEvents = False
      
'Unprotect the Sheet
Call UnprotectTheActiveSheet
    
lrcd = Sheets("Changeover Form").Range("A" & Rows.Count).End(xlUp).Row


Sheets("Changeover Form").Cells(lrcd, "A").Select
Sheets("Changeover Form").Cells(lrcd, "A").Interior.Color = RGB(255, 0, 0) 'Red


MsgBox "Order number needs a min of 8 characters"


ActiveCell.Interior.Color = RGB(255, 255, 255) 'White
ActiveCell.ClearContents


Application.EnableEvents = True

Call ProtectTheActiveSheet


End If
HERE:
End If
End Sub

Thanks

 

[Joe4]

You are referencing a range named "target", but you haven't defined it anywhere!
So "target" isn't set to anything. This will cause errors.

"Target" is often used in Event Procedures, which is VBA code that is automatically triggered upon some event happening.
In those cases, "Target" is the cells that was changed or selected that triggered the Event Procedure to run.
However, that only has meaning in Event Procedure code, which your code is not.
All ranges you use in your code must be defined and set to something.

 

[danbates]

Hi Joe,

thank you for pointing me in the right direction.
I have sorted it kind of, I just moved the first few lines back to the sheet module.

Thanks again

Dan