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
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