1

I have the following task:

"Create a subroutine called WherePutMe that asks the user for a row number and column letter then places the 2,2 position of a selection into that cell.

So far I did

Sub Whereputme()
  Dim x as integer, y as string
  x= Inputbox ("Enter a row number")
  y= Inputbox ("Enter a column letter")
  z= selection.range("B2")
End Sub

There is still something missing..how can I link the inputbox inputs with z?

2
  • Can you explain "places the 2,2 position of a selection into that cell"? Commented Mar 29, 2020 at 20:12
  • the question needs more clarification of what a "cell" refers to and what is included in 2,2 position? it will be helpful to as a snippet also show what the output should look like. Commented Mar 30, 2020 at 15:33

3 Answers 3

2

You have End Sub, but no Sub statement.. is this the whole code? What if the user cancels the prompts?

Assuming you're in a standard module (e.g. Module1.bas), you'll want it to have Option Explicit at the top, and then the Sub statement defines the procedure you need to implement - consider making it explicitly Public, too:

Option Explicit

Public Sub WherePutMe()
    'TODO
End Sub

That said "Where put me" is a horrible name for a procedure. Best practice is to use meaningful names that convey the purpose of the code - and since procedures do something, you'll want their names to generally start with a verb.

Unqualified, InputBox is invoking VBA.Interaction.InputBox, a VBA standard library function that prompts the user for a string, and the user can Cancel out of that prompt. When this happens, what you get is a null string pointer that implicitly converts to an empty string - but then, the user could very well enter an empty string and hit Ok, so before we validate the input we first need to know how the prompt was dismissed.

This quickly gets complicated and chaotic. We could abstract this complexity behind a function:

Private Function TryGetUserInput(ByVal prompt As String, ByRef result As String) As Boolean
    result = InputBox(prompt)
    TryGetUserInput = (StrPtr(result) <> 0)
End Function

And now we can do this:

Option Explicit

Public Sub WherePutMe()
    Dim userRowInput As String, userRow As Long, isValid As Boolean
    Do While Not isValid
        If TryGetUserInput("Enter a row number", userRowInput) Then
            'user submitted a value, now validate it
            If IsNumeric(userRowInput) Then
                'looks legit
                userRow = CLng(userRowInput)
                '...but is it?
                isValid = userRow > 0
            End If
        Else
            'user cancelled the prompt
            Exit Sub
        End If

        If Not isValid Then MsgBox "Invalid row number. Please enter a positive integer between 1 and 1,048,576.", vbExclamation
    Loop
    'TODO: get and validate the column letter
End Sub

Avoid code that makes too many assumptions (broken assumption => bug; think of how any given instruction might possibly fail and throw an error that sends everything up in flames), and don't hesitate to break things down into small, specialized procedures and functions - like the above loop would look pretty neat in its own TryGetValidRowNumber function that returns False if the user cancelled out, True otherwise, with a ByRef output parameter holding a Long integer value that the caller could use directly:

Public Sub WherePutMe()
    Dim rowNumber As Long
    If Not TryGetValidRowNumber(rowNumber) Then Exit Sub

    Dim columnLetter As String
    If Not TryGetValidColumnLetter(columnLetter) Then Exit Sub

    Dim targetCell As Range
    Set targetCell = ActiveSheet.Range(columnLetter & rowNumber)

    'TODO consume the targetCell as needed
End Sub
Sign up to request clarification or add additional context in comments.

Comments

0

The issue you are having is you are defining z as something but you are not using it.

By simply removing the z = portion of you code, you are now telling VBA that you want something to happen, not wanting to define a variable.

You may want to do something along the lines of the following where CStr converts the number into a string in case that ever becomes an issue when combining a string and an integer together.

Sub Whereputme()
  Dim x As Integer, y As String
  x = InputBox("Enter a row number")
  y = InputBox("Enter a column letter")
  Cells(2, 2).Value = y & CStr(x)
End Sub

2 Comments

Thx for the explanation..since I am a beginner..still lot to learn
No problem. This helped?
0
Sub WherePutMe()
    Dim x As Integer, y As String, z As String
    Dim str As String

    x = InputBox("Enter a row number")
    y = InputBox("Enter a column letter")
    z = Selection.Range("B2")
    str = y & x

    ActiveSheet.Range(str) = z

End Sub

1 Comment

Code-only answers may solve the problem but they are much more useful if you explain how they solve it. Community requires theory as well as code both to understand your answer fully.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.