Input box value to be found in column and entire column selected

[Eurekaonide]

Hi
I'm trying to use an input box to find a week number in row 2 of a worksheet, but it keeps coming back with either mismatch errors or with block errors. My code so far is below which looks like it finds the cell but I need it to then select the entire column and copy and paste it to remove the formulas, ready for the next input.
I set it as a variant because although the majority of weeks are number (i.e 45. on occasions they have to repeat in order to split out the months so I will have a 50a and 50b) and thought Variant would allow numbers and text.

Eventually I'd like this to loop through each visible worksheet in the workbook if possible. Rows will always be row 2.

Sub Test_InputBox()

Dim i As Variant
    i = InputBox("Enter Week Number to find")

    With ActiveSheet.Rows("2:2").Select
     Set Row = Selection.Find(What:=i, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate


ActiveCell.EntireColumn.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
cutcopypaste = False
   End With

If cell Is Nothing Then
MsgBox ("Week is not found")
End If
End Sub

 

[Fluff]

How about

Sub Test_InputBox()
   Dim Fnd As Range
   Dim i As Variant
   
   i = InputBox("Enter Week Number to find")
   If i = "" Then Exit Sub
   
   With ActiveSheet
      Set Fnd = .Rows(2).Find(What:=i, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
         :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
         False, SearchFormat:=False)
      If Fnd Is Nothing Then
         MsgBox "Week not found"
         Exit Sub
      End If
      With Intersect(.UsedRange, Fnd.EntireColumn)
         .Value = .Value
      End With
   End With
End Sub

 

[Eurekaonide]

For Some reason its not selecting the entire column and keeps on saying mismatch again

I may be wrong but I think its not clearing the find value once its completed, if I try another week number afterwards that's when it type mismatches and in the find box its still got the original value I looked for. But its not selecting the column either.

 

[Fluff]

What line gives the error?

 

[Eurekaonide]

This part is what is highlighted

Set Fnd = .Rows(2).Find(What:=i, After:=ActiveCell, LookIn:=xlFormulas, LookAt _ :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False, SearchFormat:=False)

If I'm not anywhere else but Cell A2 when I run the code it runs but only seems to have copied A2 it has the flashing dotting border around it when its finished running. But if I'm in A2 it doesnt give me the type mismatch error i dont think.
It might be that I'm in the correct row (row 2) it type mismatches if i'm in a different row.
So I have just set it to this and that resolves the type mismatch by placing in the correct row first but its still not selecting the entire column and copying and pasting as values.

Sub Test_InputBox()
   Dim Fnd As Range
   Dim i As Variant
  
   i = InputBox("Enter Week Number to find")
   If i = "" Then Exit Sub
  
   Range("A2").Activate
  
   With ActiveSheet
      Set Fnd = .Rows(2).Find(What:=i, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
         :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
         False, SearchFormat:=False)
        
        
      If Fnd Is Nothing Then
        MsgBox "Week not found"
        Exit Sub
      End If
     
     
      With Intersect(.UsedRange, Fnd.EntireColumn)
         .Value = .Value
        
      End With
     
          
      MsgBox ("Done")
     
   End With
  
  
End Sub

 

[Fluff]

Ok change that line to

      Set Fnd = .Rows(2).Find(i, , , xlWhole, , xlNext, False, , False)

 

[Eurekaonide]

OK scrap that its worked :D Yay thank you so much. By adding the activate Cell A2 its corrected it and worked - Thank you so very much

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Eurekaonide]

Are you able to help me to get this to loop through each worksheet in my workbook? I'm really struggling and think it might be the used range needing to be re-set. ?

 

[Fluff]

How about

Sub Test_InputBox()
   Dim Fnd As Range
   Dim i As Variant
   Dim Ws As Worksheet
   
   i = InputBox("Enter Week Number to find")
   If i = "" Then Exit Sub
   
   For Each Ws In ActiveWorkbook.Worksheets
      With Ws
         Set Fnd = .Rows(2).Find(i, , , xlWhole, , xlNext, False, , False)
         If Fnd Is Nothing Then
            MsgBox "Week not found"
         Else
            With Intersect(.UsedRange, Fnd.EntireColumn)
               .Value = .Value
            End With
         End If
      End With
   Next Ws
End Sub