Solved

Excel VBA pointing to the wrong sheet?

Posted on 2013-11-20
10
1,245 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 47

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
Technology Partners: 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 4

Assisted Solution

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

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 47

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

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Office applications crashing 7 34
Open attachments in viewer, not main app 4 31
Excel Index/Match issue 4 18
Windows update stuck (windows 7) 9 41
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.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

751 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