+ Reply to Thread
Results 1 to 5 of 5

How to extract column and row into a variable?

Hybrid View

  1. #1
    Registered User
    Join Date
    09-10-2020
    Location
    worcester
    MS-Off Ver
    2016
    Posts
    43

    How to extract column and row into a variable?

    Good Afternoon,
    I try to find an answer to my issue
    but nothing yet so here i am again
    this is the code:
    -------------------------------
     Sub blabla()
       Dim something  As String
       Dim Msomething As Range
       Dim r ,l  As Double
       something = ComboBox1.Value    'when i choose one value from combobox
       Set Msomething = Worksheets("Users").Range("A:A").Find(what:=something, LookIn:=xlFormulas)
       MsgBox Msomething.Address(False, False)
    End Sub
    --------------------------------
    The result is A3

    well i need to get(extract) in var r the column(A)
    and in var l(3) the row

    finaly to have something like
    cells(r,l)
    or
    r=A
    l=3


    Thank you.
    Last edited by cristiallx; 12-07-2020 at 05:20 PM.

  2. #2
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: How to extract column and row into a variable?

    Administrative Note:

    Welcome to the forum.

    We would very much like to help you with your query, however you need to include code tags around your code.

    Please take a moment to add the tags. Posting code between tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

    Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

    (Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)
    My General Rules if you want my help. Not aimed at any person in particular:

    1. Please Make Requests not demands, none of us get paid here.

    2. Check back on your post regularly. I will not return to a post after 4 days.
    If it is not important to you then it definitely is not important to me.

  3. #3
    Forum Expert millz's Avatar
    Join Date
    08-14-2013
    Location
    Singapore
    MS-Off Ver
    Excel, Access 2016
    Posts
    1,694

    Re: How to extract column and row into a variable?

    If you're going to use Cells, first parameter is row number, followed by column number/letter.
     Sub blabla()
       Dim something  As String
       Dim Msomething As Range
       Dim r ,l  As Long
       something = ComboBox1.Value    'when i choose one value from combobox
       Set Msomething = Worksheets("Users").Range("A:A").Find(what:=something, LookIn:=xlFormulas)
       r = Msomething.row
       l = Msomething.column
       MsgBox Cells(r, l).Address(False, False) & "  ||  " & Cells(r, l).Value
    End Sub
    多么想要告诉你 我好喜欢你

  4. #4
    Registered User
    Join Date
    09-10-2020
    Location
    worcester
    MS-Off Ver
    2016
    Posts
    43

    Re: How to extract column and row into a variable?

    Quote Originally Posted by millz View Post
    If you're going to use Cells, first parameter is row number, followed by column number/letter.
     Sub blabla()
       Dim something  As String
       Dim Msomething As Range
       Dim r ,l  As Long
       something = ComboBox1.Value    'when i choose one value from combobox
       Set Msomething = Worksheets("Users").Range("A:A").Find(what:=something, LookIn:=xlFormulas)
       r = Msomething.row
       l = Msomething.column
       MsgBox Cells(r, l).Address(False, False) & "  ||  " & Cells(r, l).Value
    End Sub
    thank you very much
    was so simple lol
    thank very much again

  5. #5
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: How to extract column and row into a variable?

    Set Msomething = Worksheets("Users").Range("A:A").Find(what:=something, LookIn:=xlFormulas)
    If Msomething = something then
    A = Split(Msomething.address,"$")
    
    'Column as Letter(s) eg AA
    CL = A(1)
    
    'Column as Number eg 27
    CN = Msomething.Column
    
    'Row as Number
    R = A(2)
    
    'Or
    R = Msomething.Row
    
    
    End If
    Or you could try

    Set Msomething = Worksheets("Users").Range("A:A").Find(what:=something, LookIn:=xlFormulas)
    If Msomething = something then
    A = Split(Msomething.address,"$")
    
    'Column as Letter(s) eg AA
    CL = Split(Msomething.address,"$")(1)
    
    'Column as Number eg 27
    CN = Msomething.Column
    
    'Row as Number
    R = Msomething.Row
    
    End If
    Last edited by mehmetcik; 12-07-2020 at 10:24 PM.

+ 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. [SOLVED] Formula to sum variable number of values in a column variable by date lookup ...
    By SNP529 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-18-2020, 06:54 AM
  2. Replies: 6
    Last Post: 09-30-2020, 09:42 PM
  3. VBA: Extract variable number of rows per variable number of phone numbers
    By redstone2830 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-17-2018, 09:22 PM
  4. [SOLVED] How to extract a piece of a string variable, and assign to a new variable
    By jmccoughlin in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-04-2015, 11:50 PM
  5. Extract First word from the variable
    By sriku in forum Excel General
    Replies: 3
    Last Post: 05-12-2014, 01:46 PM
  6. Extract position from variable
    By borgeklungerbo in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 04-21-2012, 12:01 AM
  7. Extract last name into a variable using VBA code
    By mikeburg in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 01-22-2006, 04:15 PM

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