更新/编辑数据-VBA Excel [英] Update/Edit Data - VBA Excel

问题描述

在此处输入图片描述我已经浏览了页面，但似乎找不到一个答案。任何帮助是极大的赞赏。我已经获得了可以从VBA表单更新工作表中数据的代码，但是它只是不断在最上面的行上书写，而不编辑特定的行数据。我试图让它编辑正在显示的数据，而不是覆盖顶行数据。任何帮助表示赞赏。我正在使用的代码是：

enter image description hereI have looked through the pages and cannot seem to find an answer. Any help is greatly appreciated. I have gotten the code to work for updating the data in the sheet from from VBA form however it just keeps writing over the top row and does not edit the specific rows data. I am trying to get it to edit the data that is showing and not overwrite the top lines data. any help is appreciated. The code I am using is:

Private Sub cmdupdate_Click()

 Dim rowselect As Single
 rowselect = rowselect + 2
 Rows(rowselect).Select

 Cells(rowselect, 1) = Me.txtname.Value
 Cells(rowselect, 2) = Me.txtposition.Value
 Cells(rowselect, 3) = Me.txtassigned.Value
 Cells(rowselect, 4) = Me.cmbsection.Value
 Cells(rowselect, 5) = Me.txtdate.Value
 Cells(rowselect, 7) = Me.txtjoint.Value
 Cells(rowselect, 8) = Me.txtDAS.Value
 Cells(rowselect, 9) = Me.txtDEROS.Value
 Cells(rowselect, 10) = Me.txtDOR.Value
 Cells(rowselect, 11) = Me.txtTAFMSD.Value
 Cells(rowselect, 12) = Me.txtDOS.Value
 Cells(rowselect, 13) = Me.txtPAC.Value
 Cells(rowselect, 14) = Me.ComboTSC.Value
 Cells(rowselect, 15) = Me.txtTSC.Value
 Cells(rowselect, 16) = Me.txtAEF.Value
 Cells(rowselect, 17) = Me.txtPCC.Value
 Cells(rowselect, 18) = Me.txtcourses.Value
 Cells(rowselect, 19) = Me.txtseven.Value
 Cells(rowselect, 20) = Me.txtcle.Value

 End Sub

推荐答案

将以下部分替换为行选择。。

replace following part for rowselect..

Dim rowselect As Integer
If Range("A:A").Find(Me.txtname.Value) Is Nothing Then
    Msg = "The Text Name could not be found in Column A" & _
    vbLf & "Do you want to create a new record?"    ' Define message.
    Style = vbYesNo + vbCritical + vbDefaultButton1 ' Define buttons.
    Title = "CAUTION"   ' Define title.
    Response = MsgBox(Msg, Style, "CAUTION")  '(Msg, Style, Title)
    If Response = vbYes Then
    rowselect = Range("A" & Rows.Count).End(xlUp).Row + 1
    'This will give you number of the first blank row below you table in Column A 
    Else: Exit Sub
    End If
Else
rowselect = Range("A:A").Find(Me.txtname.Value).Row
End If

' and then all the following shall work correctly each time you cilck the button .. if the form has all those fields
Cells(rowselect, 1) = Me.txtname.Value
Cells(rowselect, 2) = Me.txtposition.Value
Cells(rowselect, 3) = Me.txtassigned.Value ' and so on

这篇关于更新/编辑数据-VBA Excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！