将2个VBA代码合并为1个多页（Excel 2007） [英] combining 2 VBA codes into 1 for multiple sheets (Excel 2007)

问题描述

您好，

我尝试将2个VBA代码合并为1个代码，以处理包含多个工作表的Excel文件。

这些是2个单独的代码。感谢您帮助我！

[code]

期权明确

$
Sub zeno（）   '***计算所有床单



Dim lr As Long

Dim i As Long



对于i = 1到20



   使用表格（"Datatest"&"Datatest2"）  "和;我是
    

        lr = Cells（Rows.Count，1）.End（xlUp）.Row

    
$


        .Range（"A2：A"& lr）.Formula =" =你的公式"

        .Range（"A2：A"& lr）.Value = .Range（" A2：A& lr）.Value

        ;  

   结束时与
    
$
下一页i

结束子

[/ code]

[code]

选项明确



Sub GetInfo（）  '用于选择数据存储的Forumexample2; pb选择表格（必须是最后一次，或错误）



    Dim Rws As Long，Rng As Range，T，C作为范围

    Dim Titles作为范围

    

   

    Rws = Cells（Rows.Count，"A"）。End（xlUp）.Row

   设置Rng =范围（单元格（2,1），单元格（Rws，1））



   对于每个C In Rng.Cells

        T = WorksheetFunction.VLookup（C，工作表（"Datatest"）。范围（"A2：D11"），2,0）

         C.Offset（0,1）= T

   下一页C

 

   范围（"C2：C10"）=" = AVERAGE（Datatest！RC：R [1] C）" b
   范围（"D3：D10"）=" = AVERAGE（Datatest！R [-1] C [-1]：R [1] C [-1]）"



   范围（"C2：D10"）。值=范围（"C2：D10"）。值



结束子

[/ code]

解决方案

尝试在VBA论坛中询问：

http：// social.msdn.microsoft.com/forums/en/isvvba/threads

Hello,

I try to combine 2 VBA codes into 1 code for treatment of an Excel file with multiple sheets.

These are the 2 separate codes. Thank you for helping me out!

[code]

Option Explicit

Sub zeno()   '*** calculation for all sheets

Dim lr As Long
Dim i As Long

For i = 1 To 20

    With Sheets("Datatest" & "Datatest2")  '& i
    
        lr = Cells(Rows.Count, 1).End(xlUp).Row
    

        .Range("A2:A" & lr).Formula = "=YOUR FORMULA"
        .Range("A2:A" & lr).Value = .Range("A2:A" & lr).Value
        
    End With
    
Next i
End Sub

[/code]

[code]

Option Explicit

Sub GetInfo()  'Forumexample2 with selection of datachoice; pb with selection of sheet (has to be last, or error)

    Dim Rws As Long, Rng As Range, T, C As Range
    Dim Titles As Range
    
   
    Rws = Cells(Rows.Count, "A").End(xlUp).Row
    Set Rng = Range(Cells(2, 1), Cells(Rws, 1))

    For Each C In Rng.Cells
        T = WorksheetFunction.VLookup(C, Worksheets("Datatest").Range("A2:D11"), 2, 0)
        C.Offset(0, 1) = T
    Next C
 
    Range("C2:C10") = "=AVERAGE(Datatest!RC:R[1]C)"
    Range("D3:D10") = "=AVERAGE(Datatest!R[-1]C[-1]:R[1]C[-1])"

    Range("C2:D10").Value = Range("C2:D10").Value

End Sub

[/code]

解决方案

try asking in the VBA forum:

http://social.msdn.microsoft.com/forums/en/isvvba/threads

这篇关于将2个VBA代码合并为1个多页（Excel 2007）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！