在excel中循环遍历具有不同值的单元格 [英] Loop through a cell with different value serially in excel

问题描述

您好，

我有一个情况请帮我解决这个问题。



我有一个Excel文件，其中2张名为



Sheet1& Sheet2



Sheet1有INPUT+复杂公式=结果（再次使用复杂的公式）列

实际上案例是sheet1有很多复杂的形式和经过大量的计算后我终于得到了一些结果



Sheet2只有输入数字& 结果栏目。

很多输入数字（随机）需要逐个输入到sheet1输入列。结果列应在输入结果后自动填写。



表附加作为参考。工作表链接： Looping.xlsx - Google表格 [ ^ ]



谢谢。



我的尝试：



我试图通过使用每个循环编写一个宏，这将从范围中获取单元格，但它不会从另一个工作表获得结果。

 Sub test1_Click（）
 Dim x As Integer 
 
 Start_open = Sheet2.Range（A3）。Value 
 
 End_open = Sheet2.Range（A5）。值
 
 For x = Start_open To End_open 
 
 Sheets（Sheet1）。Name =Sheet1 
范围（A5）。选择
 ActiveCell.Value = x 
 
 
 
'Z = 5 
'y = 3 
'
'对于i = Start_open To End_open 
'Cell（Z，y）= i 
'Z = Z + 1 
'
'Sheets（Sheet2）。Name =Sheet2 
'范围（C3）。选择
'
'下一个
 
'MsgBox x 
 
下一个
 
 End Sub 

解决方案

请先阅读我对该问题的评论。对 CHill60 提供的解决方案＃1的评论[ ^ ]也会有所帮助。



假设您要在 Sheet1 中写入10个随机数，但是您希望从Sheet2中随机化获得最小值和最大值。



看看例子：

 ' 强制显式声明该模块中的所有变量 
 选项 明确 
 
  Sub  WriteRandomNumbers（）
  ' 定义源和目标工作表的变量 
  Dim  SrcWsh 作为工作表，DstWsh 作为工作表
 ' < span class =code-comment> define min and max  
  Dim  iMin  As  整数，iMax 作为 整数 
 ' 随机数 
  Dim  rndValue  As  整数 
 '  iterator  
 < span class =code-keyword> Dim  i  As  整数 
 
 ' 错误获取错误处理程序 
 开 错误  GoTo  Err_WriteRandomNumbers 
 
 ' 启动变量 
 设置 SrcWsh = ThisWorkbook.Worksheets （  Sheet2）
  Set  DstWsh = ThisWorkbook.Worksheets（  Sheet1）
 
 iMin = SrcWsh.Range（  A2）
 iMax = SrcWsh.Range（  B2） 
 
 ' 在Sheet1中循环显示单元格1到10  - 列A  
 对于 i =  1  要 < span class =code-digit> 10  
随机化
 rndValue = Int（（iMax * Rnd）+ iMin）
 DstWsh.Range（  A& i）= rndValue 
 下一步 
 
 '  exis子程序 
 Exit_WriteRandomNumbers：
 '  ignore错误 
 开启 错误 简历 下一步 
 ' 清理！ 
 设置 SrcWsh = 没什么 
 设置 DstWsh =  Nothing  
 退出  Sub  
 
 ' 错误处理程序 
 Err_WriteRandomNumbers：
 ' 告知错误 
 MsgBox Err.Description，vbExclamation，Err.Number 
 ' 转到退出子程序 
 恢复 Exit_WriteRandomNumbers 
 结束  Sub  

目前尚不清楚为什么要尝试在循环中重命名工作表 - 特别是它们的名称相同。



如果您的工作表被称为Sheet1，Sheet2等以外的其他部分，那么像< pre lang =VB>表格（ Sheet1）。Name = Sheet1

将失败。如果你真的想做那样的话，那就用

 Sheets（ 1 ）。Name =    Sheet1 

（注意Excel工作表从1开始编号，而不是像VB本身一样编号）



再次，我没有看到问题是什么 - 因为你在 For 循环中使用了Sheet2的范围，它从中获取值Sheet2中。我猜你的问题是所有的值都被插入到Sheet1上的Cell A5中。如果你想在循环中向下进行该表单，那么只需调整Range参考，就像这样

  Sub  test1_Click（）
  Dim  x  As  整数 
 
 Start_open = Sheet2.Range（  A3）。价值
 End_open = Sheet2.Range（  A5）。价值
 
表格（  1 ）。Name =   Sheet1 
 
 Z =  5  
 
 对于 x = Start_open  To  End_open 
 
 
范围（  +  CStr （Z））。选择 
 ActiveCell.Value = x 
 Z = Z +  1  
 
 下一步 
 
 结束  Sub  

如果您尝试将信息放在其他地方，例如在Sheet3上，则必须先激活工作表才能使用它。

