Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1525
  • Last Modified:

Excel VBA pointing to the wrong sheet?

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
tesla764
Asked:
tesla764
  • 4
  • 3
  • 3
2 Solutions
 
Martin LissRetired ProgrammerCommented:
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
 
yuppyduCommented:
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
 
tesla764Author Commented:
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
yuppyduCommented:
remove the "." before Range
0
 
Martin LissRetired ProgrammerCommented:
Actually

Set rng = .Range(.Cells(3, 1), .Cells(3, lc))
0
 
tesla764Author Commented:
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
 
tesla764Author Commented:
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
 
yuppyduCommented:
Just rename the sheet as I told you and you will not be influence by tab names
0
 
Martin LissRetired ProgrammerCommented:
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
 
yuppyduCommented:
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

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

  • 4
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now