问题描述:

I was making function for userform in Excel Visual basic, that change specific cell value for a X selected for one column. However, each time the user change value in the userform created, the program multiply the data in each cell related to that column and X value. Example: In case I have in column "A" the value Marie three times, one in the row 3, 7, 10 and my userform input for column "B" is hello. The output for each row will be the same "hello". But I want to modify each value independently the values column "A" repeated.

The code for the userform:

Private Sub btnSubmit_Click()

Dim erow As Long

Dim ws As Worksheet

Dim lastrow As Long

Dim count As Integer

Set ws = ThisWorkbook.Sheets("Table")

lastrow = Sheets("Table").Cells(Rows.count, 1).End(xlUp).Row

count = 0

For x = 2 To lastrow

If Sheets("Table").Cells(x, 1) = Me.cmbEmp Then

ws.Cells(x, 3) = Me.ldlcolor

ws.Cells(x, 30) = (Me.ldlp)

count = count + 1

End If

If Sheets("Table").Cells(x, 3) = Me.cmbEmp Then

ws.Cells(x, 1) = Me.ldlname

ws.Cells(x, 14) = (Me.ldlI)

count = count + 1

End If

Next x

Me.Hide

End Sub

网友答案:

The issue is that you are looping through every row of your sheet, and if a value is duplicated on different rows, it will match each time. You will need to come up with a better way of determining which of the duplicate values you want to modify. For example, this code will loop through until it finds the FIRST occurrence of your data ("marie"):

Dim bolFound As Boolean
bolFound = False
While Not bolFound
    If Sheets("Table").Cells(x, 1) = Me.cmbEmp Then 
        ws.Cells(x, 3) = Me.ldlcolor
        ws.Cells(x, 30) = (Me.ldlp)
        count = count + 1
        bolFound = True
    End If

    If Sheets("Table").Cells(x, 3) = Me.cmbEmp Then 
        ws.Cells(x, 1) = Me.ldlname
        ws.Cells(x, 14) = (Me.ldlI)
        count = count + 1
        bolFound = True
    End If
Wend

If you don't know which occurrence of "marie" you want to change, then you either have to remove duplicate data or figure out a way to differentiate the "marie" on row 3 vs the "marie on row 7.

相关阅读:
Top