Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 324
  • Last Modified:

VBA Replace &Total

Hello,
can you please help,
I need a vba that can compare Columns (AB, AE, AH, AK, AN) against CELL --- AR1
Example
AB3 is not = AR1 --- Then Empty cells AB3, AC3, AD3
AB4 is not = AR1 --- Then Empty cells AB4, AC4, AD4
and so on

AE2 is not = AR1 --- Then Empty cells AE2,AF2,AG2
AE3 is not = AR1 --- Then Empty cells AE3,AF3,AG3
and so on

AH2 is not = AR1 --- Then Empty cells AH2,AI2,AJ2
AH3 is not = AR1 --- Then Empty cells AH3,AI3,AJ3
and so on

AK2 is not = AR1 --- Then Empty cells AK2,AL2,AM2
AK3 is not = AR1 --- Then Empty cells AK3,AL3,AM3
and so on

AN2 is not = AR1 --- Then Empty cells AN2,AO2,AP2
AN3 is not = AR1 --- Then Empty cells AN3,AO3,AP3
and so on

Then a Total of Columns AD, AG, AJ, AM, AP in cell AP (LAST cell +1)
Please see attached sample.

Number of Rows is different.

Any help is appreciated.
thanks
sample.xlsx
0
W.E.B
Asked:
W.E.B
  • 2
1 Solution
 
andrew_manCommented:
Sub TEST()
Dim Ro_1 As Long
Dim C As Integer

Ro_1 = Cells(65000, 1).End(xlUp).Row
C = Range("AR1").Value

For i = 2 To Ro_1

'AB3 is not = AR1 --- Then Empty cells AB3, AC3, AD3
If Range("AB" & i).Value = C Then
 Range("AB" & i).ClearContents
 Range("AC" & i).ClearContents
 Range("AD" & i).ClearContents
End If

'AE2 is not = AR1 --- Then Empty cells AE2,AF2,AG2
If Range("AE" & i).Value = C Then
 Range("AE" & i).ClearContents
 Range("AF" & i).ClearContents
 Range("AG" & i).ClearContents
End If


'AH2 is not = AR1 --- Then Empty cells AH2,AI2,AJ2
If Range("AH" & i).Value = C Then
 Range("AH" & i).ClearContents
 Range("AI" & i).ClearContents
 Range("AJ" & i).ClearContents
End If


'AK2 is not = AR1 --- Then Empty cells AK2,AL2,AM2
If Range("AK" & i).Value = C Then
 Range("AK" & i).ClearContents
 Range("AL" & i).ClearContents
 Range("AM" & i).ClearContents
End If


'AN2 is not = AR1 --- Then Empty cells AN2,AO2,AP2
If Range("AN" & i).Value = C Then
 Range("AN" & i).ClearContents
 Range("AO" & i).ClearContents
 Range("AP" & i).ClearContents
End If


'AD, AG, AJ, AM, AP in cell AP

Range("AP" & (Ro_1 + 2)).FormulaR1C1 = "=SUM(INDIRECT(""$AD$2:$AD$" & Ro_1 & _
                                        """),INDIRECT(""$AG$2:$AG$" & Ro_1 & _
                                        """),INDIRECT(""$AJ$2:$AJ$" & Ro_1 & _
                                        """),INDIRECT(""$AM$2:$AM$" & Ro_1 & _
                                        """),INDIRECT(""$AP$2:$AP$" & Ro_1 & """))"

Next


End Sub
sample.xlsm
0
 
W.E.BAuthor Commented:
Hello,
thanks for your help,

the sample is working,
I just had to change  the = to  <>
If Range("AB" & i).Value <> C Then
If Range("AE" & i).Value <> C Then
If Range("AH" & i).Value <> C Then
If Range("AK" & i).Value <> C Then
If Range("AN" & i).Value <> C Then

when I copy the code to my sheet,  I'm getting an error,
Variable not defined.

I'm using Excel 2007.
thanks
0
 
W.E.BAuthor Commented:
Thanks you,
I added
Dim i As Long
i = 2

thanks
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now