+ Reply to Thread
Results 1 to 8 of 8

I want to create loop with a loop

Hybrid View

  1. #1
    Registered User
    Join Date
    05-11-2015
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    28

    I want to create loop with a loop

    Hi,

    I will have two variables with two values,

    I want to create a loop that runs through Coloumn A and check for the 1st variable and once found then run through Row B and check for the 2nd variable and then output this value to a new third variable.

    I have attached a sample excel sheet. Please help!Sample.xlsx

  2. #2
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,059

    Re: I want to create loop with a loop

    You say "till the end of the range" but what is the range? I assume you could have more rows? and we can't detect the last row, because the last row is 18 in the example?
    Please help by:

    Marking threads as closed once your issue is resolved. How? The Thread Tools at the top
    Any reputation (*) points appreciated. Not just by me, but by all those helping, so if you found someone's input useful, please take a second to click the * at the bottom left to let them know

    There are 10 kinds of people in this world... those who understand binary, and those who don't.

  3. #3
    Registered User
    Join Date
    05-11-2015
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    28

    Re: I want to create loop with a loop

    No sorry, for this example the last row would be 10. I just meant I could have another currency on row 11.

  4. #4
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: I want to create loop with a loop

    Sub KittenExplosion()
    
    For i = 4 To 10
        For j = 2 To 5
            If Cells(i, 1) = Range("B14") And Cells(3, j) = Range("B13") Then
                newvariable = Cells(i, j)
                Exit For
                Exit For
            End If
        Next
    Next
            
    MsgBox newvariable
    
    End Sub
    Make Mom proud: Add to my reputation if I helped out!

    Make the Moderators happy: Mark the Thread as Solved if your question was answered!

  5. #5
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,059

    Re: I want to create loop with a loop

    another one:

    Sub fdate()
    
    Dim ws As Worksheet
    Dim fr As Long 'variable for first row
    Dim lr As Long 'variable for last row
    Dim myType As String
    Dim myCurrency As String
    Dim head_row As Long 'the header column
    Dim head As Range
    Dim fdate As Date
    
    Set ws = Worksheets("Sheet1")
    fr = 4
    lr = 10
    head_row = 3
    myType = ws.Range("B13").Value
    myCurrency = ws.Range("B14").Value
    Set head = ws.Rows(head_row).Find(what:=myType)
    If head Is Nothing Then
        MsgBox ("Sorry the Type could not be found")
        Exit Sub
    End If
    For i = fr To lr
        If Range("A" & i).Value = myCurrency Then 
              fdate = ws.Cells(i, head.Column).Value
              Exit For
        End If
    Next i
    'now do something with fdate
    
    End Sub

  6. #6
    Registered User
    Join Date
    05-11-2015
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    28

    Re: I want to create loop with a loop

    Thank you, works great!

  7. #7
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: I want to create loop with a loop

    And another one (when I was learning, I found For Each macros to be a little more literal and easier to understand):

    Sub UnicornParade()
    
    For Each cell In Range("B4:E10")
        If Cells(cell.Row, 1) = Range("B14") Then
            If Cells(3, cell.Column) = Range("B13") Then
                newvariable = cell
                Exit For
            End If
        End If
    Next
                
    MsgBox newvariable
    
    End Sub

  8. #8
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    Re: I want to create loop with a loop

    Another one :

    Sub test()
      On Error Resume Next
      With Range("A3:E10")
        MsgBox .Cells(Application.Match([B14], .Columns(1), 0), Application.Match([B13], .Rows(1), 0))
      End With
    End Sub
    1. I care dog
    2. I am a loop maniac
    3. Forum rules link : Click here
    3.33. Don't forget to mark the thread as solved, this is important

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Loop through multiple files and call macros (but unable to loop)
    By ryanpetersen in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-23-2014, 12:04 PM
  2. [SOLVED] Copy dynamically changing column and Paste using VBA Loop (Loop within Loop)
    By nixon72 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-12-2013, 12:46 PM
  3. Why did an inner loop variable start overwriting the outer loop range suddenly?
    By 111StepsAhead in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-16-2012, 03:24 PM
  4. Loop a column on Sheet1 and loop a row on Sheet2 to find a match
    By johnnywinter in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-13-2009, 02:09 PM
  5. [SOLVED] How do I create a For loop within a For loop?
    By Linking to specific cells in p in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-24-2005, 06:07 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1