更改活动工作表上的数据时，以下工作表上的VBA Excel自动增量值 [英] VBA Excel autoincrement value on the following sheet, when changing data on active sheet

问题描述

在活动工作表中执行代码后，我希望在下一个工作表中将我的值加1.

I would like to have my values incremented on the following sheet by 1 after the code execution on the activesheet.

情况看起来像这样:公共子ResizeCiv2()

The situation looks like this: Public Sub ResizeCiv2()

   Dim targetSheet As Worksheet
   Dim targetRange As Range
   Dim targetShape As Shape


' Define the sheet that has the pictures
Set targetSheet = ThisWorkbook.ActiveSheet
' Define the range the images is going to fit
Set targetRange = targetSheet.Range("C3:K24")

' Loop through each Shape in Sheet
For Each targetShape In targetSheet.Shapes
    ' Check "picture" word in name
    If targetShape.Name Like "*Picture*" Then
        ' Call the resize function
        SizeToRange targetShape, targetRange
    End If

   Next targetShape

   Call CivBox
   Call Divider

   ActiveSheet.Range("M15").Value = Range("D52")
   With Sheets("Cables 1").Range("C50:C51")
   .Value = .Value + 1
   End With

  End Sub

现在调试器将突出显示 .Value = .Value + 1 ，说这是类型不匹配.

And now debugger highlights .Value = .Value + 1 saying, that it's type mismatch.

这是我的活动图片，只有一张照片.按照上面的代码，我偶尔会添加另一张图片.如果是这样，我需要在下一页上将值增加1 ...

This is my active sheet with one picture. As per the code above, I am going to add up another picture occasionally. If so, I need the value incrementation by 1 on the following sheet...

如果我不能使用 With 语句或 Sheet("Cables 1").Range("C50").Value = Range("C50").Value进行修改，+ 1 是否有使其运行的选项?

If I cannot do it nether with the With statement nor with Sheet("Cables 1").Range("C50").Value = Range("C50").Value + 1 is there any option to make it running?

它可能与此查询有关:如何我可以简化或循环Excel VBA代码以在不同的工作表中交换数据吗?

It's possibly related to this query: How can I simplify or loop excel vba code for data exchange in different sheets?

更新:

我在这里找到了一些解决方案:

I found some solution here:

将范围中的单元格值递增1个使用VBA Excel

并尝试使用我的代码...

and tried with my code...

Public Sub ResizeCiv2()

Dim targetSheet As Worksheet
Dim targetRange As Range
Dim targetShape As Shape
Dim rng As Range

Set rng = Sheets("Cables 1").Range("C50:C51")

' Define the sheet that has the pictures
Set targetSheet = ThisWorkbook.ActiveSheet
' Define the range the images is going to fit
Set targetRange = targetSheet.Range("C3:K24")

' Loop through each Shape in Sheet
For Each targetShape In targetSheet.Shapes
    ' Check "picture" word in name
    If targetShape.Name Like "*Picture*" Then
        ' Call the resize function
        SizeToRange targetShape, targetRange
    End If

Next targetShape

Call CivBox
Call Divider

ActiveSheet.Range("M15").Value = Range("D52")

Dim myCell As Range
With rng
myCell = myCell + 1
End With

 End Sub

现在，调试器说:对象变量或未设置变量

当我用 Value = .Value + 1 替代myCell行时，出现错误:类型不匹配

When I superseede the myCell line with Value = .Value + 1, then I am getting error: Type mismatch

推荐答案

我在这里找到了解决问题的答案:

I found an answer to my problem here:

我如何使用VBA增加Excel中的单元格值?错误:类型不匹配

所以我的代码应如下所示:

so my code should look like this:

Public Sub ResizeCiv2()

Dim targetSheet As Worksheet
Dim targetRange As Range
Dim targetShape As Shape

Set rng = Sheets("Cables 1").Range("C50:C51")

' Define the sheet that has the pictures
Set targetSheet = ThisWorkbook.ActiveSheet
' Define the range the images is going to fit
Set targetRange = targetSheet.Range("C3:K24")

' Loop through each Shape in Sheet
For Each targetShape In targetSheet.Shapes
    ' Check "picture" word in name
    If targetShape.Name Like "*Picture*" Then
        ' Call the resize function
        SizeToRange targetShape, targetRange
    End If

Next targetShape

Call CivBox
Call Divider

ActiveSheet.Range("M15").Value = Range("D52")
Sheets("Cables 1").Cells(50, 3).Value = Sheets("Cables 1").Cells(50, 3).Value + 1
Sheets("Cables 1").Cells(51, 3).Value = Sheets("Cables 1").Cells(51, 3).Value + 1

End sub

我在表格中指定确定"单元格而不是范围".

Where instead of "range" I am specifying the certains cells in the sheet.

这篇关于更改活动工作表上的数据时，以下工作表上的VBA Excel自动增量值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！