PDA

View Full Version : Solved: searching a word in lower and uppercase



Ger
07-12-2011, 11:30 PM
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:

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



Who can help.

Thx

Ger

mikerickson
07-12-2011, 11:44 PM
Please remember to use code tags.
Please edit your post accordingly.

Thank you.

Ger
07-13-2011, 12:08 AM
Sorry,

changed it.

Ger

mohanvijay
07-13-2011, 12:30 AM
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
07-13-2011, 01:14 AM
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
07-13-2011, 01:41 AM
Hi Ger,

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

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

Hope that helps,

Mark

Ger
07-13-2011, 01:53 AM
Thx,

this works perfect.

Ger

GTO
07-13-2011, 01:59 AM
Glad that worked:beerchug: .

Aflatoon
07-13-2011, 02:00 AM
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
07-13-2011, 02:26 AM
Thx Aflatoon, but the solution of GTO i understand. So it is easier for me to make changes.

:thumb :jsmile:

Aflatoon
07-13-2011, 02:37 AM
Not a problem - it was just for information. :)