Row number returned not correct in VBA for Excel

I have a worksheet that has seven ranges in column F.  I need to search for a value in a particular range as selected by the user.  That is, if user selects Active Ports for value WAL, then in the range cc_ActivePorts, (which is one dimenion, F25:F70), I need to have the row number returned.  However, say the value is located in F64, but the code returns 122 as the row number indicated in the statement, intRowNum = .Range(strAddress).Row.  I know there is something about working with ranges and the column/rows are reset, but when I try to account for this, I still get incorrect information returned.  I just need to strip out the row number from the strAddress.

All this is done in VBA
ThisWorkbook.Worksheets("Settings").Activate
With Worksheets("Settings").Range(strFindRange)
    Set strRange = .Find(strPortCode, LookIn:=xlValues)
    strAddress = strRange.Address
    intRowNum = .Range(strAddress).Row  RETURNS WRONG ROW NUMBER!

    Me.txtPortfolioCode.Value = Range(strAddress)
    Me.txtPortfolioName.Value = Range(strAddress).Offset(0, 1)
    Me.txtURLSectorSummary.Value = Range(strAddress).Offset(0, 2)
    Me.txtURLSectorDetail.Value = Range(strAddress).Offset(0, 3)
    Me.txtURLRatingSummary.Value = Range(strAddress).Offset(0, 4)
    Me.txtURLRatingDetail.Value = Range(strAddress).Offset(0, 5)
    Me.txtAddedBy.Value = Range(strAddress).Offset(0, -5)
    Me.txtAddDate.Value = Range(strAddress).Offset(0, -4)
    Me.txtClosedChangedBy.Value = Range(strAddress).Offset(0, -2)
    Me.txtCloseChangeDate.Value = Range(strAddress).Offset(0, -1)
    Me.txtRowNumber.Value = intRowNum End With

Open in new window

ssmith94015Asked:
Who is Participating?
 
SiddharthRoutConnect With a Mentor Commented:
Also I feel, I could be wrong but you are getting an incorrect row number because you are referencing a wrong range.

try this

intRowNum = Worksheets("Settings").Range(strAddress).Row

Sid
0
 
zorvek (Kevin Jones)ConsultantCommented:
Run this code and post the results from the Immediate window:

ThisWorkbook.Worksheets("Settings").Activate
With Worksheets("Settings").Range(strFindRange)
    Debug.Print strAddress
    Set strRange = .Find(strPortCode, LookIn:=xlValues)
    strAddress = strRange.Address
    Debug.Print strAddress
    intRowNum = .Range(strAddress).Row  RETURNS WRONG ROW NUMBER!

Kevin
0
 
ssmith94015Author Commented:
$F$64   - Which is correct, but when I hover over intRowNum, it still equals 122.
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
zorvek (Kevin Jones)Connect With a Mentor ConsultantCommented:
Try this:

ThisWorkbook.Worksheets("Settings").Activate
With Worksheets("Settings")
    Set strRange = .Range(strFindRange).Find(strPortCode, LookIn:=xlValues)
    strAddress = strRange.Address
    intRowNum = .Range(strAddress).Row

Kevin
0
 
ssmith94015Author Commented:
Sid, as far as I can tell, it is referring to the correct range.  I checks the Select statement that validates which entry in the drop-down the user select, made sure that it referrs to the correct range to pass as the parameter for strFindRange, which it is.  I also checked the range definition to be sure it is covering the correct range, which is F59 to F64 for this range name.
0
 
SiddharthRoutCommented:
Look at kevin's code ;)

You will understand what I mean.

Sid
0
 
SiddharthRoutCommented:
In your actual code

intRowNum is equal to

.Range(strAddress).Row

in

Worksheets("Settings").Range(strFindRange)

It should have been in

Worksheets("Settings")

Sid
0
 
ssmith94015Author Commented:
Sid, sorry, I did no realize what you were asking and both solutions worked.  I get caught in that range problem every time.
0
 
ssmith94015Author Commented:
Sid and Kevin, as a follow-up, I just want to let you know this answer has saved me over and over again on this project.  I keep coming back to this answer as I repeatedly run into the same issue.  You have no idea of the potential headaches you both have saved me from.  I wish you both to know how very much this is appreciated.
0
 
SiddharthRoutCommented:
ssmith94015: You are welcome :)

Do let us know if you face any glitch :)

Sid
0
 
ssmith94015Author Commented:
Sid, I am having the same problem again.  I thought this was solved, buy I have run into is again and I just can't seem to get this solved once and for all.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.