0

I have been working on this tool inventory system for the shop I work at (machine shop).

Earlier today things were working ok, pretty much got it all running, was just spending some time debugging and cleaning up. Suddenly it's not working. I wasn't working in this area I don't recall but somehow I'm getting a type mismatch. I'm lost on this one.

I get a mismatch type on if activecell.value <> x then.

Sub createOrder_BTN()
  Dim x As String
  Dim found As Boolean
  Dim lRow As Long
  
  Application.ScreenUpdating = False
  
  Worksheets("Orders").Activate
  Worksheets("Orders").Range("A4").Activate
  
  'change to master sheet
  Worksheets("Master").Activate
    
  ' Select first line of data.
  Range("U3").Select
  
  ' Set search variable value.
  x = ""
  
  ' Set Boolean variable "found" to false.
  found = False

  ' Set Do loop to stop at empty cell.
  Do Until IsEmpty(ActiveCell)
    ' change to master sheet
    Worksheets("Master").Activate
        
    ' Check active cell for search value.
    If ActiveCell.Value <> x Then
      found = True
      'MsgBox "Value found in cell " & ActiveCell.Address
      ActiveCell.EntireRow.Copy
      Worksheets("Orders").Activate
      lRow = Cells.Find(What:="", _
              After:=Range("A4"), _
              LookAt:=xlPart, _
              LookIn:=xlFormulas, _
              SearchOrder:=xlByColumns, _
              SearchDirection:=xlPrevious, _
              MatchCase:=False).Row
      ActiveCell.Offset(1).Select
      ActiveCell.PasteSpecial
    End If
         
    ' change to master sheet
    Worksheets("Master").Activate
    
    ' Step down 1 row from present location.
    ActiveCell.Offset(1, 0).Select
  Loop

  Application.ScreenUpdating = True
  
  Worksheets("Orders").Activate
End Sub
GSerg
  • 76,472
  • 17
  • 159
  • 346
Joe Fisher
  • 43
  • 6
  • 2
    Most likely `ActiveCell.Value` is an error value. – BigBen Oct 08 '21 at 17:01
  • 6
    At some point, it would be beneficial to read [How to avoid using Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). – BigBen Oct 08 '21 at 17:02
  • For an example of how you can remove `Activate` and `Select` from your code. Instead of doing `Range("U3").Select` and `ActiveCell.Offset(1).Select` you could do `Dim Target as Range` `Set Target = Range("U3")` and `Set Target = Target.Offset(1)`. This way you can use `Target.Value` instead of `ActiveCell.Value` and the user doesn't have things flickering around on their screen as things get selected and unselected. The code will run much faster too. – Toddleson Oct 08 '21 at 17:45
  • To take it a step farther, you can also avoid switching back and forth between worksheets by saving those as variables too. Which will let you interact with data on those sheets without Activating them. `Dim OrderSheet As Worksheet` `Set OrderSheet = Worksheets( "Orders")` Then you can search inside it like `OrderSheet.Range(...` – Toddleson Oct 08 '21 at 17:51
  • Thank you for comments. i went back a few versions and got past this issue. i will dive into streamlining code soon. im aware there is much to be improved upon. just having fun getting back into coding. its been at least 10 years since i really dove into it. i was probably 15 when i stopped. – Joe Fisher Oct 12 '21 at 16:24

0 Answers0