使用VBA在excel中创建表格

问题描述：

我想用VBA在Excel中创建一个表格，但我被卡在代码中。我需要找到代码来使用VBA表单将数据输入到我的工作表中。这里是我使用的代码，但不起作用。使用VBA在excel中创建表格

Private Sub cmdAdd_Click() 

Dim LastRow As Range 
Dim DPDIAdhocRequestTable As ListObject 


With LastRow 

    Cells(1, 2) = RequesterName.Value 
    Cells(1, 3) = RequesterPhoneNumber.Value 
    Cells(1, 4) = RequesterBureau.Value 
    Cells(1, 5) = DateRequestMade.Value 
    Cells(1, 6) = DateRequestDue.Value 
    Cells(1, 7) = PurposeofRequest.Value 
    Cells(1, 8) = ExpectedDataSaurce.Value 
    Cells(1, 9) = Timeperiodofdatarequested.Value 
    Cells(1, 10) = ReoccuringRequest.Value 
    Cells(1, 11) = RequestNumber.Value 
    Cells(1, 12) = AnalystAssigned.Value 
    Cells(1, 13) = AnalystEstimatedDueDate.Value 
    Cells(1, 14) = AnalystCompletedDate.Value 
    Cells(1, 15) = SupervisiorName.Value 

End With 


End Sub

你能帮我弄清楚输入命令的正确代码吗？

非常感谢你的帮助。

'但不work..' - 什么，具体而言，是不是工作？ –

'Cells（x，y）.Value = ObjectName.Value'您是否尝试过这种语法？ –

你可能想纠正'SupervisiorName'的拼写 - 虽然不是一个错误，但会让我更加恼火。 :) –

答

第一个问题是，你已经创建了一个名为Range但LastRow尚未分配什么的。

'Declaration 
Dim LastRow As Range 
'Assigns the last row based on the last item in Column A 
Set LastRow = Range("A" & Rows.Count).End(xlUp).Row 
With LastRow 
    ... 
End With

第二个问题是With LastRow块中的次要语法错误。

With LastRow 
     Cells(x,y).Value = "Foo" 
End With

应该

With LastRow 
    .Cells(x,y).Value = "Foo" 
End With

基本上是相同的话说LastRow.Cells(x,y).Value = "Foo"。没有“。”在Cells()前VBA不会对With应用到该对象，并承担你的意思ActiveSheet.Cells()

他们的'With'语句不会对其内部的任何内容产生任何影响，使用它当前写入的方式 – Tom

@Tom很好的捕获，更新它的方式。 –

答

正如@Adam说 - 你已经创建LASTROW，而不是它分配给什么。
我猜这是你想要粘贴数据的下一行，所以它应该是一个Long保存行号而不是对单元格的实际引用。

在下面你的代码有资格表单控件加入Me.，例如Me.RequesterName.Value
https://msdn.microsoft.com/en-us/library/office/gg251792.aspx

Private Sub cmdAdd_Click() 

    Dim wrkSht As Worksheet 
    Dim LastRow As Long 

    'The sheet you want the data to go into. 
    Set wrkSht = ThisWorkbook.Worksheets("Sheet1") 

    'You're after the last row number, rather than referencing the range so LastRow is a Long. 
    'This looks for the last cell containing data in column A (the 1 in 'Cells(...)'). 
    LastRow = wrkSht.Cells(Rows.Count, 1).End(xlUp).Row + 1 

    'With... End With block. Cells is preceded by a '.' notation - indicating it's referencing the 'With wkrSht'# 
    'https://msdn.microsoft.com/en-us/library/wc500chb.aspx 
    With wrkSht 

     'Using LastRow as row number in cell reference. 
     .Cells(LastRow, 2) = RequesterName.Value 

     'Before adding dates to the sheet you might want to check that the 
     'user entered a date and not rubbish. 
     .Cells(LastRow, 5) = DateRequestMade.Value 
     'You could use CDATE to force it to a date - will try and coerce the entered text into a date. 
     'Note - 1 will be changed to 01/01/1900 (so will need to add extra code to check it really is a date). 
     .Cells(LastRow, 5) = CDate(DateRequestMade.Value) 

    End With 

End Sub

使用VBA在excel中创建表格

相关推荐