?
Solved

Row number returned not correct in VBA for Excel

Posted on 2011-03-03
11
Medium Priority
?
404 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
  • 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 1000 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
Industry Leaders: 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!

 
LVL 81

Assisted Solution

by:zorvek (Kevin Jones)
zorvek (Kevin Jones) earned 1000 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

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
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.

621 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