(Solved) – Finding the Row number of a value in an array

(solved)-–-finding-the-row-number-of-a-value-in-an-array

Update

so i have added a count in, so if theres a match, add 1 to n. If not, n =0. If n = 1 then row = the number found. But why do i need to use a count why cant i use my original code.

lastrow = (subtaskws.Range("A" & Rows.Count).End(xlUp).row)   1
lastcol = subtaskws.Cells(2, 1).End(xlToRight).Column
lastcollet = lastcol
lastcollet = Split(Cells(1, lastcol).Address, "$")(1)
lastrowST = subtaskws.Range("A" & Rows.Count).End(xlUp).row
activitynum = AddTask.TextBoxid.Value   1


Dim Ctrl, ArrayID, userformorder As Variant, j As Long, range1 As Range, os As Integer, col, listitems As String, templatesubtaskrow As Range, tmeplatemilestonerow As Range, newrowadded As Range
userformorder = Array("SubTaskID", "TextBoxsubtask", "ComboBoxDeliverableFormat", "TextBoxcheckedcomplete", "TextBoxformat", "TextBoxacceptancecriteria", "BudgetWorkloadTextBox", "ComboBoxOwner", "TextBoxTDSNumber", "TextBoxMilestone", "TextBoxTargetDeliveryDate", "ComboBoxW", "ComboBoxI", "ComboBoxe", "TextBoxP", "TextBoxLevel", "TextBoxInputQuality", "TextBoxNewInput", "TextBoxDelay", "TextBoxInternalVV", "TextBoxReviewer", "TextBoxDelivered", "ComboBoxNumIterations", "ComboBoxAcceptance", "ComboBoxProgress", "ComboBoxStatus", "ComboBoxFlowChart", "TextBoxActivitySheet", "TextBoxEvidenceofDelivery", "TextBoxComments") 'etc

Set range1 = subtaskws.Range("A3:A" & lastrowST)
Set templatesubtaskrow = subtaskws.Range("A4:" & lastcollet & "4")
ArrayID = range1.Value

With subtaskws
n = 0
For j = LBound(ArrayID) To UBound(ArrayID)
    If ArrayID(j, 1) = activitynum Then
    n = n   1
    Else
    n = n
    End If
Next j

If n = 1 Then
newrow = j
Else
newrow = lastrow
End If



    Set newrowadded = subtaskws.Range(IDCol & newrow)

Original Question

I have a userform that fills in a sheet labelled subtasks. However, sometimes the info being inserted may be in the middle of the sheet eg. row 64 out of 140.

I want to search the array for a set value (activtiynum) and when found, equal newrow to this newly found row number. If the activtiynum isnt found, then newrow should equal the lastrow 1.

However, the code below wont work and displays the correct row number in the msgbox but then always adds a new row at the end

'find lastrows, columns and cells
lastrow = (subtaskws.Range("A" & Rows.Count).End(xlUp).row)   1
lastcol = subtaskws.Cells(2, 1).End(xlToRight).Column
lastcollet = lastcol
lastcollet = Split(Cells(1, lastcol).Address, "$")(1)
lastrowST = subtaskws.Range("A" & Rows.Count).End(xlUp).row
activitynum = AddTask.TextBoxid.Value   1


Dim Ctrl, ArrayID, userformorder As Variant, j As Long, range1 As Range, os As Integer, col, listitems As String, templatesubtaskrow As Range, tmeplatemilestonerow As Range, newrowadded As Range
userformorder = Array("SubTaskID", "TextBoxsubtask", "ComboBoxDeliverableFormat", "TextBoxcheckedcomplete", "TextBoxformat", "TextBoxacceptancecriteria", "BudgetWorkloadTextBox", "ComboBoxOwner", "TextBoxTDSNumber", "TextBoxMilestone", "TextBoxTargetDeliveryDate", "ComboBoxW", "ComboBoxI", "ComboBoxe", "TextBoxP", "TextBoxLevel", "TextBoxInputQuality", "TextBoxNewInput", "TextBoxDelay", "TextBoxInternalVV", "TextBoxReviewer", "TextBoxDelivered", "ComboBoxNumIterations", "ComboBoxAcceptance", "ComboBoxProgress", "ComboBoxStatus", "ComboBoxFlowChart", "TextBoxActivitySheet", "TextBoxEvidenceofDelivery", "TextBoxComments") 'etc

Set range1 = subtaskws.Range("A3:A" & lastrowST)
Set templatesubtaskrow = subtaskws.Range("A4:" & lastcollet & "4")
ArrayID = range1.Value

With subtaskws

For j = LBound(ArrayID) To UBound(ArrayID)
    If ArrayID(j, 1) = activitynum Then
        MsgBox range1(j).row
        newrow = range1(j).row
    Else
        newrow = lastrow
    End If
Next j


   .Range("A" & newrow).EntireRow.Insert


    Set newrowadded = subtaskws.Range(IDCol & newrow)

    templatesubtaskrow.EntireRow.Copy Destination:=newrowadded

Leave a Reply

Your email address will not be published. Required fields are marked *