CODEWITHSUNDEEP
excelvba
Vigmo10
Vigmo10

Reputation: 49

MsgBox error when trying to use a variable

I have the following code, but for some reason the msgbox krow does not seem to work. There is a match but it just shows error. I don't understand why. Help is appreciated, thanks!

Sub addsheet()

Dim lrow As Variant, krow As Variant
Dim i As Long, lastcol As Long, lastrow As Long, lastrowcomp As Long
Dim sheetname As String, sheetname2 As String

Sheets("Main Sheet").Activate
lastcol = Cells(2, Columns.Count).End(xlToLeft).Column
lrow = Application.Match(Sheets("Main Sheet").Range("F6").Value, Sheets("Main Sheet").Range(Cells(2, 14), Cells(2, lastcol)), 0)

If IsError(lrow) Then
    MsgBox "Please Select a value under change"
ElseIf lrow > 0 Then
    lastrow = Sheets("Main Sheet").Cells(Rows.Count, lrow + 13).End(xlUp).Row
    lastrowcomp = Sheets("Comparison Check").Range("A" & Rows.Count).End(xlUp).Row

    For i = 3 To lastrow
        krow = Application.Match(Sheets("Main Sheet").Cells(i, lrow).Value, Sheets("Comparison Check").Range("A3:A" & lastrowcomp), 0)
        MsgBox krow

        'If IsError(krow) Then
        'sheetname = Sheets("Main Sheet").Cells(i, lrow + 13).Value
        'Worksheets.Add(After:=Worksheets(1)).Name = sheetname
        'ThisWorkbook.Sheets("Sheet1").UsedRange.Copy
        'ThisWorkbook.Sheets(sheetname).Select
        'ThisWorkbook.Sheets(sheetname).Range("A1").Select
        'ThisWorkbook.Sheets(sheetname).Paste
        'ThisWorkbook.Sheets(sheetname).Cells.Interior.ColorIndex = 2
        'Else
        If krow > 0 Then
            sheetname2 = Sheets("Comparison Check").Cells(krow, 1).Value
            Sheets(sheetname2).Activate

        End If
    Next i
End If

End Sub

Basically, I am trying to create a new sheet with the name if there is no match and go to the existing sheet if the match exists. but when there is a match it somehow keeps creating a new sheet. Hence, why I added the msgbox krow to confirm the issue was with the matching.

Upvotes: 1

Views: 120

Answers (1)

Ralph
Ralph

Reputation: 9444

I made a few minor adjustments to your code. Please have a look and let me know if it works now.

Option Explicit

Sub addsheet()

Dim lrow As Variant, krow As Variant
Dim i As Long, lastcol As Long, lastrow As Long, lastrowcomp As Long
Dim sheetname As String, sheetname2 As String

Dim ws As Worksheet, lngCount As Long, strSheets As String

strSheets = "Main Sheet/Comparison Check"
For Each ws In ThisWorkbook.Worksheets
    For i = LBound(Split(strSheets, "/")) To UBound(Split(strSheets, "/"))
        If Split(strSheets, "/")(i) = ws.Name Then lngCount = lngCount + 1
    Next i
Next ws
If lngCount < 2 Then
    MsgBox "One of the required sheets was not found." & Chr(10) & "Aborting!"
    Exit Sub
End If

With ThisWorkbook.Worksheets("Main Sheet")
    lastcol = .Cells(2, .Columns.Count).End(xlToLeft).Column
    lrow = Application.Match(.Range("F6").Value, .Range(.Cells(2, 14), .Cells(2, lastcol)), 0)

    If VarType(lrow) = vbError Then
        MsgBox "Please Select a value under change."
        Debug.Print "Value '" & .Range("F6").Value & "' not found in range " & .Cells(2, 14).Address & ":" & .Cells(2, lastcol).Address
    Else
        lastrow = .Cells(.Rows.Count, lrow + 13).End(xlUp).Row
        lastrowcomp = ThisWorkbook.Worksheets("Comparison Check").Range("A" & .Rows.Count).End(xlUp).Row
        For i = 3 To lastrow
            krow = Application.Match(.Cells(i, lrow).Value, ThisWorkbook.Worksheets("Comparison Check").Range("A3:A" & lastrowcomp), 0)
            If VarType(krow) = vbError Then
                MsgBox CStr(krow)
            Else
                sheetname2 = ThisWorkbook.Worksheets("Comparison Check").Cells(krow, 1).Value
                For Each ws In ThisWorkbook.Worksheets
                    If ws.Name = sheetname2 Then lngCount = lngCount + 1
                Next ws
                If lngCount < 3 Then
                    MsgBox "A sheet by the name '" & sheetname2 & "'  couldn't be found." & Chr(10) & "Aborting!"
                    Exit Sub
                Else
                    ThisWorkbook.Worksheets(sheetname2).Activate
                End If
            End If
        Next i
    End If
End With

End Sub

Basically, the above code is almost the same as yours but with much more error handling. So, there are much less assumptions in the code and instead much more checking. If something is not working as intended then you get either a message box or a notification line in the Immediate Window of the VBE.

Also, I coded much more explicitly. That means that I am really telling VBA what I want and don't allow for any interpretation. For example: when you wrote lastcol = Cells(2, Columns.Count).End(xlToLeft).Column you meant to say that you would like to refer to the last column (Columns.Count) of the sheet Main Sheet. Yet, this is not explicitly stated in that line and merely inferred by the VBA compiler since you activated the sheet upfront. I changed all of this to explicit coding which eliminates (most of the time) already a lot of trouble.

Upvotes: 1

Related Questions