用户表格编辑现有数据并添加新条目

发布于 2025-02-01 15:53:58 字数 1282 浏览 2 评论 0原文

我有一个表格，其中包含帐户列表及其各种资产分配。他们的帐号是非顺序的，并且保存在列A中。目前，我有代码（请参见下文），该代码允许用户搜索现有数据并使用此表单。

我希望使用相同的表格允许用户添加新帐户。

本质上，我需要表格来检查输入到帐号字段中的编号是否已经存在并更新关联的分配，或者如果不存在帐户号，则将该帐户编号添加到A列的最后一个空行并填写将相关的分配作为在其他字段中输入的分配。

写下来时，这似乎足够简单，但我无法围绕如何做。

请参阅下面的现有代码

Private Sub UpdateButton_Click()
If Me.AccSearch.Value = "" Then
MsgBox "Please enter an account number", vbExclamation, "Error"
Exit Sub
End If
Dim ws As Worksheet
Dim AccNo As Long
Dim AccFound As Range
Set ws = ThisWorkbook.Worksheets("Register")
AccNo = Me.AccSearch.Value

With Range("A:A")
Set AccFound = .Find(AccNo)

With AccFound
.Offset(0, 22) = Me.AusMin.Value
.Offset(0, 23) = Me.AusMax.Value
.Offset(0, 24) = Me.IntMin.Value
.Offset(0, 25) = Me.IntMax.Value
.Offset(0, 40) = Me.PrpMin.Value
.Offset(0, 41) = Me.PrpMax.Value
.Offset(0, 42) = Me.FixMin.Value
.Offset(0, 43) = Me.FixMax.Value
.Offset(0, 34) = Me.AltMin.Value
.Offset(0, 44) = Me.AltMin.Value
.Offset(0, 35) = Me.AltMax.Value
.Offset(0, 45) = Me.AltMax.Value
.Offset(0, 36) = Me.CashMin.Value
.Offset(0, 46) = Me.CashMin.Value
.Offset(0, 37) = Me.CashMax.Value
.Offset(0, 47) = Me.CashMax.Value
End With
End With
End Sub

原文

I have a sheet that contains a list of accounts and their various asset allocations. Their account numbers are non sequential and are kept in Column A. Currently I have code (see below) that allows users to search the existing data and adjust the asset allocations using this form.

I am hoping to use this same form to allow users to also add new accounts.

In essence I need the form to check if the number entered into the Account Number field already exists and update the allocations associated or, if the account number doesn’t exist, to add that account number to the last empty row of column A and fill out the associated allocations as inputted in the other fields.

This seems straight forward enough when written down but I can’t wrap my head around how to do it.

Please see existing code below

Private Sub UpdateButton_Click()
If Me.AccSearch.Value = "" Then
MsgBox "Please enter an account number", vbExclamation, "Error"
Exit Sub
End If
Dim ws As Worksheet
Dim AccNo As Long
Dim AccFound As Range
Set ws = ThisWorkbook.Worksheets("Register")
AccNo = Me.AccSearch.Value

With Range("A:A")
Set AccFound = .Find(AccNo)

With AccFound
.Offset(0, 22) = Me.AusMin.Value
.Offset(0, 23) = Me.AusMax.Value
.Offset(0, 24) = Me.IntMin.Value
.Offset(0, 25) = Me.IntMax.Value
.Offset(0, 40) = Me.PrpMin.Value
.Offset(0, 41) = Me.PrpMax.Value
.Offset(0, 42) = Me.FixMin.Value
.Offset(0, 43) = Me.FixMax.Value
.Offset(0, 34) = Me.AltMin.Value
.Offset(0, 44) = Me.AltMin.Value
.Offset(0, 35) = Me.AltMax.Value
.Offset(0, 45) = Me.AltMax.Value
.Offset(0, 36) = Me.CashMin.Value
.Offset(0, 46) = Me.CashMin.Value
.Offset(0, 37) = Me.CashMax.Value
.Offset(0, 47) = Me.CashMax.Value
End With
End With
End Sub

分享到QQ

分享到微博