Solved

Row number returned not correct in VBA for Excel

Posted on 2011-03-03
11
366 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 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
 
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
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

The new Microsoft OS looks great, is easier than ever to upgrade to, it is even free.  So what's the catch?  If you don't change the privacy settings, Microsoft will, in accordance with the (EULA) you clicked okay to without reading, collect all the…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

708 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now