Error when running a VBA code

Question

I am trying to run this code, but I am getting a 400 error on the line "Holdc.Offset(0, 3).Select " and I cant figure out why. My code is in a different workbook and is supposed to open another workbook and sheet to find a matching line and then change field. If I run it from the sheet that it is supposed to be changing it works, but if I run it from the workbook that it is actually in I get an error. I thought I set all the paths correct, and the Holdc variable is pulling the correct information. However when I try to select Holdc, which is a range in a different workbook I am getting an error.

Dim HoldDealer As String

Dim HoldItem As String

Dim HoldQty As Integer

Dim HoldWB As Workbook

Dim HoldWS As Worksheet

Dim HoldLoopcount As Long

Dim HoldWhere As Range

Dim Holdc As Range

Dim HoldHere As Range

Dim HoldFirstmatch As String

Dim HoldfoundInRow As Long

Dim HoldDelRange As Range

Dim HoldBO As String

HoldDealer = Range("D5").Value

For HoldLoopcount = 9 To 34

HoldfoundInRow = 0

HoldItem = Range("D" & HoldLoopcount).Value

HoldQty = Range("B" & HoldLoopcount).Value

HoldBO = Range("A" & HoldLoopcount).Value

        Set HoldWB = Workbooks.Open("\\TIMS-COMPUTER\Users\furnd\POS System\Stock File.xlsm") 

        Set HoldWS = HoldWB.Worksheets("Hold\_List") 

        Set HoldWhere = HoldWS.Range("B:B") 

        Set Holdc = HoldWhere.Find(HoldDealer, LookIn:=xlValues, LookAt:=xlWhole) 

        If HoldBO = "BO" Then 

            GoTo ExitPoint 

            End If 

        If Holdc Is Nothing Then 

            GoTo ExitPoint21 

            End If 

        HoldFirstmatch = Holdc.Address 

        Do While Not Holdc Is Nothing And HoldfoundInRow = 0 

            If Holdc.Offset(0, 5).Value = HoldItem Then 

                HoldfoundInRow = Holdc.Row 

                Debug.Print (Holdc.Row) 

                Debug.Print (Holdc.Address) 

                Holdc.Offset(0, 3).Select 

                Set HoldHere = ActiveCell 

                If HoldHere - HoldQty < 0 Then 

                    Set HoldDelRange = HoldWB.Sheets("Hold\_List").Rows(ActiveCell.Row) 

                    HoldQty = HoldQty - HoldHere 

                    HoldDelRange.Delete 

                    Set Holdc = HoldWhere.Find(HoldDealer, LookIn:=xlValues, LookAt:=xlWhole) 

                    HoldfoundInRow = 0 

                    GoTo ExitPoint12 

                ElseIf HoldHere - HoldQty = 0 Then 

                    Set HoldDelRange = HoldWB.Sheets("Hold\_List").Rows(ActiveCell.Row) 

                    HoldDelRange.Delete 

                    GoTo ExitPoint13 

                Else 

                HoldHere = HoldHere - HoldQty 

                End If 

            End If 

            Set Holdc = HoldWhere.FindNext(Holdc) 

            Debug.Print Holdc.Address, Holdc.Text, HoldItem 

            If HoldFirstmatch = Holdc.Address Then Exit Do 

ExitPoint12:

        Loop 

ExitPoint13:

Next HoldLoopcount

ExitPoint21:

MsgBox "Complete"

HoldWB.Close SaveChanges:=True

Answer 1

Actually, it's better not to select cells. You can replace

        Holdc.Offset(0, 3).Select

        Set HoldHere = ActiveCell

with

        Set HoldHere = Holdc.Offset(0, 3)

Answer 2

Thank you for that information. I didn't realize that select only worked on the active sheet. If I need to pull more information from the original sheet will I need to select it again after I make the changes on the second sheet?

Answer 3

Select only works on the active sheet.

Option 1:

HoldWS.Select

Holdc.Offset(0, 3).Select

Option 2:

Application.GoTo Holdc.Offset(0, 3)