使用Range（Cells（）Cells（））。样式导致应用程序定义或用户定义的错误 [英] Using Range(Cells() Cells()).Style results in application defined or user defined error

问题描述

curlly如果满足某些标准，我试图设计一系列单元格。我可以成功地将一个样式应用于一个单元格，但迄今尚未成功应用于一个单元格。以下是我所知道的作品：

  ElseIf OneA / OneC> 0.8 And OneA / OneC< = 1然后
 ActiveWorkbook.Sheets（StartYear&& StartQ& - & EndYear&& EndQ）.Cells（Employee，StartCol）。 Style =60％ -  Accent2
  

上面的代码将单元格（Employee，StartCol） ％ - Accent2。但是，当我更改此代码以尝试将样式应用于范围时，我将获得应用程序定义或用户定义的错误。这是我试图将代码更改为：

  ElseIf OneA / OneC> 0.8 And OneA / OneC <= 1然后
 ActiveWorkbook.Sheets（StartYear&& StartQ& - & EndYear&& EndQ）.Range（Cells（Employee，StartCol ），Cells（Employee + 49，StartCol））Style =60％ -  Accent2
  

我不知道我在这里做错了什么我认为这可能是一个语法错误，但是我没有足够的经验来了解vba究竟出了什么问题。任何帮助将不胜感激。谢谢！

解决方案

您正在使用 Cells 全局对象片段，但明确地引用第一个代码段中的工作表的 .Cells 属性。第二个将实际工作，但只有当活动工作表 匹配 从 ActiveWorkbook.Sheets（）。获取对目标工作表的引用，然后使用 .Cells 属性而不是全局：

  ElseIf OneA / OneC> 0.8 And OneA / OneC <= 1 Then 
 Dim target As Worksheet 
 Set target = ActiveWorkbook.Sheets（StartYear&& StartQ&& EndYear&& ; EndQ）
目标
 .Range（.Cells（Employee，StartCol），.Cells（Employee + 49，StartCol））Style =60％ -  Accent2
 End With 
  

Curretly I'm trying to style a range of cells if certain criteria are met. I am able to sucessfully apply a style to one cell, but have so far been unsuccessful in applying this to a range. Here's what I know works:

ElseIf OneA / OneC > 0.8 And OneA / OneC <= 1 Then
      ActiveWorkbook.Sheets(StartYear & " " & StartQ & " - " & EndYear & " " & EndQ).Cells(Employee, StartCol).Style = "60% - Accent2"

This above code styles the cell (Employee, StartCol) to 60% - Accent2. However, when I change this code to try to apply the styling to a range, I get the "Application defined or user defined error". Here's what I tried to change the code to:

ElseIf OneA / OneC > 0.8 And OneA / OneC <= 1 Then
                    ActiveWorkbook.Sheets(StartYear & " " & StartQ & " - " & EndYear & " " & EndQ).Range(Cells(Employee, StartCol), Cells(Employee + 49, StartCol)).Style = "60% - Accent2"

I'm not sure what I'm doing wrong here. I think it may be a syntax error, but I'm not experienced enough with vba to know where exactly it's going wrong. Any help would be greatly appreciated. Thanks!

解决方案

You are using the Cells global object in the second snippet, but explicitly referencing the .Cells property of the worksheet in the first snippet. The second one will actually work, but only if the active worksheet matches the one you're getting from ActiveWorkbook.Sheets(). Grab a reference to the target worksheet, and then use its .Cells property instead of the global:

ElseIf OneA / OneC > 0.8 And OneA / OneC <= 1 Then
    Dim target As Worksheet
    Set target = ActiveWorkbook.Sheets(StartYear & " " & StartQ & " - " & EndYear & " " & EndQ)
    With target
        .Range(.Cells(Employee, StartCol), .Cells(Employee + 49, StartCol)).Style = "60% - Accent2"
    End With

这篇关于使用Range（Cells（）Cells（））。样式导致应用程序定义或用户定义的错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！