Consulting

Results 1 to 3 of 3

Thread: Array loop values

  1. #1
    VBAX Newbie
    Joined
    Sep 2022
    Posts
    2
    Location

    Array loop values

    Hello, I have two columns (C and D) filled with values. If column D is more than zero but less than 10 i want the cell (and cell C from the same row) to be picked. I cannot get this to work. Any suggestions?






    Dim lrowD As Long, frowD As Long, i As Long
    Dim arr As Variant
    Dim rng As Range
    Dim Col1 as String
    
    With ActiveSheet
    
    
    frowD = .Cells(.Rows.Count, "D").End(xlUp).Row - 14
    lrowD = .Cells(.Rows.Count, "D").End(xlUp).Row - 3
    Set rng = ActiveSheet.Range(.Cells(frowD, "B"), .Cells(lrowD, "D"))
    arr = rng
    
    
    For i = 1 To UBound(arr)
    If arr(i, 3) > 0 and arr(i, 3) < 10 Then
    Col1 = Col1 & arr(i, 2) & vbNewLine & arr(i, 3)
    
    End If
    Next i
    end with
    Last edited by SamT; 09-26-2022 at 09:38 AM.

  2. #2
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,619
    Sub M_snb()
      sn = cells(1,4).currentregion
    
      for j = ubound(sn)-14 to ubound(sn) -3
       if sn(j,4)>0 and sn(j,4)<10 then exit for
      next
    
      cells(j,3).select
    End Sub

  3. #3
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,797
    Location
    Set rng = ActiveSheet.Range(.Cells(frowD, "B"), .Cells(lrowD, "D"))
    arr = rng

    '
    '
    '
    Arr = .Range(.Cells(frowD, "B"), .Cells(lrowD, "D")).Value
    
    For i = 1 To UBound(arr)
    If arr(i, 3) > 0 and arr(i, 3) < 10 Then
    Col1 = Col1 & arr(i, 2) & " : " & arr(i, 3) & vbCrLf
    '
    '
    '
    Msgbox Col1
    It would be worth your while to comprehend what Sub M_snb is doing.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

Posting Permissions

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