0

I am working on trying to find an item in an excel sheet and pasting information in an adjacent column. I need to strip the number in the first excel sheet of its hypens and then find it in the other document. I have had no issues with this, thus far, however when I try to select the second row I am getting a compilation error, saying line number or label was expected. 

Sub info()

Dim dehyp As Long
Dim rng As Range

dehyp = Replace(Range("A5").Value, "-", "")
Sheets("Gov").Select

Set rng = Sheets("Gov").Columns(1).Find(What:=dehyp, LookIn:=xlFormulas, 
LookAt:=xlWhole, SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)

    Select
    Selection.Copy
    Sheets("Total usage").Select
    Range("B5").Select
    ActiveSheet.Paste
End sub
Meliodus123
  • 45
  • 8
  • 6
    Note: `Select`ing is not needed. See https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba – BigBen Oct 21 '19 at 17:26
  • So would I tried Set num = Sheets("Gov").Range(rng, 2) num.Copy, but I am getting a type mismatch now. – Meliodus123 Oct 21 '19 at 17:47
  • 1
    `Select` by itself is indeed a compilation error. Probably something like this will work - `rng.Offset(1).Select`, in order to select the next row? And in general, consider avoiding `Select` and `Activate`, as noted in the first comment. – Vityata Oct 21 '19 at 17:57
  • 2
    That line makes no sense. You never declare `num` as anything. And the `range.copy` method doesn't return an object that you can set to something else. Perhaps `rng.Offset(1,0).copy Sheets("Total usage").Range("B5")` – Ron Rosenfeld Oct 21 '19 at 17:59

0 Answers0