Getting range from value of cell

[maxbur123]

Hi guys,

Im looking to use the value of the selected cell, which is a cell reference, as the range.

ie

Range(Range(Selection).Value).End(xlUp).Select

One of the potential issues is that the value is referring to another sheet: 'Front and Input'!G27

Thanks for your help!

[Richard Buttrey]

Would you upload an example of your workbook and explain what range you expect to select with your VBA code.

[maxbur123]

Hi!

I've uploaded a very basic example using apples and pears

What I am aming to do is effectively build a macro that takes the formulas used in finding the various "differences" and
the "totals" and returns in a seperate cell what is is doing in words.

E.g. take away (june) (apples) of (150) from (may)(apples) of (152) to get (may/jun) (apples) of (2)

Where everything in (brackets) has been looked up.

I have unfortunately fallen at the first hurdle though.

I would like to know how i can take a formula from a cell and use the cells used as part of a new reference, which i can then
use .End(xlUp) to get the header (apples) and .End(xlLeft) to get the time (may).

Im thinking maybe using Trim functions could work, but with all the formulas being different, and in my real sheet having
references to cells in colum being Z and 100 might make that no longer work, i dont know though.

As you can probably guess im starting out with vba so maybe ive missed something crucial.

My very basic question is how can i use
Range(Range(Selection).Value), tried putting "" around it and it didnt like that.

Thanks

[Richard Buttrey]

Hi,

One way is the following UDF

First name your Sheet1 range B3:D6 "Data"
Then in G4 enter the first month and in H4 the second month

Then in a cell enter the formula

=Words(G4,H4,C3,"M")

The 4th parameter should be eithet "M" for minus or "P" for plus

See also attached

Function Words(stM1 As String, stM2 As String, stItem As String, stsign As String) As String
    Dim lVal1 As Long, lval2 As Long, x1 As Long, x2 As Long, y As Long, lResult As Long, stText As String, sttext2 As String

    x1 = WorksheetFunction.Match(stM1, Range("Data").Resize(, 1), False)    '.Find(What:=stM1, after:=Range("Data").Cells(1, 1)).Row
    y = WorksheetFunction.Match(stItem, Range("Data").Resize(1), False)
    x2 = WorksheetFunction.Match(stM2, Range("Data").Resize(, 1), False)
    lVal1 = Range("Data").Cells(x2, y)
    lval2 = Range("Data").Cells(x1, y)

    Select Case stsign
    Case Is = "M"
        lResult = lval2 - lVal1
        stText = "Take away "
        sttext2 = " from "
    Case Is = "P"
        lResult = lval2 + lVal1
        stText = "Add "
        sttext2 = " to "


    End Select


    Words = stText & stM2 & " " & stItem & " of " & lVal1 & sttext2 & stM1 & " " & stItem & " of " _
    & lval2 & " to get " & stM1 & "/" & stM2 & " " & stItem & " of " & lResult

End Function

[maxbur123]

Thanks a bunch for that! It won't work exactly for my real sheet as it is slightly omore complicated, but has definitely helped me know how to go about it!

How would you edit your forumla so that I can use =Words from another sheet, and it still refer to Data from sheet 1.

And just going back to my first question:
If i imput on sheet 2 say cell A1 (but theoretically any cell as I will select it before running macro): Sheet1!C5
How can I use an equivalent of Range(Selection.Value).Select, which I want to take whatever cell i have written in the selected cell(in this case, selecting A1 in sheet 2, and therefore referring to Sheet1!C5) and then select that cell instead?

Thanks again!