0

I am using this code to select an range by reference for copy and paste. It shows error in the Consultant3.Select line.

I am unable to fix this code. 

Sub SelectMyRange()

Dim Consultant1 As Integer, Consultant2 As Integer
Dim Consultant3 As Range
Dim rngFind As Range    

Set rngFind = Columns("A:A").Find(What:="OBJ NO.", After:=Range("A1"), LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext)

If Not rngFind Is Nothing Then
    Consultant1 = rngFind.Row + 2
End If

Set rngFind = Columns("A:A").Find(What:="OBJ END", After:=Range("A1"), LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext)

If Not rngFind Is Nothing Then
    Consultant2 = rngFind.Row - 1
End If

If Consultant1 > 0 And Consultant2 > 0 Then
    Set Consultant3 = Range(Cells(Consultant1, 1), Cells(Consultant2, 1))
End If

Consultant3.Select
Selection.Copy

End Sub
Community
  • 1
  • 1
Siraj
  • 157
  • 1
  • 5
  • 16
  • 1
    add `Debug.Print Consultant1` and `Debug.Print Consultant2` , and also `Debug.Print Consultant3.Address` before this line, what values are getting in the immediate window ? – Shai Rado May 09 '17 at 07:01
  • 1
    Side note: Don't use `Integer` use `Long` instead. Excel has more rows than integer can handle. Read [why here](http://stackoverflow.com/a/26409520/3219613). – Pᴇʜ May 09 '17 at 07:10
  • 1
    What's the error description? I just tested your code and it executed without error given that both strings were found in A:A. If they are not, why is your Select outside the If where the reference is set? – jivko May 09 '17 at 07:29
  • What I realized is, I hide both the reference cell in column A which has "OBJ NO." AND "OBJ END". It gives error as "Object variable or With block variable not set". – Siraj May 09 '17 at 09:31
  • I need to hide them as I don't want users to change the reference cell data. Is it possible to refere to a hidden cell data and the macro works. – Siraj May 09 '17 at 09:32

0 Answers0