Solved

Row number returned not correct in VBA for Excel

Posted on 2011-03-03
11
370 Views
Last Modified: 2012-05-11
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

0
Comment
Question by:ssmith94015
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 2
11 Comments
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 35029825
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
 
LVL 30

Accepted Solution

by:
SiddharthRout earned 250 total points
ID: 35029874
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
 

Author Comment

by:ssmith94015
ID: 35029878
$F$64   - Which is correct, but when I hover over intRowNum, it still equals 122.
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 81

Assisted Solution

by:zorvek (Kevin Jones)
zorvek (Kevin Jones) earned 250 total points
ID: 35029909
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
 

Author Comment

by:ssmith94015
ID: 35029919
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
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35029935
Look at kevin's code ;)

You will understand what I mean.

Sid
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35029956
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
 

Author Closing Comment

by:ssmith94015
ID: 35029961
Sid, sorry, I did no realize what you were asking and both solutions worked.  I get caught in that range problem every time.
0
 

Author Comment

by:ssmith94015
ID: 35097162
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
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35097171
ssmith94015: You are welcome :)

Do let us know if you face any glitch :)

Sid
0
 

Author Comment

by:ssmith94015
ID: 35101971
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Having trouble getting your hands on Dynamics 365 Field Service or Project Service trial? Worry No More!!!
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question