Not Hard Code If

  • Thread starter Thread starter FIRSTROUNDKO via OfficeKB.com
  • Start date Start date
F

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
 
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
Click to expand...
 
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
 
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
 
Back
Top