0

The program I am working on has to deal with data, with multiple duplicate column names, so I want to look for a field name "port type' and perform some checks, if not satisfied then move to the next duplicate column. In order to accomplish it, I tried using FindNext function.

'Application.ScreenUpdating = False
duplicateBook.Sheets(1).Activate
duplicateBook.Sheets(1).Select

Dim lr As Integer
Application.ScreenUpdating = False
ActiveSheet.AutoFilterMode = False

Rows("9:9").Select
Rows("9:9").Find("Charge Type", LookIn:=xlValues, lookat:=xlWhole).Select
chargetypefield = Selection.Column
Rows("9:9").Find("Product", LookIn:=xlValues, lookat:=xlWhole).Select
ProductField = Selection.Column
Rows("9:9").Find("Feature", LookIn:=xlValues, lookat:=xlWhole).Select
Featurefield = Selection.Column
Rows("9:9").Find("Service Id(CP Only)", LookIn:=xlValues, lookat:=xlWhole).Select
serviceidField = Selection.Column
Rows("9:9").Find("CIRCUIT ID", LookIn:=xlValues, lookat:=xlWhole).Select
CircuitIDField = Selection.Column
Rows("9:9").Find("Ignored", LookIn:=xlValues, lookat:=xlWhole).Select
ignoredField = Selection.Column
Rows("9:9").Find("Country", LookIn:=xlValues, lookat:=xlWhole).Select
countryField = Selection.Column

'################
'On Error Resume Next
With duplicateBook.Sheets(1).Rows("9:9")
   Set selectCell = .Find("Port Type", LookIn:=xlValues, lookat:=xlWhole)
    firstVal = selectCell
    

    Do
        If selectCell.End(xlDown).Value = "Ethernet" Or selectCell.End(xlDown).Value = "TDM" Or selectCell.End(xlDown).Value = "MVIC" Then
            portypeField = Selection.Column
            Exit Do
        Else
            **Set selectCell = .FindNext("Port Type")**
        End If
    Loop While selectCell.Column <> firstVal
End With
Prashant R Joga
  • 1
  • 1
  • 3
    You might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). – Pᴇʜ Sep 16 '20 at 11:22

1 Answers1

0

As PEH commented, avoid using SELECT. You get that when recording a macro, but it can lead to numerous problems.

Then, unless all the columns of the sheet have data, I'd recommend defining the intersection of row 9 and the sheet's UsedRange (e.g., Intersect({sheet}.Rows(9),{sheet}.UsedRange) or {sheet}.UsedRange.Resize(1).Offset(8) (of course, {sheet} is however you prefer to specify the applicable worksheet object).

Next you can iterate that limited row with For Each or use the Find method, but either way, you'll need to test whether your resulting range "Is Nothing" before acting on it (preferably, again, not by selecting it) to get the column number and/or find the last value in that column (e.g., use the Find method only on the column to look for your keys, "Ethernet" or "TDM")

pdtcaskey
  • 242
  • 1
  • 9