如何在VBA中访问非连续范围？

发布于 2025-01-29 07:48:43 字数 1186 浏览 0 评论 0原文

首先，我想做的是如下进行回归分析。

Application.Run "ATPVBAEN.XLAM!Regress", RngY, RngX, False, False, 95, RngOut, False, False, False, False, , False

问题是，当解释变量的p值（我的意思是x轴）超过0.05时，我需要从解释变量中删除该变量。

但是，我无法从工作表中删除数据。因为我对这些数据进行了许多其他回归分析。

因此，我的解决方案是在代码中使用范围变量分配数据。

Dim RngX1 As Range
Dim RngX2 As Range
Dim RngX3 As Range
...
Dim RngX20 As Range
Dim XAxisRng As range

Set XAxisRng = Nothing

Set RngX1 = ActiveSheet.Range("A1","A100")
Set RngX2 = ActiveSheet.Range("B1","B100")
Set RngX3 = ActiveSheet.Range("C1","C100")
...
Set RngX20 = ActiveSheet.Range("T1","T100")

当我进行第一次回归分析时，因此得到结果：例如，RNGX2的p值超过0.05!。然后，我只使用两个范围1。rngx1，2。rngx3，

For j = 1 to 50 '''I need to do regression analysis 50 cases with the same dataset.
   For i = 1 to 20
      If pval < 0.05 Then '''Only the range which have p-value below 0.05 can be included in explanatory variable.
         XAxisRng = Union(XAxisRng , RngX & i) 
      End If
   Next i
   Application.Run "ATPVBAEN.XLAM!Regress", RngY, XAxisRng , False, False, 95, RngOutcome, False, False, False, False, , False
Next j

但它不起作用。

我该如何做这项工作？

原文

First, What I want to do is do regression analysis like below.

Application.Run "ATPVBAEN.XLAM!Regress", RngY, RngX, False, False, 95, RngOut, False, False, False, False, , False

The problem is, when p value of explanatory variable(I mean X-axis) over 0.05, I need to delete that variable from explanatory variable.

However, I cannot delete data from worksheet. Because I do many other regression analysis with that data.

So My solution is assign the data with Range variable in the code.

Dim RngX1 As Range
Dim RngX2 As Range
Dim RngX3 As Range
...
Dim RngX20 As Range
Dim XAxisRng As range

Set XAxisRng = Nothing

Set RngX1 = ActiveSheet.Range("A1","A100")
Set RngX2 = ActiveSheet.Range("B1","B100")
Set RngX3 = ActiveSheet.Range("C1","C100")
...
Set RngX20 = ActiveSheet.Range("T1","T100")

When I do first regression analysis so get the result : For example RngX2 has p-value over 0.05!.
Then I only use two Range 1. RngX1, 2. RngX3

For j = 1 to 50 '''I need to do regression analysis 50 cases with the same dataset.
   For i = 1 to 20
      If pval < 0.05 Then '''Only the range which have p-value below 0.05 can be included in explanatory variable.
         XAxisRng = Union(XAxisRng , RngX & i) 
      End If
   Next i
   Application.Run "ATPVBAEN.XLAM!Regress", RngY, XAxisRng , False, False, 95, RngOutcome, False, False, False, False, , False
Next j

But It doesn't work.

How Can I do this job?

分享到QQ

分享到微博