分配变量并在范围内使用 [英] Assigning variable and using it in range

问题描述

我是这里的新手，我才刚刚开始使用VBA，如果有人可以帮助我，我将非常感激.

I'm new here and I've just started to use VBA and I will be really grateful if anyone could help me out.

基本上，我从单元格A1:D5中收到了信息.这些信息会定期添加到电子表格中. E1:F5是我已经在VBA中开发的论坛，可以自动填充到Column A的结尾.

Basically, from cell A1:D5 were information that I received. These information are periodically add into the spread sheet. E1:F5 are forumlas which I have already developed in VBA to autofill till the end of Column A.

问题是这样的，我有超过100,000行的数据.因此，始终重播宏将需要大量时间来处理.无论如何，我可以写一些代码来填充前几行(即E5和E6)吗?

The problem is this, I have over a 100,000 rows of data. So always replaying the macro will require a significant amount of time to process. Is there anyway by which I can write a code that will fill in from the previous rows (i.e. E5 and E6)?

    A   B   C   D       E       F
1   6J  6J  HND KMI formula 1   formula 2       
2   6J  6J  HND KMJ formula 1   formula 2       
3   6J  6J  HND NGS formula 1   formula 2       
4   6J  6J  KMI HND formula 1   formula 2       
5   6J  6J  KMJ HND formula 1   formula 2   
6   6J  6J  NGS HND             
7   7A  7A  FUK ISG             
8   7A  7A  FUK KMQ             
9   7A  7A  ISG FUK     

我写了一个简单的代码来测试这一点，但是我被困住了.我认为问题与x作为变量的声明有关:

I have written a simple code to test this out but I was stuck. I think the problem has to do with the declaration of x as a variable :

Sub testloop()

Dim lastrowdatab As String
Dim lastrowpopulate As String
Dim x As String

lastrowdataoriginal = Cells(Rows.Count, "A").End(xlUp).Row
lastrowformula = Cells(Rows.Count, "E").End(xlUp).Row

x = Cells(Rows.Count, "E").End(xlUp).Offset(1, 0).Address(False, fase)
Range(":J" & lastrowdatab).Formula = "=2+3"

If lastrowdatab <> lastrowpopulate Then

    Range("x:E" & lastrowdataoriginal).Formula = "=2+3"

Else

    MsgBox "all good"

End If

End Sub

---

请帮帮我！非常感谢你！

---

Please help me out! Thank you very much!

推荐答案

这是您正在尝试(尝试并测试)的吗?

Is this what you are trying (TRIED AND TESTED)?

Sub testloop()
    Dim ws As Worksheet
    Dim LrowA As Long, LRowE As Long, LRowF As Long
    Dim sFormula As String

    '~~> Change this to relevant sheet
    Set ws = ThisWorkbook.Sheets("Sheet1")

    With ws
        LrowA = .Range("A" & .Rows.Count).End(xlUp).Row
        LRowE = .Range("E" & .Rows.Count).End(xlUp).Row
        LRowF = .Range("F" & .Rows.Count).End(xlUp).Row

        sFormula = .Range("E" & LRowE).Formula
        .Range("E" & LRowE & ":E" & LrowA).Formula = sFormula

        sFormula = .Range("F" & LRowF).Formula
        .Range("F" & LRowF & ":F" & LrowA).Formula = sFormula
    End With
End Sub

这篇关于分配变量并在范围内使用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！