IF Condition for Two Different Scenarios

[madhuchelliah]

Hello Guys, I have two scenarios need to be solve with IF conditions. In my sheet1 I column cells i have a string which consists of alphanumeric and symbols. I am in the process of cleaning up the strings. I cant fix these 2 cases. Please heads up and help.

1st case: If any cell in I column has "A" as second word, the space between 1st and 2nd word should be deleted. Example: 70 A to 70A) Note: Second word is "A" letter alone not words starts with A.

2nd case: If any of the cell in in I column starts with "NAME" the first word below to that cell should be deleted.

I need 2 separate macros for the 2 cases. Thank you.

 

[reberryjr]

In your first scenario, do you have instances where there would be data followed by a space, followed by an A, that you don't want to delete the space?

For your second scenario, you don't actually need VBA. You can use a formula:

[TABLE="class: grid, width: 314"]
<tbody>[TR]
[TD]NAME[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Purple[/TD]
[TD]Delete[/TD]
[TD]=IF(A1="NAME","Delete","")[/TD]
[/TR]
[TR]
[TD]Dog[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cat[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]NAME[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Fish[/TD]
[TD]Delete[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[offthelip]

This code will deal with your first condition, your second requirement is unclear what does:

the first word below to that cell should be deleted.

mean??

code for first requirement

Sub test()

Dim txt As String


lastrow = Cells(Rows.Count, "I").End(xlUp).Row
inarr = Range(Cells(1, 9), Cells(lastrow, 9))
 For i = 1 To lastrow
  txt = inarr(i, 1)
  ' find the first space
  sp = InStr(txt, (" "))
  If sp > 0 Then
  If Mid(txt, sp + 1, 1) = "A" Then
   inarr(i, 1) = Left(txt, sp - 1) & Mid(txt, sp + 1)
  End If
  End If
Next i
Range(Cells(1, 9), Cells(lastrow, 9)) = inarr


   
End Sub

 

[madhuchelliah]

Hello Offthelip, the code is working great. Thank you. Please check the below table for my 2nd case. If any of the cell in I column starts with NAME the first word in the cell below to that should get deleted. Hope you understand. Please help. Thank you.



[TABLE="class: grid, width: 200, align: center"]
<tbody>[TR]
[TD]I
[/TD]
[TD][/TD]
[TD]I
[/TD]
[/TR]
[TR]
[TD]NAME AGE
[/TD]
[TD][/TD]
[TD]NAME AGE
[/TD]
[/TR]
[TR]
[TD]Mr John
[/TD]
[TD][/TD]
[TD]John
[/TD]
[/TR]
[TR]
[TD]Test
[/TD]
[TD][/TD]
[TD]Test
[/TD]
[/TR]
[TR]
[TD]NAME DOB
[/TD]
[TD][/TD]
[TD]NAME DOB
[/TD]
[/TR]
[TR]
[TD]AA Sara
[/TD]
[TD][/TD]
[TD]Sara
[/TD]
[/TR]
[TR]
[TD]Tst
[/TD]
[TD][/TD]
[TD]Tst
[/TD]
[/TR]
[TR]
[TD]NAME MARK
[/TD]
[TD][/TD]
[TD]NAME MARK
[/TD]
[/TR]
[TR]
[TD]Adam Smith
[/TD]
[TD][/TD]
[TD]Smith
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[madhuchelliah]

Hello Reberry, thanks for your formula. Since i am running batch of macros in the process. So i required in macros.

 

[Nishant94]

Check if this is what you want:

Sub madhuchelliah()


Dim lastrow As Integer, i As Integer
Dim MyRng As Range
Dim Rng As Range
Dim ChkVal As Variant
Dim text As String


lastrow = ActiveSheet.Cells(Rows.Count, 9).End(xlUp).Row
Set MyRng = ActiveSheet.Range("I1:I" & lastrow)


For Each Rng In MyRng
    
    ChkVal = Split(Rng.Value, " ")
    If UBound(ChkVal) > 0 Then
        If ChkVal(1) = "A" Then
            text = ChkVal(0) & ChkVal(1)
            Rng.Value = text & Replace(Rng.Value, ChkVal(0) & " " & ChkVal(1), "")
         End If
    End If
        
    If Rng.Row <> 1 Then


        If InStr(LCase(Rng.Offset(-1, 0).Value), "name") <> 0 Then
            Rng.Value = Mid(Rng.Value, InStr(Rng.Value, " ") + 1, Len(Rng.Value))
        End If
    
    End If


Next Rng
   
End Sub

 

[madhuchelliah]

Thanks Nishant. Working great!!!!!!!!!!!!!!!