Help to check report's name Quaterly, Yearly. If match report name vill clear column

[sbv1986]

Hi all

I have workbook with data in sheet(result)
sheet(result).range(F1) have value like: yyyymm=> 201909, 201908 ....
Now I want macro to check that:
1. Get 02 character from right(range(F1)) = b
2. If b <> 12 then check each cell(1,j).value from range(G1, last column)
- If cell(1,j).value = A001 or B002 or C003 then Clear column(j) from row 2 to last row
- Check if b <> 09 or b <> 06 or b <> 03 then check again each cell(i,j).value from range(G1, last column)
If cell(i,j).value = G01 or G02 or G03 then Clear column(j) from row 2 to last row

For Examble clear my idea:

b = right(sheets(result).range(F1),2)
if b = 12 then do nothing' This's report of year

if b = 09 then 'This is repoert of Quater
check each cell(i,j).value from range(G1, last column) 
=> if Cells(1,8).value = "A001" then range(H2:H).clear
=> if Cells(1,9).value = "C003" then range(I2:I).clear
.....

if b =08 then ' This is report of month
check each cell(i,j).value from range(G1, last column) 
=> if Cells(1,8).value = "A001" then range(H2:H).clear
=> if Cells(1,9).value = "C003" then range(I2:I).clear
=> if Cells(1,11).value = "G01" then range(K2:K).clear
=> if Cells(1,15).value = "G03" then range(O2:O).clear

 

[Rijnsent]

Hi SBV,
that is some nice pseudo-code. Some hints before I dive into code: start with the macro recorder to get some real code and teach yourself a bit more with e.g. https://www.excel-pratique.com/en/vba.php or https://www.homeandlearn.org/ .
Secondly: There is a difference between sheets(result) and sheets("result"). The first assumes you have somewhere above in your code a variable named result with a text/string in it, the second simply looks for a worksheet named result. So to improve your pseudo code a bit:

Sub ClearItAll()

sheet_name = "Blad1"
Set s = Worksheets(sheet_name)
first_column = s.Range("G1").Column
last_column = 16
first_row = 2
last_row = 100

b = Right(s.Range("F1").Value, 2)

If b <> 12 Then
    If b Mod 3 = 0 Then
        'quarters
        For col = first_col To last_col
            If s.Cells(1, col).Value = "A001" Or s.Cells(1, col).Value = "A001" Or s.Cells(1, col).Value = "C003" Then
                s.Range(s.Cells(start_row, col), s.Cells(end_row, col)).ClearContents
            End If
        Next col
    Else
        'months
        'do something here
    End If
End If

End Sub

Hope that gets you moving, please do post more code if you get stuck.
Cheers,
Koen

 

[sbv1986]

@Rijnsent
Thanks for your respond but code don't work as I want. I'll try another way