Looping and String Variable

[Mourinho]

I use this code to find a match of Val1 in a row of cells, though also have
Val2, 3 ,4 etc also

For j = StrtCol To EndCol
If ActiveCell = Val1 Then
Call Get_Data
Else
ActiveCell.Offset(0, 1).Select
End j

I turned the val variable into a String called Val() though not sure how the
syntax works to look for e.g. Val(1 to 5) in the row of the cells and return
data below it.
The Val is text.

For Each i In Val(i)?

Thanks

 

[Jim Rech]

There are many ways to do what I think you want so here's one that should
get you started. A couple things about your code though. First, Val is a
built-in VB function so you shouldn't use it for a variable's name. Second,
moving the active cell via code is slow and unnecessary.

Sub demo()
Dim ValArray(1 To 5) As String
Dim Cell As Range, MatchIdx As Variant
ValArray(1) = "a"
ValArray(2) = "b"
ValArray(3) = "c"
ValArray(4) = "d"
ValArray(5) = "e"
For Each Cell In Range("A1:E1")
MatchIdx = Application.Match(Cell.Value, ValArray, False)
If Not IsError(MatchIdx) Then
MsgBox Cell.Offset(1).Value
End If
Next
End Sub


--
Jim Rech
Excel MVP
|
| I use this code to find a match of Val1 in a row of cells, though also
have
| Val2, 3 ,4 etc also
|
| For j = StrtCol To EndCol
| If ActiveCell = Val1 Then
| Call Get_Data
| Else
| ActiveCell.Offset(0, 1).Select
| End j
|
| I turned the val variable into a String called Val() though not sure how
the
| syntax works to look for e.g. Val(1 to 5) in the row of the cells and
return
| data below it.
| The Val is text.
|
| For Each i In Val(i)?
|
| Thanks
|
|

 

[Mourinho]

Thanks Jim,

I'm new to this, so new methods are gold dust to me...

I also have to continue the same search in a new Row further down the sheet,
so I would want to include that: For Each row (every x rows), For Each Cell
in Range, For Each Variable (ValArray1,2etc), match and return value below.

Cheers

 

[Tom Ogilvy]

For j = StrtCol To EndCol
for k = 1 to 5
If ActiveCell = Val(k) Then
Call Get_Data
End if
Next k
ActiveCell.Offset(0, 1).Select
Next j

 

[Jim Rech]

Well, at the risk of making this inscrutable<g>, this example assumes you
want to operate on row 1 and every tenth row after that (11, 21, 31, etc,)
to row 101:

Sub demo2()
Dim ValArray(1 To 5) As String
Dim Cell As Range, MatchIdx As Variant
Dim RowOffset As Integer
ValArray(1) = "a": ValArray(2) = "b"
ValArray(3) = "c": ValArray(4) = "d"
ValArray(5) = "e"
For RowOffset = 0 To 90 Step 10
For Each Cell In Range("A1:E1").Offset(RowOffset)
MatchIdx = Application.Match(Cell.Value, ValArray, False)
If Not IsError(MatchIdx) Then
MsgBox Cell.Offset(1).Value
End If
Next
Next
End Sub


--
Jim Rech
Excel MVP
| Thanks Jim,
|
| I'm new to this, so new methods are gold dust to me...
|
| I also have to continue the same search in a new Row further down the
sheet,
| so I would want to include that: For Each row (every x rows), For Each
Cell
| in Range, For Each Variable (ValArray1,2etc), match and return value
below.
|
| Cheers
|
| "Jim Rech" wrote:
|
| > There are many ways to do what I think you want so here's one that
should
| > get you started. A couple things about your code though. First, Val is
a
| > built-in VB function so you shouldn't use it for a variable's name.
Second,
| > moving the active cell via code is slow and unnecessary.
| >
| > Sub demo()
| > Dim ValArray(1 To 5) As String
| > Dim Cell As Range, MatchIdx As Variant
| > ValArray(1) = "a"
| > ValArray(2) = "b"
| > ValArray(3) = "c"
| > ValArray(4) = "d"
| > ValArray(5) = "e"
| > For Each Cell In Range("A1:E1")
| > MatchIdx = Application.Match(Cell.Value, ValArray, False)
| > If Not IsError(MatchIdx) Then
| > MsgBox Cell.Offset(1).Value
| > End If
| > Next
| > End Sub
| >
| >
| > --
| > Jim Rech
| > Excel MVP
| > | > |
| > | I use this code to find a match of Val1 in a row of cells, though also
| > have
| > | Val2, 3 ,4 etc also
| > |
| > | For j = StrtCol To EndCol
| > | If ActiveCell = Val1 Then
| > | Call Get_Data
| > | Else
| > | ActiveCell.Offset(0, 1).Select
| > | End j
| > |
| > | I turned the val variable into a String called Val() though not sure
how
| > the
| > | syntax works to look for e.g. Val(1 to 5) in the row of the cells and
| > return
| > | data below it.
| > | The Val is text.
| > |
| > | For Each i In Val(i)?
| > |
| > | Thanks
| > |
| > |
| >
| >
| >