Set range to every 7th cell

[Justmegan93]

I currently used this code attached to a command button to search and find employees in my company by their surname
although the row now includes both names and other details, is there a way I can still only search for the surname?

The surname is in every 7th cell along (starting at E1... L1... S1 ect)
Would there be any way to set the range for every 7th cell in the row ?

Sub Find_Name()
Dim intColumn As Integer
Dim strBox As String
Dim rFind As Range
Dim intRow As Integer
intRow = ActiveCell.Row

strBox = InputBox("Enter the name you want to search for.")

If strBox = "" Then Exit Sub

Set rFind = Rows("1:1").Find(What:=strBox, LookIn:=xlValues, LookAt:=xlPart)

If Not rFind Is Nothing Then
    rFind.Select
    intColumn = ActiveCell.Column
Cells(intRow, intColumn).Select
Else
    MsgBox ("That name could not be found")
End If

End Sub

[Arkadi]

Hi justmegan,

I believe the answer is yes, but would it be possible to see the sheet to understand a little better? You want the range to be every 7th cell in the row, but your range is the range where the name matched... so if you just select every 7th cell, does that not mean you are selecting all surnames?

[Speshul]

Every 7th cell, I would just use a for...next with a step of 7

for i = 1 to NUM step 7    '[NUM = total number of populated columns]
'and offset to find that 7th column
rfind = Range("a1").Offset(0, i)
'code
next i

Or something along those lines

[Justmegan93]

Private Sub FindName_Click()
Dim intColumn As Integer
Dim strBox As String
Dim rFind As Range
Dim intRow, i As Integer
intRow = ActiveCell.Row

strBox = InputBox("Enter the name you want to search for.")

If strBox = "" Then Exit Sub

For i = 1 To 900 Step 7
Set rFind = Range("d2").Offset(0, i)
 Set rFind = rFind.Find(What:=strBox, LookIn:=xlValues, LookAt:=xlPart)
If Not rFind Is Nothing Then
    rFind.Select
    intColumn = ActiveCell.Column
Cells(intRow, intColumn).Select
End If
Next i
End Sub

Thank you Speshul
Got it to work now!