Hi
I'm pretty new to VBA and need some assistance to rename a sheet based on
1. finding a value in the sheet
2. Offsetting 3 cells to the right of the found cell
3. using the value of the offset cell to rename the sheet
Thus far i've been able to find the cell & allocate the value of the offset cell to a string using the following code viz.
Sub find()
Dim rs As Worksheet
Dim rgFound As Range
Dim EmpNo As String
Set rgFound = Range("A1:AU57").find("Empl. No. :")
EmpNo = Cells(rgFound.Row, rgFound.Column + 3)
rs.Name = EmpNo
End Sub
However the rs.Name = EmpNo gives me an error Run time error '91':
Object variable or with block variable not set
Any help will be appreciated
I'm pretty new to VBA and need some assistance to rename a sheet based on
1. finding a value in the sheet
2. Offsetting 3 cells to the right of the found cell
3. using the value of the offset cell to rename the sheet
Thus far i've been able to find the cell & allocate the value of the offset cell to a string using the following code viz.
Sub find()
Dim rs As Worksheet
Dim rgFound As Range
Dim EmpNo As String
Set rgFound = Range("A1:AU57").find("Empl. No. :")
EmpNo = Cells(rgFound.Row, rgFound.Column + 3)
rs.Name = EmpNo
End Sub
However the rs.Name = EmpNo gives me an error Run time error '91':
Object variable or with block variable not set
Any help will be appreciated