get value from cell in a range
Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 22

Thread: get value from cell in a range
Thanks Thanks: 0 Likes Likes: 0

  1. #11
    Board Regular
    Join Date
    Dec 2018
    Location
    Poland
    Posts
    203
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    1 Thread(s)

    Default Re: get value from cell in a range

    Hi,
    I recommend you upload your file with test data on dropbox in order to have a better overview of what you have and check how to help you. What do you reckon?

  2. #12
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,487
    Post Thanks / Like
    Mentioned
    86 Post(s)
    Tagged
    19 Thread(s)

    Default Re: get value from cell in a range

    Quote Originally Posted by trimmer69 View Post
    Peter, when I copied and pasted into cell outside of range I got 1.
    Hmm, that seems strange. Could you either do what Mento82 has suggested or when you get the #REF ! error (with no merged cells), tell me ..

    - what cell the only text is in
    - what that text is
    - what these two formulas return when placed in cells outside the H2:P4 range.
    =SUMPRODUCT((H2:P4<>"")*ROW(H2:P4))-ROW(2:2)+1
    =SUMPRODUCT((H2:P4<>"")*COLUMN(H2:P4))-COLUMN(H:H)+1
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  3. #13
    Board Regular
    Join Date
    May 2004
    Posts
    397
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: get value from cell in a range

    Formula 1 returned 4, formula 2 returned 17. The text is BARGE "KIRBY 28180" currently in L2, but on different worksheets it could be in M2. The worksheets are different since it is from multiple vendors. When I look for blank cells using the f5 function and special selecting blanks it indicates that a cell L3 could contain a space since it was not shaded

  4. #14
    Board Regular
    Join Date
    May 2004
    Posts
    397
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: get value from cell in a range

    Mentor, I tried your function again and get error message #Value !. I tried to narrow the range down to just two cells L2:L3 and got the same error

  5. #15
    Board Regular
    Join Date
    Dec 2018
    Location
    Poland
    Posts
    203
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    1 Thread(s)

    Default Re: get value from cell in a range

    Hi,
    I just found out I made a mistake in the own developed function I provided you with. Here's the function fixed. Pls check it now
    Code:
    Function GetId(IdRange as range) as string
       Dim rCell as range
       For each rCell in IdRange
           If isnumeric(rCell.value)=false then
                GetId = cstr(rCell.value)
                Exit for
           End if
       Next
    
    End function

  6. #16
    Board Regular
    Join Date
    Dec 2018
    Location
    Poland
    Posts
    203
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    1 Thread(s)

    Default Re: get value from cell in a range

    Hi,
    You can also try this own-developed function which is the previous one extended by verification if.for.some reason value in the provided range is not a date format too.
    Code:
    Function GetId(IdRange as range) as string
       Dim rCell as range
       For each rCell in IdRange
           If IsNumeric(rCell.value)=false and IsDate(rCell.value)=false then
                GetId = cstr(rCell.value)
                Exit for
           End if
       Next
    
    End function

  7. #17
    Board Regular
    Join Date
    May 2004
    Posts
    397
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: get value from cell in a range

    Mentor the last one worked, is there way to hard code the range into function? I will using this in a workbook loop on about 13k workbooks

  8. #18
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,487
    Post Thanks / Like
    Mentioned
    86 Post(s)
    Tagged
    19 Thread(s)

    Default Re: get value from cell in a range

    Quote Originally Posted by trimmer69 View Post
    Mentor the last one worked, is there way to hard code the range into function? I will using this in a workbook loop on about 13k workbooks
    Firstly, given the above, would you still be interested in a standard formula solution?

    If so, let's consider this
    Quote Originally Posted by trimmer69 View Post
    Formula 1 returned 4, formula 2 returned 17. The text is BARGE "KIRBY 28180" currently in L2, but on different worksheets it could be in M2. The worksheets are different since it is from multiple vendors. When I look for blank cells using the f5 function and special selecting blanks it indicates that a cell L3 could contain a space since it was not shaded
    If L3 does in fact contain a space (or spaces) then that would mean that "KIRBY 28180" is not the only text in the range and would therefore account for the #REF error because my formula was only expecting to find one thing in the range. So when it found 2 things, the first extra formula that I gave you in post 12 returned 4 and so my main formula was looking for the required value in row 4 of the H2:P4 range. Clearly that is not possible since that range only has 3 rows, hence the error. Similar with the other formula returning 17 when the H2:P4 range does not contain 17 columns.

    However, if there is a space in L3 then the =COUNTIF(H2:P4,"?*") formula that I gave you in post 9 would not return 1 as you said in post 10. Perhaps you had a different data set then?

    If you still have that data with "something" that is not visible in L3, what do these formulas return?
    =CODE(L3)
    =LEN(L3)


    Would you "identification numbers" have a minimum length? That is, can we say that every identification number is at least 4 characters long? Or some other number?
    If so, and any other stray cells containing spaces or whatever are less than 4 characters, then you could try this version

    =INDEX(H2:P4,SUMPRODUCT((LEN(H2:P4)>3)*ROW(H2:P4))-ROW(2:2)+1,SUMPRODUCT((LEN(H2:P4)>3)*COLUMN(H2:P4))-COLUMN(H:H)+1)


    Also, if you still have that data set with the "something" in L3, what happens if you delete "KIRBY 28180" from L2 and type "KIRBY 28180" into cell L4 and run Mentor's function on that data?

    Finally, note that your idea of merging the whole range will fail if the identification number is in a row below or on the same row to the right of a cell like your L3.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  9. #19
    Board Regular
    Join Date
    May 2004
    Posts
    397
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: get value from cell in a range

    Peter, yes formula would be great, =CODE(L3) returned 34,=LEN(L3) returned 1. The identification numbers range from 3 to 35(raw data)
    "Also, if you still have that data set with the "something" in L3, what happens if you delete "KIRBY 28180" from L2 and type "KIRBY 28180" into cell L4 and run Mentor's function on that data?" - no value returned

  10. #20
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,487
    Post Thanks / Like
    Mentioned
    86 Post(s)
    Tagged
    19 Thread(s)

    Default Re: get value from cell in a range

    Quote Originally Posted by trimmer69 View Post
    =CODE(L3) returned 34,=LEN(L3) returned 1.
    That tells us that the is a double quote mark (") in L3. You should be able to see that unless there is formatting to make that mark the same colour as the background, or vice-versa. In any case, we have established that your identification number is (or may) not be the only text in the H2:P4 range.

    Quote Originally Posted by trimmer69 View Post
    "Also, if you still have that data set with the "something" in L3, what happens if you delete "KIRBY 28180" from L2 and type "KIRBY 28180" into cell L4 and run Mentor's function on that data?" - no value returned
    Assuming you have corrected r.Cell to rCell in two places in mentor's function, it should return the double quote mark. In any case it may not do what you want if there is more than one cell with text.

    Quote Originally Posted by trimmer69 View Post
    Peter, yes formula would be great, ...The identification numbers range from 3 to 35(raw data)
    OK, so the formula I suggested in post 18, with this slight modification should work for you - provided that your identification number is the only text 3 characters or more in the range.

    =INDEX(H2:P4,SUMPRODUCT((LEN(H2:P4)>2)*ROW(H2:P4))-ROW(2:2)+1,SUMPRODUCT((LEN(H2:P4)>2)*COLUMN(H2:P4))-COLUMN(H:H)+1)
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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