0

All I'm trying to do is get a cell's value from a different sheet. In the debugger as soon as I step over the first line I get a value error.

From a Sheet2 cell I'm calling the function =getStr("20") and am getting a #VALUE! error. I had the parameter as an Integer passing 20 and still had the error.

I'm able to call other functions in my Module1 module???

enter image description here

K.Dᴀᴠɪs
  • 9,945
  • 11
  • 33
  • 43
Steve
  • 1
  • 1
    While an image of the VBE is useful in this case, you should not expect people volunteering their time to assist you to retype your code. The relevant code should have been added to the question in a code block. –  Feb 11 '18 at 01:05
  • Additional information [here](https://stackoverflow.com/questions/30638716/excel-vba-set-multiple-cells-to-the-same-value/30639510#30639510) and [here](https://stackoverflow.com/questions/39800873/how-do-i-pass-worksheet-and-ranges-as-variables/39801078#39801078) and [here](https://stackoverflow.com/questions/37850017/loop-files-onto-master-sheet-but-data-keeps-overwriting-itself/37850206?s=4|46.0881#37850206), etc. –  Feb 11 '18 at 02:41

3 Answers3

3

You are confusing the worksheet name property and the worksheet codename property.

The name is Summary. The codename is Sheet1.

You also should not be using .Select. See How to avoid using Select in Excel VBA.

  • 1
    Additionally, A52:J52 does not have 20 columns so you never be able to retrieve a legitimate value in any event. –  Feb 11 '18 at 05:38
2

Jeeped already told you where the flaw of your code was

here's a possible refactoring of your code (explanations in comments):

Public Function getStr(iCol As Integer) As String
    With Worksheets("Summary").Range("A52:J52") ' reference relevant sheet range (change "Summary" and range address to suit your needs)
        If iCol <= .Columns.Count Then 'check if the passed column index fits the referenced range column number
            getStr = .Cells(1, iCol).Value 'if so, then return the requested value
        Else
            gestr = "column index out of range!" ' otherwise inform the user she input the wrong column index
        End If
    End With
End Function
DisplayName
  • 13,283
  • 2
  • 11
  • 19
0

What about this?

Public Function getStr(iCol As Integer) As String
    Dim rng As Range
    Dim val As String
    Dim ws As Worksheet
    Set ws = Worksheets("Sheet1")
    ws.Activate  'Activate the sheet before selecting a cell if another sheet is currently active
    ws.Range("A1").Select 'not required if you want to get a value from a cell
    Set rng = ws.Range("A52:J52") 'Correct way to set a range
    'val = rng.Cells(1, iCol).Value 'Doesn't make sense as rng has only 10 columns in it
    val = ws.Cells(1, iCol).Value  'Maybe you need this?
    getStr = val
End Function
Subodh Tiwari sktneer
  • 9,906
  • 2
  • 18
  • 22