输出与输入不匹配

您正在allCells正确设置为不同范围的单元格。

  Set allCells = mySheet.Range("a2", mySheet.Cells(lastRow, lastCol))

然后，您遍历allCells范围中的每一行。

  For Each r In allCells.Rows

但是，当您实际使用r时，仅使用行号。

  thisBanner.imagePath = Cells(r.Row, 2).Value

r.Row是介于1到1,048,576之间的数字，仅此而已。无法保证Cells(r.Row, 2).Value在mySheet上引用了某些内容；只有它来自的任何工作表都将使用与对应的任何工作表的行号r.row。您需要定义一些亲子关系。一个With ... End With内部的块For ... Next并适当注释.Range和.Cell参考文献应该足够了。

Sub createCode()

    Dim myWorkbook As Workbook
    Dim mySheet As Worksheet
    Dim bannerCount As Integer
    Dim BannerCollection() As Banner
    Dim r As Range
    Dim lastRow, lastCol
    Dim allCells As Range

    Set myWorkbook = Application.ActiveWorkbook

    For Each mySheet In myWorkbook.Worksheets
        With mySheet
            'declare your vars outside the loop and zero/null then here if necessary.
            bannerCount = 0
            lastCol = .Range("a2").End(xlToRight).Column
            lastRow = .Range("a2").End(xlDown).Row
            Set allCells = .Range("a2", .Cells(lastRow, lastCol))
        '    MsgBox (mySheet.Name)
        '    MsgBox ("lastRow:" & lastRow & "lastCol:" & lastCol)
            ReDim BannerCollection(allCells.Rows.Count)

            For Each r In allCells.Rows
                Dim thisBanner As Banner
                thisBanner.imagePath = ""
                thisBanner.retImagePath = ""
                thisBanner.bannerTitle = ""
                thisBanner.urlPath = ""
                bannerCount = bannerCount + 1
        '        MsgBox (bannerCount)
                thisBanner.imagePath = .Cells(r.Row, 2).Value
                thisBanner.retImagePath = .Cells(r.Row, 3).Value
                thisBanner.bannerTitle = .Cells(r.Row, 4).Value
                thisBanner.urlPath = .Cells(r.Row, 5).Value
                'MsgBox (.Cells(r.Row, 2).Value)
                'MsgBox (.Cells(r.Row, 3).Value)
                'MsgBox (.Cells(r.Row, 4).Value)
                'MsgBox (.Cells(r.Row, 5).Value)
                BannerCollection(bannerCount - 1) = thisBanner
            Next r

            Dim i As Variant
            Dim retinaCSS, imgCSS, firstBannerCode, otherBannersCode, bannerTracking As String
            retinaCSS = ""
            imgCSS = ""
            firstBannerCode = ""
            otherBannersCode = ""
            bannerTracking = ""


            For i = 0 To bannerCount - 1
                bannerTracking = BannerCollection(i).bannerTitle
                bannerTracking = Replace(bannerTracking, " ", "+")
                bannerTracking = Replace(bannerTracking, "&", "And")
                bannerTracking = Replace(bannerTracking, "%", "PC")
                bannerTracking = Replace(bannerTracking, "!", "")
                bannerTracking = Replace(bannerTracking, "£", "")
                bannerTracking = Replace(bannerTracking, ",", "")
                bannerTracking = Replace(bannerTracking, "'", "")
                bannerTracking = Replace(bannerTracking, "#", "")
                bannerTracking = Replace(bannerTracking, ".", "")
                retinaCSS = retinaCSS & "#sliderTarget .banner-" & i + 1 & "{background-image: url('/assets/static/" & BannerCollection(i).retImagePath & "');}" & vbNewLine
                imgCSS = imgCSS & "#sliderTarget .banner-" & i + 1 & "{background-image: url('/assets/static/" & BannerCollection(i).imagePath & "');}" & vbNewLine
                If i = 0 Then
                    firstBannerCode = firstBannerCode & "<div class=" & Chr(34) & "banner banner-" & i + 1 & " staticBanner" & Chr(34) & ">" & vbNewLine
                    firstBannerCode = firstBannerCode & "<a href=" & Chr(34) & BannerCollection(i).urlPath & Chr(34) & " manual_cm_re=" & Chr(34) & "MAINBANNER-_-BANNER+" & i + 1 & "-_-" & bannerTracking & Chr(34) & "></a>" & vbNewLine
                    firstBannerCode = firstBannerCode & "</div>" & vbNewLine
                Else
                    otherBannersCode = otherBannersCode & "<div class=" & Chr(34) & "banner banner-" & i + 1 & " staticBanner" & Chr(34) & ">" & vbNewLine
                    otherBannersCode = otherBannersCode & "<a href=" & Chr(34) & BannerCollection(i).urlPath & Chr(34) & " manual_cm_re=" & Chr(34) & "MAINBANNER-_-BANNER+" & i + 1 & "-_-" & bannerTracking & Chr(34) & "></a>" & vbNewLine
                    otherBannersCode = otherBannersCode & "</div>" & vbNewLine
                End If
        '        MsgBox (BannerCollection(i).retImagePath & vbNewLine & BannerCollection(i).imagePath & vbNewLine & BannerCollection(i).bannerTitle & vbNewLine & BannerCollection(i).urlPath)

            Next i

            CodeString = ""
            CodeString = CodeString & "<style type=" & Chr(34) & "text/css" & Chr(34) & ">" & vbNewLine
            CodeString = CodeString & "/* Banners */" & vbNewLine
            CodeString = CodeString & imgCSS
            CodeString = CodeString & "/* Retina Banners */" & vbNewLine
            CodeString = CodeString & "@media only screen and (-webkit-min-device-pixel-ratio: 2) {" & vbNewLine
            CodeString = CodeString & retinaCSS
            CodeString = CodeString & "}" & vbNewLine
            CodeString = CodeString & "</style>" & vbNewLine
            CodeString = CodeString & "<div id=" & Chr(34) & "sliderTarget" & Chr(34) & " class=" & Chr(34) & "slides" & Chr(34) & ">" & vbNewLine
            CodeString = CodeString & firstBannerCode
            CodeString = CodeString & "</div>" & vbNewLine
            CodeString = CodeString & "<script id=" & Chr(34) & "sliderTemplate" & Chr(34) & " type=" & Chr(34) & "text/template" & Chr(34) & ">" & vbNewLine
            CodeString = CodeString & otherBannersCode
            CodeString = CodeString & "</script>"

            FilePath = Application.DefaultFilePath & "\" & mySheet.Name & "code.txt"
            Open FilePath For Output As #2
            Print #2, CodeString
            Close #2
            MsgBox ("code.txt contains:" & CodeString)
            MsgBox (Application.DefaultFilePath & "\" & mySheet.Name & "code.txt")
            Erase BannerCollection
        End With
    Next mySheet

End Sub