Solved

Row number returned not correct in VBA for Excel

Posted on 2011-03-03
11
368 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Excel Formula 5 45
locking multiple column ranges 10 25
Excel Charts: How is this Chart made? 8 29
Excel Formula to check both condition's and return values 2 28
A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Having trouble getting your hands on Dynamics 365 Field Service or Project Service trial? Worry No More!!!
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

778 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