当前位置: 动力学知识库 > 问答 > 编程问答 >

excel - Code Not Filling Table Cells Individually, Filling Whole Columns Instead

问题描述:

I have a button that takes the data from a listbox and puts it into specific cells of my table. My problem right now is when inserting the value into the cells it fills the whole column that cell is in instead of the specific cell.

Here is the code for the button:

Private Sub cbSubmit_Click()

Dim i As Long

Dim v As Variant

Dim vTable() As Variant

Set inventoryTable = cSheet.ListObjects("inventory_table")

colItemID = inventoryTable.ListColumns("Item #").Index

colSpecs = inventoryTable.ListColumns("Specs").Index

v = inventoryTable.DataBodyRange.Rows

ReDim vTable(1 To UBound(v, 1), 1 To 4)

For i = 0 To lbItemList.ListCount - 1

vTable(i + 1, 1) = "=DATA!" & lbItemList.List(i, 2)

If specLink = "" Then

Exit For

Else

vTable(i + 1, 4) = lbItemList.List(i, 1)

End If

inventoryTable.DataBodyRange(i + 1, colItemID).Value = vTable(i + 1, 1)

inventoryTable.DataBodyRange(i + 1, colSpecs).Value = vTable(i + 1, 4)

Next

Unload Me

End Sub

This is how it looks after I run the button.

I want it to only fill in the first cell in Item # and then the cell in Specs in that same row. Then go down the rows each cell and fill in the next item. Instead each item gets filled overtop the old items.

网友答案:

If you are targeting individual cells in a structured table (aka ListObject object) then you need to turn of the AutoCorrect.AutoFillFormulasInLists property.

Application.AutoCorrect.AutoFillFormulasInLists = False

This can also be achieved with Alt+F,T,P, Alt+A then go to the AutoFormat As You Type tab and uncheck Fill formulas in tables to create calculated columns.

Optionally turn it back on at the end of your sub procedure if you wish to have this application-wide option available.

Application.AutoCorrect.AutoFillFormulasInLists = True
分享给朋友:
您可能感兴趣的文章:
随机阅读: