Excel VBA范围对象通过工作表循环出错 [英] Excel VBA Range object Error in Looping Through Worksheet

问题描述

我正试图循环使用我的工作簿中的不同工作表。我已经尝试通过stackoverflow搜索，但仍然坚持错误。错误显示对象工作表的方法范围失败。

I am trying to loop through different sheets in my workbook. I have tried searching through stackoverflow but still stuck with the error. The error showed "Method 'Range' of object'_Worksheet' failed.

此代码的目的是标准化工作表中的所有格式。

The purpose of this code is to standardise all formats in the worksheets.

Sub formatting()
Dim ws as Worksheet
Dim lastRow as long, lastColumn as long

lastRow = Range("A" & Rows.Count).End(xlUp).Row
lastColumn = (Cells(1, Columns.Count).End(xlToLeft).Column)

For each ws in Activeworkbook.Worksheets
With ws.Range(cells(lastRow, 1), cells(1, lastColumn))
    'rest of the actions I want to perform'
    .font.bold = true
End With
Next ws

End Sub

我刚刚开始使用Excel vba，请启发我！谢谢！

I just got started on Excel vba, please enlighten me! Thank you!

推荐答案

make：
1.你必须重新定义每个工作表的最后一行和最后一列，就是说，将变量赋值放在循环中。

Few changes to be made: 1. You have to redefine the last row and last column for each sheet. With that said, place the variable assignment inside the loop.

1. Instea d的 ws.Range（cells ...），正确的语法是 Range（ws.cells ...）

1. Instead of ws.Range(cells...), the proper syntax is Range(ws.cells...)

见下文：

Sub formatting()
    Dim ws As Worksheet
    Dim lastrow As Long, lastcolumn As Long


    For Each ws In ActiveWorkbook.Worksheets
        lastrow = ws.Range("A" & Rows.Count).End(xlUp).Row
        lastcolumn = (Cells(1, Columns.Count).End(xlToLeft).Column)
        With Range(ws.Cells(1, 1), ws.Cells(lastrow, lastcolumn))
            'rest of the actions I want to perform'
            .Font.Bold = True
        End With
    Next ws

End Sub

这篇关于Excel VBA范围对象通过工作表循环出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！