How to insert data from userform to a specific row with a specific value

Question

I want to create a userform that can find the "Sales" value in column E and then input the remaining data to the same row.

Set APAC = Sheet2
APAC.Activate
Range("E18:E1888").Select
For Each D In Selection
    If D.Value = "TWO.Sales.Value" Then
        Exit For
    End If
Next D


Rows(D.Row).Select

    D.Offset(0, 2).Value = TWO.RSA.Value
    D.Offset(0, 3).Value = TWO.Part.Value
    D.Offset(0, 4).Value = Application.WorksheetFunction.VLookup(TWO.Part.Value, Worksheets("DataEntry").Range("T2:U70").Value, 2, False)
    D.Offset(0, 5).Value = TWO.Program.Value
    D.Offset(0, 6).Value = TWO.QTY.Value
    Sheet2.Activate

This is my code but

run time error '91'

occurs.

Wecome to SO... https://stackoverflow.com/a/60650179/9808063 — Naresh, Mar 12 '20 at 09:06
First, declare all your variables. Add `Option Explicit` at the top of your code module. You will find that `TWO.RSA` is an object that hasn't been declared. Then don't *Select* or *Activate* anything. `For Each D in Sheet2.Range("E18:E1888")` will have the exact same result as what your 3 lines of code generate - minus the clutter. — Variatus, Mar 12 '20 at 09:11
As mentioned in earlier comment.. Here is a very similar question ... https://stackoverflow.com/a/60650179/9808063 ... You dont have to loop through to find sales value .. Also is D range or just a variant? — Naresh, Mar 12 '20 at 09:22

score 1 · Answer 1 · answered Mar 12 '20 at 09:21

I am having error on the "Rows(D.Row).select" line – Jacob 2 mins ago

That means "TWO.Sales.Value" was not found in Range("E18:E1888") and hence D was nothing. You need to check if the value was found. Also I have a feeling that you wanted If D.Value = TWO.Sales.Value Then instead of If D.Value = "TWO.Sales.Value" Then

Also there is no need to Select/Activate. You can directly work with the objects. You may want to see How to avoid using Select in Excel VBA

Whenever you are working with VLookup, it is better to handle the error that may pop up when a match is not found. There are various ways to do it. I have shown one way in the code below.

Is this what you are trying? (UNTESTED)

Option Explicit

Sub Sample()
    Dim APAC As Worksheet
    Dim curRow As Long
    Dim aCell As Range
    Dim Ret

    Set APAC = Sheet2

    With APAC
        For Each aCell In .Range("E18:E1888")
            If aCell.Value = TWO.Sales.Value Then
                curRow = aCell.Row
                Exit For
            End If
        Next aCell

        If curRow = 0 Then
            MsgBox "Not Found"
        Else
            .Range("G" & curRow).Value = TWO.RSA.Value
            .Range("H" & curRow).Value = TWO.Part.Value

            On Error Resume Next
            Ret = Application.WorksheetFunction.VLookup(TWO.Part.Value, _
                  Worksheets("DataEntry").Range("T2:U70").Value, 2, False)
            On Error GoTo 0

            If Ret <> "" Then .Range("I" & curRow).Value = Ret

            .Range("J" & curRow).Value = TWO.Program.Value
            .Range("K" & curRow).Value = TWO.QTY.Value
        End If
    End With
End Sub

NOTE: If the range .Range("E18:E1888") is dynamic then you may want to find the last row as shown HERE and then use the range as .Range("E18:E" & LastRow)

you also can replace the first loop to look for sales value with Range.Find — Naresh, Mar 12 '20 at 09:24
Yup one can as shown [HERE](http://www.siddharthrout.com/index.php/2018/01/05/find-and-findnext-in-excel-vba/) — Siddharth Rout, Mar 12 '20 at 09:25
@SiddharthRout i tried your code but they it is saying that they can't find TWO.Sales.Value in the range E18:E1888 even though it is there. — Jacob, Mar 13 '20 at 00:59
Thank you guys so much for your help. I use the Range.find function in the end as i find it easier. — Jacob, Mar 13 '20 at 03:11
@Jacob: Probabaly that is because there is some space or it is case sensitive. Try by replacing `If aCell.Value = TWO.Sales.Value Then` with `If Upper(Trim(aCell.Value)) = Upper(Trim(TWO.Sales.Value)) Then`. Yes using `.Find` in a better way though. Anyways, did the rest of the code help? — Siddharth Rout, Mar 13 '20 at 04:18

How to insert data from userform to a specific row with a specific value

1 Answers1