'if' statement qn in VB for excel (Runtime error '13')

[buffalo]

Hi the foll code does not run when i include the if statement

Sub f()
Dim CurrentCell, NextCell
Set CurrentCell = Range("Shares")
j = 0
niter = Range("Shares").Count
For i = 1 To niter
Set NextCell = CurrentCell.Offset(1, 0)
'If NextCell.Value = CurrentCell.Value Then
'End If

Set CurrentCell = NextCell
Next i
End Sub

RunTime error 13, type mismatch

Plz help

 

[dk]

Hi,

Is there more than 1 cell in the named range "Shares"? If so then that is why you're getting the error. You can't compare a multi-celled range with a single cell in this way.

 

[buffalo]

I have changed the 'if' part, still it gives the same error

Anyway, I just need a code that should check for each element in a whole column if the element's value is the same as next

buff

Sub f()
Dim CurrentCell, NextCell, NCell
Set CurrentCell = Range("Shares")
j = 0
niter = Range("Shares").Count
For i = 1 To niter
Set NextCell = CurrentCell.Offset(1, 0)
Set NCell = CurrentCell.Offset(0, 0)
If NextCell = NCell Then
End If

Set CurrentCell = NextCell
Next i
End Sub

 

[dk]

OK, this code will loop through each cell in the range "Shares". If it finds a cell that has a value the same as the cell below it then it will tell you. Is this what you need?

Sub f()

    Dim rngShares As Range
    Dim rngEachCell As Range

    Set rngShares = Range("Shares")

    For Each rngEachCell In rngShares.Cells

        If rngEachCell.Value = rngEachCell.Offset(1, 0).Value Then
            MsgBox "Cell " & rngEachCell.Address & " has the same value as " & _
                    "the cell below it."
        End If


    Next rngEachCell

End Sub

 

[buffalo]

Thanks dan! One last (hopefully for today) qn on updating.

THe data on "Shares" is actually updated using the Data-form.

Initially I named the whole column (A1:A506) as Shares by going Insert->Name->Define etc. Now when I add another element to the column at A507, the Count is still 506, this element is NOT a part of Shares.

I think Insert->Name->Define etc... is not the right thing for this purpose. Could you suggest a way to name/label a column so any updated data in the column is automatically a part of "Shares"

I guess I'll have to dig into VB more. Could you suggest a good online / book tutorial for basic VB? (that's 2 questions...)

thanks a bunch,
buff

 

[dk]

Hi,

You can use a dynamic named range for this purpose. Click Insert, Name, Define. Select the Shares name. In the Refers To box type this formula:-

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)

Shares will now be dynamic; if you add extra data the name will automatically update to include it.

This book by John Walkenbach is an excellent Excel VBA book for people of all levels. As for online tutorials I'm not sure.