1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Can I "find" (or search) a text string & use the column number as my variable ?

Discussion in 'VBA Macros' started by James thunderbolt, Mar 20, 2017 at 6:17 PM.

  1. James thunderbolt

    James thunderbolt New Member

    Messages:
    27
    Say Row 1 Column 8 (A8) has the title "STARTING RANGE".

    Can I use VBA code to search Row 1 for the exact word "STARTING RANGE" and use that column number (8) as my Integer Variable ? If someone starts moving the spreadsheet around I don't want to have to manually change the code every time to use a new column number

    P.S. I can't put a name on the cell (A8) and look for that - which is what I have done previously, as this data set gets exported from an external source (which is not uniform).

    Appreciate any suggestions.
    Thanks
    JT
  2. Marc L

    Marc L Excel Ninja

    Messages:
    2,713

    Hi !

    I often use a named cell …
    Anyway, you can use Find method or MATCH worksheet function.
  3. Monty

    Monty Well-Known Member

    Messages:
    541
    Try This!

    Code (vb):
    Sub test()
    Dim wb As Workbook
    Set wb = ThisWorkbook
    With wb.Sheets("Sheet1")
        Set FindRow = .Range("A:A").Find(What:="STARTING RANGE", LookIn:=xlValues)
        MsgBox FindRow.Address
    End With
    End Sub
     

Share This Page