First attempt at VBA in excel

[flect]

I'm having a go at using VBA for excel, having used it a bit in Access.

What I'm trying to do in this exercise is have the code scan column C, and if the cell is empty it needs to remember the value in column A, and then clear cells B & C for all instances of value A


example (small sample)

-- ---- A ---- --B ---- C
1 ---- 210 ---- 20 ---- 40
2 ---- 210 ---- 20 ---- 
3 ---- 210 ---- 30 ---- 
4 ---- 230 ---- 20 ---- 40
5 ---- 230 ---- 32 ---- 40
6 ---- 230 ---- 20 ---- 40
7 ---- 250 ---- 21 ---- 
8 ---- 250 ---- 23 ---- 
9 ---- 250 ---- 54 ---- 40

The end result shoud something like:

 ---- ---A ---- --B ---- C
1 ---- 210 
2 ---- 210 
3 ---- 210 
4 ---- 230 ---- 20 ---- 40
5 ---- 230 ---- 32 ---- 40
6 ---- 230 ---- 20 ---- 40
7 ---- 250 
8 ---- 250 
9 ---- 250

Essentially what I'm trying to do is something like:

If C = null Then
For each where A = A
B & C = null
Next

and I've come up with the following code which has limited success.

Sub CheckSheet()
    Dim intRow
    Dim intLastRow
    Dim TargetVar
    
    intLastRow = Range("C65536").End(xlUp).Row
    For intRow = intLastRow To 1 Step -1
    Rows(intRow).Select
    
        If Cells(intRow, 3).Value = "" Then
            Cells(intRow, 3).Select
            TargetVar = Cells(intRow, 1).Value
        End If

    
    If Cells(intRow, 1).Value = TargetVar Then
            Cells(intRow, 2).Value = ""
            Cells(intRow, 3).Value = ""
    End If

    Next intRow
    Range("c1").Select

End Sub

I think it needs to run again in the opposite direction but i'm unsure how to do it.

Does anyone have any suggestions or perhaps can think of a better way to achieve the result?

 

[lemo]

i can do it with formulas, if you are interested..
here, actually -
in d1 enter =VALUE(A1&C1)
in e1 enter =A1
in f1 enter =IF(ISERROR(VLOOKUP($A1,$D$1:$D$12,1,FALSE)),B1,"")
in g1 enter =IF(ISERROR(VLOOKUP($A1,$D$1:$D$12,1,FALSE)),C1,"")
copy d1:g1 all the way down.
your result is in columns e through g.
l

 

[Brianwarnock]

If you want to use vba this works.

Sub CheckSheet()
    Dim intRow As Long
    Dim introw3 As Long
    Dim intLastRow As Long
    Dim TargetVar As Long
    

    intLastRow = Range("A65536").End(xlUp).Row

    For intRow = 1 To intLastRow Step 1
        
    If Cells(intRow, 3).Value = "" Then
            TargetVar = Cells(intRow, 1).Value
        For intRow2 = 1 To intLastRow Step 1
            If Cells(intRow2, 1).Value = TargetVar Then
                Cells(intRow2, 2).Value = ""
                Cells(intRow2, 3).Value = ""
            End If
        Next intRow2
        
    End If
    Next intRow
       
End Sub

Now do you want me to explain it or do you want to work it out yourself?
The one thig I will say is that as Col C can be null do not use it to find the last row.

As none of your variables are variants do not allow them to default to that as that slows the code, always define them as to what they are.

I always use Long integer rather than integer so i guess I should have altered the names.

Brian