此行例如

 Sheet3.Range（  A  +  CStr （Z））。选择 

结果一个应用程序定义的或对象定义的错误，除非你还包括

 Sheet3.Activate 

我希望这会有所帮助。如果没有，那么使用 在此解决方案旁边有问题或评论？  链接以更详细地解释您的问题

Hello,
I Have a situation Please help me to solve this.

I have a Excel file with 2 sheets named as

Sheet1 & Sheet2

Sheet1 has "INPUT" + "Complex Formulae" = "Result"(again with complex formulae) Columns
Actually the case is that the sheet1 has a lot of complex formuales & I will get some result finally after a lot of calculations

Sheet2 has only "Input Numbers" & "Results" Columns.
A lot of input numbers(random) which need to go to sheet1 input column one by one & Results column should fill automatically after the result of that input.

Sheet Attached as a reference. Sheet Link: Looping.xlsx - Google Sheets[^]

Thank you.

What I have tried:

I Have tried to write a macros by using for each loop, which will take cell from range but it is not going to get result from another sheet.

Sub test1_Click()
    Dim x As Integer
    
    Start_open = Sheet2.Range("A3").Value
    
    End_open = Sheet2.Range("A5").Value
    
    For x = Start_open To End_open
    
    Sheets("Sheet1").Name = "Sheet1"
    Range("A5").Select
    ActiveCell.Value = x
    
    
    
'    Z = 5
'    y = 3
'
'        For i = Start_open To End_open
'        Cells(Z, y) = i
'        Z = Z + 1
'
'        Sheets("Sheet2").Name = "Sheet2"
'        Range("C3").Select
'
'        Next
        
    'MsgBox x
    
    Next
    
End Sub

解决方案

Please, read my comment to the question first. The comment to the solution #1 provided by CHill60[^] will be helpful too.

Let's say you want to write 10 random numbers in Sheet1 but you want to get minimal and maximal values to randomize from Sheet2.

Take a look at example:

'force explicit declaration of all variables in that module
Option Explicit

Sub WriteRandomNumbers()
'define variables for source and destination worksheets
Dim SrcWsh As Worksheet, DstWsh As Worksheet
'define min and max
Dim iMin As Integer, iMax As Integer
'random number
Dim rndValue As Integer
'iterator
Dim i As Integer

'on error got error handler
On Error GoTo Err_WriteRandomNumbers

'initiate variables
Set SrcWsh = ThisWorkbook.Worksheets("Sheet2")
Set DstWsh = ThisWorkbook.Worksheets("Sheet1")

iMin = SrcWsh.Range("A2")
iMax = SrcWsh.Range("B2")

'loop through cell 1 to 10 in Sheet1 - column A
For i = 1 To 10
    Randomize
    rndValue = Int((iMax * Rnd) + iMin)
    DstWsh.Range("A" & i) = rndValue
Next

'exis sub-programme
Exit_WriteRandomNumbers:
    'ignore errors
    On Error Resume Next
    'clean up!
    Set SrcWsh = Nothing
    Set DstWsh = Nothing
    Exit Sub

'error handler
Err_WriteRandomNumbers:
    'inform about error
    MsgBox Err.Description, vbExclamation, Err.Number
    'go to exit subroutine
    Resume Exit_WriteRandomNumbers
End Sub

It is not clear why on earth you would attempt to rename the sheets within the loop - especially to the same name that they were.

If your sheets are called something other than "Sheet1", "Sheet2" etc then the line(s) like

Sheets("Sheet1").Name = "Sheet1"

will fail. If you really want to do something like that then use

Sheets(1).Name = "Sheet1"

(Note Excel sheets start numbering from 1 not 0 like VB itself)

Again I don't see what the problem is - because you are using a range from Sheet2 in the For loop it is getting the values from Sheet2. I'm guessing your problem is that all of the values are being inserted into Cell A5 on Sheet1. If you want to progress down that sheet within the loop then just adjust the Range reference as you go like this

Sub test1_Click()
    Dim x As Integer
    
    Start_open = Sheet2.Range("A3").Value
    End_open = Sheet2.Range("A5").Value
    
    Sheets(1).Name = "Sheet1"
    
    Z = 5
    
    For x = Start_open To End_open
    

        Range("A" + CStr(Z)).Select
        ActiveCell.Value = x
        Z = Z + 1
   
    Next
    
End Sub

If you were trying to put the information somewhere else, on Sheet3 for example then you have to activate the sheet before you can use it.
This line for example

Sheet3.Range("A" + CStr(Z)).Select

results in an "Application-defined or object-defined error" unless you also include

Sheet3.Activate

I hope this helps. If not then use the  Have a Question or Comment?  link next to this solution to explain your problem in more detail

这篇关于在excel中循环遍历具有不同值的单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！