Solved

Excel VBA pointing to the wrong sheet?

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

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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 4

Assisted Solution

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

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 46

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

770 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