Not Hard Code If
 

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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200707/1

Not Hard Code If
 

I forgot I had posted the same question ages ago

FIRSTROUNDKO wrote:
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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200707/1

Not Hard Code If
 

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"FIRSTROUNDKO via OfficeKB.com" <u15639@uwe wrote in message
news:74f7d593c282b@uwe...
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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200707/1

Not Hard Code If
 

Try something like this:

Set objRange = Range("A1:A4") 'region that you want to check
Set objRangeVal = Range("D1:D2") '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

--

Rodrigo Ferreira
Regards from Brazil


"FIRSTROUNDKO via OfficeKB.com" <u15639@uwe escreveu na mensagem
news:74f7d593c282b@uwe...
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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200707/1