Solved

Excel VBA pointing to the wrong sheet?

Posted on 2013-11-20
10
1,059 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
  • 4
  • 3
  • 3
10 Comments
 
LVL 45

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
 
LVL 4

Assisted Solution

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

Expert Comment

by:Martin Liss
ID: 39662864
Actually

Set rng = .Range(.Cells(3, 1), .Cells(3, lc))
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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 45

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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

760 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

15 Experts available now in Live!

Get 1:1 Help Now