基于变量“N”扩展的动态表。 [英] Dynamic Table that Extends based on Variable "N"

问题描述

您好，

想要制定一个简单的预算，并尝试创建一个基于我输入的变量扩展的表，非常类似于excel，但我无法理解如何重现。我的要求如下：

我希望能够输入所需的周数，我的表格会自动添加行以匹配"n"。假设我输入22，我希望我的表格扩展到22行（说它历史上已经有15行）.....如果我然后将输入更改为
10，表格将删除12行，这样我现在有10个。

这方面的一个例子是，我希望在10周内保存2K，我的桌子预计周数，我将逐周手动填写我已保存的数量。而不是手动拖动表，表将有
10行（编号1-10）...我重新评估，并有15周保存，我希望表扩展到15，当我将15输入到"分配给付款的周"中。细胞。

远程可以吗？

谢谢，

Sagar2000

解决方案

您好

问候

Deepak

Hi there,

Looking to make a simple budget, and am attempting to create a table that extends based on a variable that I input, very much like the templates in excel, but I can't understand how to reproduce. My requirements are below:

I want to be able to enter the number of weeks required, and my table will automatically add rows to match "n." Say I input 22, I would like my table to then extend to 22 rows (say it already has 15 historically)..... If I then change input to 10, the table will delete 12 rows, so that I now have 10.

An example of this is, I want to save up to 2K over the course of 10 weeks, and my table projects out the number of weeks, in which I would manually fill in how much I have saved, week by week. Instead of manually dragging the table, the table would have 10 rows (numbered 1-10)... I re-evaluate, and have 15 weeks to save, I would want the table to extend to 15, when I input 15 into the "Weeks Allocated to Payment" Cell.

Is this remotely possible?

Thanks,

Sagar2000

解决方案

Hi Sagar2000,

You had mentioned that,"Say I input 22, I would like my table to then extend to 22 rows (say it already has 15 historically)..... If I then change input to 10, the table will delete 12 rows, so that I now have 10."

Please refer example below.

Sub demo()

Dim rng As Range
Dim tbl As ListObject
Dim myValue, rw As Integer
myValue = InputBox("Enter number of Rows...")

  Set tbl = ActiveSheet.ListObjects("Table1")
  If myValue > tbl.Range.Rows.Count Then
        rw = myValue - tbl.Range.Rows.Count
        Set rng = Range("Table1[#All]").Resize(tbl.Range.Rows.Count + rw, tbl.Range.Columns.Count)
        tbl.Resize rng
  Else
        rw = tbl.Range.Rows.Count - myValue
        Set rng = Range("Table1[#All]").Resize(tbl.Range.Rows.Count - rw, tbl.Range.Columns.Count)
        tbl.Resize rng
  End If
End Sub

Output:

You will notice that Currently, table containing 5 rows. I will enter 10 in input box. Table will add new 5 rows. Now total rows will be 10.

Then I will enter 6 in input box and it will delete 4 rows from table and total row will be 6 in table.

Further, You can try to modify the code as per your requirement.

Other question you had asked,"Is this remotely possible?"

If you are able to open the file programmatically then you can refer to the table inside sheet and resize it.

Reference:

ListObject Object (Excel)

Regards

Deepak

这篇关于基于变量“N”扩展的动态表。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！