Use a variable to find and name another.

Posted on 2014-08-06
Last Modified: 2014-08-06
I have a variable named SurfCH_Value
What I need to do is in column M, find the cell that has its value in it.  Then when that is found, (will be there everytime) I need to assign another variable to the value of the cell that is offset 4 cells from that to the right.  Naming that variable CH_FixedValueForSurf

I need to use this name later in the procedure...  new to the naming of items within VBA to use later..  opening up a lot more understanding of how to use and write subs.
Question by:RWayneH
    LVL 26

    Accepted Solution

    Try this:
    Sub Demo()
        Dim SurfCH_Value
        Dim CH_FixedValueForSurf
        Dim rng As Range
        Dim c As Range
        ' Here you asign the value to the "SurfCH_Value" variable
        SurfCH_Value = "TEST_VALUE"
        Set rng = Range("M1:M" & Range("M1").End(xlDown).Row)
        For Each c In rng
            If c.Value = SurfCH_Value Then
                ' Here you asign the value to the "CH_FixedValueForSurf" variable
                CH_FixedValueForSurf = c.Offset(0, 4).Value
                Exit For
            End If
    End Sub

    Open in new window


    Author Comment

    Hey thanks!!
    One more quick thing, how would I at the end concatenate SurfCH_Value with CH_FixedValueForSurf and give that a variable name?
    LVL 27

    Expert Comment

    by:Glenn Ray
    No points...

    in VBA:
    New_Variable_Name = SurfCH_Value & CH_FixedValueForSurf

    Author Closing Comment

    EXCELlent !!  Thank you very much.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
    Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
    The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
    This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    26 Experts available now in Live!

    Get 1:1 Help Now