Page 2 of 2 FirstFirst 12
Results 11 to 15 of 15

Thread: vba for a range and return value

  1. #11
    Board Regular
    Join Date
    Dec 2017
    Location
    UK
    Posts
    903
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    1 Thread(s)

    Default Re: vba for a range and return value

    this code is shows you how to do a MATCH in VBA:
    Code:
    marr = Range("NameManager1")
    farr = Range("Namemanager")
    For kk = LBound(marr, 1) To UBound(marr, 1)
     If marr(kk, 1) = farr Then
       MsgBox kk
      Exit For
     End If
    Next kk
    Speed up your code use variant arrays and NEVER ACCESS THE WORKSHEET IN A LOOP

  2. #12
    New Member
    Join Date
    Mar 2019
    Posts
    42
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: vba for a range and return value

    so A1 is called in "Smith" and my range from F1:P1 is called Tom.
    My cells in my range of C column will fill one by one "Smith" .
    so i have these two formulas =MATCH(Smith;Tom;0) which is in AA20 cell and the formula =AA20+COUNTIF(Tom;Smith)-1.
    so if in column B we have a Yes the in column D put the result for the match fuction and in E the result of the countif

  3. #13
    Board Regular
    Join Date
    Dec 2017
    Location
    UK
    Posts
    903
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    1 Thread(s)

    Default Re: vba for a range and return value

    I think I have understood your requirements, if so this should do it:
    Code:
    lastrow = Cells(Rows.Count, "C").End(xlUp).Row
    inarr = Range(Cells(1, 2), Cells(lastrow, 3))
    outarr = Range(Cells(1, 4), Cells(lastrow, 4))
    tomrange = Range("Tom")
    
    
     For i = 1 To lastrow
      If inarr(i, 1) = "YES" Then
           A1 = inarr(i, 2)
           matchfnd = False
           matchi = 0
           matchcnt = 0
           For kk = LBound(tomrange, 2) To UBound(tomrange, 2)
           
            If tomrange(1, kk) = A1 Then
              If Not (matchfnd) Then
                matchfnd = True
                matchi = k
              End If
                matchcnt = matchcnt + 1
       
            End If
           Next kk
                outarr(i, 1) = matchi + matchcnt - 1
      End If
     Next i
    Range(Cells(1, 4), Cells(lastrow, 4)) = outarr
    Speed up your code use variant arrays and NEVER ACCESS THE WORKSHEET IN A LOOP

  4. #14
    Board Regular
    Join Date
    Dec 2017
    Location
    UK
    Posts
    903
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    1 Thread(s)

    Default Re: vba for a range and return value

    I just spotted a typo in the code "k" should be "kk"
    try this:
    Code:
    Sub test()
    lastrow = Cells(Rows.Count, "C").End(xlUp).Row
    inarr = Range(Cells(1, 2), Cells(lastrow, 3))
    outarr = Range(Cells(1, 4), Cells(lastrow, 4))
    tomrange = Range("Tom")
    
    
     For i = 1 To lastrow
      If inarr(i, 1) = "YES" Then
           A1 = inarr(i, 2)
           matchfnd = False
           matchi = 0
           matchcnt = 0
           For kk = LBound(tomrange, 2) To UBound(tomrange, 2)
           
            If tomrange(1, kk) = A1 Then
              If Not (matchfnd) Then
                matchfnd = True
                matchi = kk
              End If
                matchcnt = matchcnt + 1
       
            End If
           Next kk
                outarr(i, 1) = matchi + matchcnt - 1
      End If
     Next i
    Range(Cells(1, 4), Cells(lastrow, 4)) = outarr
     
    End Sub
    Speed up your code use variant arrays and NEVER ACCESS THE WORKSHEET IN A LOOP

  5. #15
    New Member
    Join Date
    Mar 2019
    Posts
    42
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: vba for a range and return value

    Hi,
    Sorry for the late response.
    yes it about what i want but up till now i think the best solution was
    Sub test1()


    lastrow = Cells(Rows.Count, "C").End(xlUp).Row
    inarr = Range(Cells(1, 2), Cells(lastrow, 3))
    outarr = Range(Cells(1, 4), Cells(lastrow, 4))
    For i = 1 To lastrow
    If inarr(i, 1) = "Yes" Then
    Cells(1, 1) = inarr(i, 2)
    outarr(i, 1) = Cells(4, 1)
    End If
    Next i
    Range(Cells(1, 4), Cells(lastrow, 4)) = outarr

    End Sub

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •