Solved: searching a word in lower and uppercase

[Ger]

I've build (with asome help) a macro that replaces the values in a table (row 5 to 84) with the values of row 205 to 284.
All values must be updated except if the value in row 5 to 84 is "RES" or "res" or "Res" and the value in row 205 to 284 is a number.
At this time only "RES" is not replaced. Res and res are still replaced.
How can i make vba understand that "Res" and "res" also may not be changed if there is a number.

code:
[vba]
Sub Verwerken()
Range("START").Activate
startregel = ActiveCell.Row
eindregel = Range("eind").Row
aantalRegels = eindregel - startregel
StartKolom = ActiveCell.Column
eindkolom = StartKolom + Range("start").Offset(0, -1)
AantalKolommen = Range("start").Offset(0, -1)
Application.ScreenUpdating = False
For r = 1 To aantalRegels
For i = 1 To AantalKolommen
If ActiveCell.Offset(-200, 0) = "RES" And ActiveCell <> "RES" And Not IsNumeric(ActiveCell) Then
ActiveCell.Offset(-200, 0) = ActiveCell
ElseIf ActiveCell.Offset(-200, 0) <> "RES" Then ActiveCell.Offset(-200, 0) = ActiveCell
End If
ActiveCell.Offset(0, 1).Activate
Next i
Range("start").Offset(r, 0).Activate
Next r
Application.ScreenUpdating = True
Rows("205:312").Hidden = True
End Sub
[/vba]


Who can help.

Thx

Ger

[mikerickson]

Please remember to use code tags.
Please edit your post accordingly.

Thank you.

[Ger]

Sorry,

changed it.

Ger

[mohanvijay]

Try this

Dim i As Long, r_s As Long, r_e As Long, r_dif As Long
r_s = 5
r_e = 84
r_dif = 200
For i = r_s To r_e
    If UCase(Cells(i, 1).Value) <> "RES" And IsNumeric(Cells(i + r_dif, 1).Value) = False Then
        Cells(i, 1).Value = Cells(i + r_dif, 1).Value
    End If
Next i

[Ger]

This doesn't work. "RES" is now replaced if there is a number.

If the value of a cell (in the range I5:GH84) is "RES"/"res"/"ReS" etc and the value in the "input" cell (range I205:GH284) is a number the value "RES" may not be replaced. All other replacements are allowed.

Ger

[GTO]

Hi Ger,

I might not be following, but have you tried something like:

[VBA]Sub exa()
Dim Cell As Range

For Each Cell In Range("I5:GH84")
If UCase(Cell.Value) = "RES" _
And IsNumeric(Cell.Offset(200).Value) _
And Not Cell.Offset(200).Value = vbNullString Then
'do nothing
Else
Cell.Value = Cell.Offset(200).Value
End If
Next
End Sub[/VBA]

Hope that helps,

Mark

[Ger]

Thx,

this works perfect.

Ger

[GTO]

Glad that worked .

[Aflatoon]

One could also do it without looping:

   Dim rngData1 As Range, rngData2 As Range
   Set rngData1 = Range("A5:G84")
   Set rngData2 = Range("A205:G284")
   rngdata1.value = Evaluate("INDEX(IF(" & rngData1.Address & "=""RES"",IF(ISNUMBER(" & rngData2.Address & _
            ")," & rngData1.Address & "," & rngData2.Address & ")," & rngData2.Address & "), 0, 0)")

[Ger]

Thx Aflatoon, but the solution of GTO i understand. So it is easier for me to make changes.

[Aflatoon]

Not a problem - it was just for information.