• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA

sauda

New Member
Hi,


I have data in column 'H' where I want to add string "No cases" after number. Condition is that after record number 6, I want to delete the cell (not complete row )where equal to zero (i.e.mark=0) is present.


Problem is that, when I run the macro, data from column A-F are also affected. How to restrict it in column H only and delete cells with equal to (=0) from 7th records onwards


Boys mark Physics=50

Boys mark non-Physics=1

Boys games in Physics=11

Boys games in non-Physics=No Cases

Girls mark Physics=2

Girls mark non-Physics=27

Girls/non-games/Physics/Boys/in=1088

mark=0

ames in non-Physics=0


Sub PROB()

LR = Range("H" & Rows.Count).End(xlUp).Row

Set Rng = Range(Cells(1, 1), Cells(LR, 1))

For i = Rng.Count To 1 Step -1


Pos = InStrRev(Cells(i, 1), "=")

If IsNumeric(Right(Cells(i, 1), Len(Cells(i, 1)) - Pos)) And Right(Cells(i, 1), Len(Cells(i, 1)) - Pos) <> 0 Then

If Right(Cells(i, 1), Len(Cells(i, 1)) - Pos) > 1 Then

Cells(i, 1).Value = Cells(i, 1).Value & " Cases"

ElseIf Right(Cells(i, 1), Len(Cells(i, 1)) - Pos) = 1 Then

Cells(i, 1).Value = Cells(i, 1).Value & " Case"

End If

Else

Cells(i, 1).EntireRow.Delete

End If


Next i

End Sub
 
Hi ,


Replace the statement :


Cells(i, 1).EntireRow.Delete


in your code , by the following statement :


Cells(i, "H").Delete


I am assuming that your code is already taking care to check for the greater than 0 condition.


Narayan
 
The macro removes cases where equal to zero(=0) is there along with string "No Cases".

Can we restrict that it works where value (=0) is there but skip records with string "No Cases".
 
Hi Ashok ,


Try out this :

[pre]
Code:
Sub PROB()
LR = Range("H" & Rows.Count).End(xlUp).Row
Set Rng = Range(Cells(1, 1), Cells(LR, 1))
For i = Rng.Count To 1 Step -1
Pos = InStrRev(Cells(i, 1), "=")
If IsNumeric(Right(Cells(i, 1), Len(Cells(i, 1)) - Pos)) Then
If Right(Cells(i, 1), Len(Cells(i, 1)) - Pos) <> 0 Then
If Right(Cells(i, 1), Len(Cells(i, 1)) - Pos) > 1 Then
Cells(i, 1).Value = Cells(i, 1).Value & " Cases"
ElseIf Right(Cells(i, 1), Len(Cells(i, 1)) - Pos) = 1 Then
Cells(i, 1).Value = Cells(i, 1).Value & " Case"
End If
Else
Cells(i, "H").Delete
End If
End If
Next i
End Sub
[/pre]
Narayan
 
thanks narayan, its working very fine. But the string 'Cases' has not been added to the numeric data. Output should be like this.

Could you plz spend some time. Your help will be highly appreciated


Boys mark Physics=50 Cases

Boys mark non-Physics=1 Case

Boys games in Physics=11 Cases

Boys games in non-Physics=No Cases

Girls mark Physics=2 Cases

Girls mark non-Physics=27 Cases

Girls/non-games/Physics/Boys/in=1088 Cases
 
Hi Ashok ,


Try this :

[pre]
Code:
Sub PROB()
Const COLUMN_TO_BE_CHECKED = "H"
LR = Range(COLUMN_TO_BE_CHECKED & Rows.Count).End(xlUp).Row
Set Rng = Range(Cells(1, COLUMN_TO_BE_CHECKED), Cells(LR, COLUMN_TO_BE_CHECKED))
For i = Rng.Count To 1 Step -1
Pos = InStrRev(Cells(i, COLUMN_TO_BE_CHECKED), "=")
If IsNumeric(Right(Cells(i, COLUMN_TO_BE_CHECKED), Len(Cells(i, COLUMN_TO_BE_CHECKED)) - Pos)) Then
If Right(Cells(i, COLUMN_TO_BE_CHECKED), Len(Cells(i, COLUMN_TO_BE_CHECKED)) - Pos) <> 0 Then
If Right(Cells(i, COLUMN_TO_BE_CHECKED), Len(Cells(i, COLUMN_TO_BE_CHECKED)) - Pos) > 1 Then
Cells(i, COLUMN_TO_BE_CHECKED).Value = Cells(i, COLUMN_TO_BE_CHECKED).Value & " Cases"
ElseIf Right(Cells(i, COLUMN_TO_BE_CHECKED), Len(Cells(i, COLUMN_TO_BE_CHECKED)) - Pos) = 1 Then
Cells(i, COLUMN_TO_BE_CHECKED).Value = Cells(i, COLUMN_TO_BE_CHECKED).Value & " Case"
End If
Else
Cells(i, COLUMN_TO_BE_CHECKED).Delete
End If
End If
Next i
End Sub
[/pre]
Narayan
 
Back
Top