Not Hard Code If

[FIRSTROUNDKO via OfficeKB.com]

Hi,

I would like to change this code

If cells (mydel, 1).text ="car" or cells (mydel, 2).text ="bus" or cells
(mydel, 2).text ="walk" then

into

if cells ( "all cells in the current region", 2) then

where those in the code can be changed before running the macro

i.e the modes of transport start in Sheet1, range A1 and can vary in number

car
bus
walk
....
....

Thanks in advance

 

[FIRSTROUNDKO via OfficeKB.com]

I forgot I had posted the same question ages ago

Hi,

I would like to change this code

If cells (mydel, 1).text ="car" or cells (mydel, 2).text ="bus" or cells
(mydel, 2).text ="walk" then

into

if cells ( "all cells in the current region", 2) then

where those in the code can be changed before running the macro

i.e the modes of transport start in Sheet1, range A1 and can vary in number

car
bus
walk
....
....

Thanks in advance

 

[Don Guillett]

See if this idea helps
Sub currreg()
MyArray = Range(Range("A1"), Cells(Rows.Count, "a").End(xlUp))
For Each c In MyArray
MsgBox c
Next
End Sub

 

[Rodrigo Ferreira]

Try something like this:

Set objRange = Range("A1:A4") 'region that you want to check
Set objRangeVal = Range("D12") 'car, bus, walk, ...

For Each objCell In objRange
For Each objCellval In objRangeVal
If objCell.Value = objCellval.Value Then
Debug.Print objCell.Address & " = " & objCellval.Address
Exit For
End If
Next
Next