How to get row number on condition of case in vba?

Question

I am having case like below, I need row number of particular condition.

    Dim sve_rc as Integer
    sve_rc = 1 ' to get Rowcount 
    With Worksheets("Mktg.Effort")
    For lrow = 1 To .Cells(.Rows.Count, "A").End(xlUp).Row
    Select Case CStr(Cells(lrow, "A").Value)
           Case "engg"
           ' do somwthing

           Case "SvE"  
           sve_aug = Cells(lrow, "AF").Value
           sve_rc = ActiveCell.Row  ' here I am getting rows from 1st colum I need only rows where case is "SvE"
           sum = sum + Range("AF" & sve_rc)

           Case "GMO"
            ' do something
    End Select
    Next lrow
    End With

My Excel is as below,

       engg 
       SvE 
       SvE 
       GMO 
       engg 
       GMO

So if I check for condition SvE I must get row number as 2, but I am getting it from 1. Unable to build logic here... any help please.

You are testing `Cells(lrow, "A").Value`. Why are you then using `ActiveCell.Row`? `ActiveCell` is not going to be `Cells(lrow, "A")`, and you [don't need `ActiveCell`](http://stackoverflow.com/q/10714251/11683) to begin with. I would advise to use `Cells(lrow, "A").Row` if it wasn't that this will return `lrow`. So your answer is, the row number is in `lrow`. — GSerg, Sep 19 '16 at 07:19
@GSerg I am I wrong... I am just trying to get matching row of that particular case column... Plz help me — Pink Pink, Sep 19 '16 at 07:20
It would also appear you in fact want to use [SUMIF](https://support.office.com/en-us/article/SUMIF-function-169b8c99-c05c-4483-a712-1697a653039b). — GSerg, Sep 19 '16 at 07:26

Zac · Accepted Answer · 2016-09-19T07:30:18.367

-2

Activate your row with: Worksheets("Mktg.Effort").Rows(lrow).Activate. Now you can use ActiveCell.Row

edited Sep 19 '16 at 07:30

answered Sep 19 '16 at 07:22

Zac

1,924
1
8
21

this is giving me result as string. I need row number of this matching column – Pink Pink Sep 19 '16 at 07:28
That is a [very bad advice](http://stackoverflow.com/q/10714251/11683). – GSerg Sep 19 '16 at 07:31
I agree. If I knew what OP was attempting to do, I would suggest an alternate approach as I don't believe that OP's approach is correct. I mostly use object based approach but here all OP wants (without providing any details of the end goal) is the row number for specified value. As primitive as my answer is, it will get OP what they want – Zac Sep 19 '16 at 07:39

How to get row number on condition of case in vba?

1 Answers1