Solved

Excel VBA pointing to the wrong sheet?

Posted on 2013-11-20
10
1,330 Views
Last Modified: 2013-11-20
I have confirmed that I am looking in the wrong sheet with the MsgBox statements.
The statement "For Each cell In rng" should be looking in the sheet called "DRA Summary", but the MsgBox reveals the data sheet name is "Data". I am in a loop that keeps finding the value "GQ-000" instead of advancing to the next value of "GQ-001". I believe this is because the program is looking in the wrong sheet.
Could someone verify this and tell me what I am doing wrong?
Thanks in advance for your help.
I have attached the workbook as well. The module is "Test_GQModule".

Sub Get_GQnumber1()
    Dim rng As Range, cell As Range
    Dim lc As Long
    Dim s As String, x As String
       
    lc = Cells(3, Columns.Count).End(xlToLeft).Column

    Set rng = Range(Cells(3, 1), Cells(3, lc))
       
    For Each cell In rng
        If cell.Value Like "GQ-*" Then
            MsgBox "Sheet Name: " & ActiveSheet.Name
            MsgBox cell.Value
            s = Left(cell.Value, InStr(1, cell.Value & " ", " ") - 1)

            On Error Resume Next
                       
            x = Application.VLookup(s, Worksheets("Data").Range("A:XFD"), 5, 0)
           
            Sheets("DRA Summary").Range("F2") = x
           
        End If
    Next
End Sub
Data.xlsm
0
Comment
Question by:tesla764
[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
  • 4
  • 3
  • 3
10 Comments
 
LVL 48

Accepted Solution

by:
Martin Liss earned 475 total points
ID: 39662785
Sub Get_GQnumber1()
    Dim rng As Range, cell As Range
    Dim lc As Long
    Dim s As String, x As String
    With Worksheets("DRA Summary")    
    lc = .Cells(3, Columns.Count).End(xlToLeft).Column

    Set rng = .Range(Cells(3, 1), Cells(3, lc))
        
    For Each cell In rng
        If cell.Value Like "GQ-*" Then
            MsgBox "Sheet Name: " & ActiveSheet.Name
            MsgBox cell.Value
            s = Left(cell.Value, InStr(1, cell.Value & " ", " ") - 1)

            On Error Resume Next
                        
            x = Application.VLookup(s, Worksheets("Data").Range("A:XFD"), 5, 0)
            
            .Range("F2") = x
            
        End If
    Next
End With
End Sub

Open in new window

0
 
LVL 4

Expert Comment

by:yuppydu
ID: 39662817
If I understood your issue try this:

in VBA rename Sheet3 as wksDRASummary. This has to be done in the property window.
Then insert With wksDRASummary before your lc = Cells(3, Columns.Count).End(xlToLeft).Column
then close the with command with End With just before End Sub
0
 

Author Comment

by:tesla764
ID: 39662818
On the statement...
Set rng = .Range(Cells(3, 1), Cells(3, lc))

I get the error...
Run-time error '1004':
Application-defined or object-defined error
0
Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 
LVL 4

Assisted Solution

by:yuppydu
yuppydu earned 25 total points
ID: 39662840
remove the "." before Range
0
 
LVL 48

Expert Comment

by:Martin Liss
ID: 39662864
Actually

Set rng = .Range(.Cells(3, 1), .Cells(3, lc))
0
 

Author Comment

by:tesla764
ID: 39662868
Thanks removing the . got rid of the error. This whole thing actually worked but only after I repointed the tab to "DRA Summary". How do I use or ensure the use of the desired sheet even if I don't reset the tab pointer?  I am pretty sure there is a command to do that.
0
 

Author Comment

by:tesla764
ID: 39662907
Thanks everybody. Both suggestions worked. Maybe I should just open a new question for the last comment...

This whole thing actually worked but only after I repointed the tab to "DRA Summary". How do I use or ensure the use of the desired sheet even if I don't reset the tab pointer?  I am pretty sure there is a command to do that.

I will wait a few minutes before I do.
0
 
LVL 4

Expert Comment

by:yuppydu
ID: 39662913
Just rename the sheet as I told you and you will not be influence by tab names
0
 
LVL 48

Expert Comment

by:Martin Liss
ID: 39662915
How do I use or ensure the use of the desired sheet even if I don't reset the tab pointer?
That's what my code does (with the correction). It insures that Excel knows the sheet which the Range and Cells code is pointing to. My use of the With/End With makes
Set rng = .Range(.Cells(3, 1), .Cells(3, lc))

the equivalent of

Set rng = Worksheets("DRA Summary").Range(Worksheets("DRA Summary").Cells(3, 1), Worksheets("DRA Summary").Cells(3, lc))
0
 
LVL 4

Expert Comment

by:yuppydu
ID: 39663034
If you use the tab name as shown in the spreadsheet, if anybody would ever change the sheet name the code will brake. If you rename the sheet from the property panel in VBA and use that name, the code will not be affected by any change at spreadsheet level.
0

Featured Post

 Database Backup and Recovery Best Practices

Join Percona’s, Architect, Manjot Singh as he presents Database Backup and Recovery Best Practices (with a Focus on MySQL) on Thursday, July 27, 2017 at 11:00 am PDT / 2:00 pm EDT (UTC-7). In the case of a failure, do you know how long it will take to restore your database?

Question has a verified solution.

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

Having trouble getting your hands on Dynamics 365 Field Service or Project Service trial? Worry No More!!!
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

617 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