2007 to 2003 vba error

[Tcarey]

The following code was working fine in a 2007 application, but I have a user who needs it to work with 2003. I'd like one file that can handle the conversion. Everything is working fine right now except the portion bolded and underlined. It won't paste to the "Risk" sheet as previously.

'NAICS Sector Enter button
Private Sub CommandButton2_Click()
    Dim Found As Range, FirstFound As String, AllRows As Range
    Dim keysheet As Worksheet
    Set keysheet = ThisWorkbook.Sheets("system")
    
    If ComboBox1.Value <> vbNullString Then
        Set Found = keysheet.Range("C:C").Find(ComboBox1.Value, LookIn:=xlValues, Lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=fasle)
                         
        If Found Is Nothing Then
            MsgBox "No match found.", vbCritical, "No Match"
        Else
        
            FirstFound = Found.Address
            Set AllRows = Found.EntireRow
            
            Do
                Set Found = keysheet.Range("C:C").FindNext(Found)
                Set AllRows = Union(AllRows, Found.EntireRow)
                
            Loop Until Found.Address = FirstFound
            
            AllRows.Copy
         [B][U]   Sheets("Risk").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial
[/U][/B]            Range("A2").Select
            
        End If
        End If
    End Sub

 

[Tcarey]

I made a new sheet for the rows to be copied to and it pasted fine. There seems to be some kind of issue for pasting to the active sheet?

 

[shg]

You need to make sure you're counting the rows on the destination sheet, not the active sheet:

With Worksheets("Risk")
    AllRows.Copy .Cells([SIZE=4][COLOR=red][B].[/B][/COLOR][/SIZE]Rows.Count, "A").End(xlUp).Offset(1)
End With

 

[Tcarey]

Do you have an idea why it works as written in 2007, but not 2003?

I'm still having trouble even after entering your suggestion. Maybe I entered it incorrectly? Would you mind pasting it in the context of the code above?

 

[shg]

Try this:

Private Sub CommandButton2_Click()
    Dim Found       As Range
    Dim FirstFound  As String
    Dim AllRows     As Range
    Dim keysheet    As Worksheet
 
    Set keysheet = ThisWorkbook.Sheets("system")
 
    If ComboBox1.Value <> vbNullString Then
        With keysheet
            Set Found = keysheet.Range("C:C").Find _
                        (ComboBox1.Value, _
                         After:=.Cells(.Rows.Count, .Columns.Count), _
                         LookIn:=xlValues, _
                         Lookat:=xlPart, _
                         SearchOrder:=xlByRows, _
                         SearchDirection:=xlNext, _
                         MatchCase:=False)
        End With
 
        If Found Is Nothing Then
            MsgBox "No match found.", vbCritical, "No Match"
 
        Else
            FirstFound = Found.Address
            Set AllRows = Found.EntireRow
 
            Do
                Set AllRows = Union(AllRows, Found.EntireRow)
                Set Found = keysheet.Range("C:C").FindNext(Found)
            Loop Until Found.Address = FirstFound
 
            With Worksheets("Risk")
                AllRows.Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
            End With
 
            Range("A2").Select
        End If
    End If
End Sub

You should put Option Explicit at the top of every module. It would tell you that "fasle" is not defined.

 

[Tcarey]

Am I missing the past statement in that code?

 

[Tcarey]

Weird. I simply inserted another worksheet. Remained "Risk" in my original code to "sheet1" and moved the control to "sheet1". The program worked just as it did in 2007....

 

[shg]

Am I missing the past statement in that code?

Pardon?

Weird. ... The program worked just as it did in 2007

Not weird, as I said before.

It's because Excel 2003- and Excel 2007+ have different numbers of rows, and when you run code with workbooks of each type open, you need to be sure to count rows in the right version.