Announcement

Collapse
No announcement yet.

[VBA] Control the loop from the spreadsheet

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • [VBA] Control the loop from the spreadsheet



    Hello.
    The code I am loading now is working good, BUT I would like to control the ranges from the spreadsheet.

    Code:
    Dim rng As Range, fnd As Range
        For Each rng In Range("B3:g3")
            Set fnd = Range("J5:Q10").Find(rng, LookIn:=xlValues, lookat:=xlWhole)
            If Not fnd Is Nothing Then
                fnd.Interior.ColorIndex = 6
            End If
        Next rng
    the Range ("B3:G3") I would like to be able to change from K1 or L1, I really don't know how to do it as well the other range here ("J5:Q10")
    I want to be able to make changes from the sheet.
    Thank you for reading this guys.

  • #2
    Maybe you can define a variable for the Range and allow the user to select the range to search.

    Code:
    Option Explicit
    Sub x()
        Dim rng As Range, fnd As Range, rFind As Range
    
        ''///Get A Range Address From The User to use
        On Error Resume Next
        Set rFind = Application.InputBox( _
                    Title:="Select range", _
                    Prompt:="Select a Range to search", _
                    Type:=8)
        On Error GoTo 0
    
        ''///Test to ensure User did not cancel
        If rFind Is Nothing Then Exit Sub
    
        If Not rFind.Address = "B3:G3" Or Not rFind.Address = "Ki:L1" Then
            MsgBox "The range selected is not valid, please try again"
            Exit Sub
        End If
    
        For Each rng In rFind
            Set fnd = Range("J5:Q10").Find(rng, LookIn:=xlValues, lookat:=xlWhole)
            If Not fnd Is Nothing Then fnd.Interior.ColorIndex = 6
        Next rng
    End Sub
    Hope that Helps

    Roy

    New users should read the Forum Rules before posting

    For free Excel tools & articles visit my web site

    RoyUK's Web Site

    royUK's Database Form

    Where to paste code from the Forum

    About me.

    Comment


    • #3
      Super Moderator, royUK Thank you So much for the time you are spending to help me. B3 and G3 is the range I want to manipulate, and J5:Q10 as well, What I tried to explain is to enter the values for B3 on K1 and for G3 on I1 ►IF◄ VBA let me do that and the same for J5: on M1 and Q10 , N1.
      and this code is just part of a module.
      I am trying to teach myself VBA, I really investigate before ask here, but most of the website they just copy each other, so once I got the basics only Ozgrid help me to keep going,That's why I want to Thank you, for this wonderful lesson Sir royUK Super Moderator.


      Comment


      • #4
        Can you attach a small example of your workbook
        Hope that Helps

        Roy

        New users should read the Forum Rules before posting

        For free Excel tools & articles visit my web site

        RoyUK's Web Site

        royUK's Database Form

        Where to paste code from the Forum

        About me.

        Comment


        • #5
          THANK YOU. Is a real honor for me your help. royUK. In this work book you will see exactly what I am working on. Is a report about coefficient trendlines
          Attached Files

          Comment


          • #6
            I'm not quite sure what you are doing. What do you mean by controlling from the spreadsheet?
            Hope that Helps

            Roy

            New users should read the Forum Rules before posting

            For free Excel tools & articles visit my web site

            RoyUK's Web Site

            royUK's Database Form

            Where to paste code from the Forum

            About me.

            Comment


            • #7
              royUK, Sorry, Proportionally speaking, Part of this workbook you see another loop for next, so I understand that if I use for next, I will do For i = 1 to 5, but also if I create a two more variable like : a = range("M1").value and b= range("N1").value, now I can control the for next loop from the spreadsheet, meaning for i = a to b, So I would like to do the same with the for each loop, Thank you Sir.

              Comment


              • #8


                do you mean
                Code:
                Dim i As Integer, a As Integer, b As Integer
                For i = UBound(a) To LBound(b)
                'do something 
                Next i
                Hope that Helps

                Roy

                New users should read the Forum Rules before posting

                For free Excel tools & articles visit my web site

                RoyUK's Web Site

                royUK's Database Form

                Where to paste code from the Forum

                About me.

                Comment

                Working...
                X