Input Box not working in VBA

[some_evil]

hi Guys,

I am trying to play with vbs again, and what I am trying to achieve is that a pop up box appears when I run the macro, the user enters some text (a name or something) and then the macro fills this name down the sheet.

I have the line below it (commented out below) working fine, but I am trying to get a little fancier and I keep getting an error 'Compile Error: Object Required'.

Can someone see where I am going wrong in the below code??
Thanks

Sub SewerStructures_InsertFormula_AssetDescription()
     With ActiveSheet
     Dim inputData As String
     Set inputData = Application.InputBox("Enter Name:", "Collect User Input", Type:=2)
        .Range("B2:B" & Range("F" & Rows.Count).End(xlUp).Row).Formula = inputData

'       .Range("B2:B" & Range("F" & Rows.Count).End(xlUp).Row).Formula = "=G2&IF(I2="""","""","" - Manhole # ""&I2)&IF(N2="""","""","" - ""&N2)"


    End With
End Sub

 

[Rick Rothstein]

hi Guys,

I am trying to play with vbs again, and what I am trying to achieve is that a pop up box appears when I run the macro, the user enters some text (a name or something) and then the macro fills this name down the sheet.

I have the line below it (commented out below) working fine, but I am trying to get a little fancier and I keep getting an error 'Compile Error: Object Required'.

Can someone see where I am going wrong in the below code??
Thanks

Sub SewerStructures_InsertFormula_AssetDescription()
     With ActiveSheet
     Dim inputData As String
     [COLOR=#FF0000][B]Set [/B][/COLOR]inputData = Application.InputBox("Enter Name:", "Collect User Input", Type:=2)
        .Range("B2:B" & Range("F" & Rows.Count).End(xlUp).Row).Formula = inputData

'       .Range("B2:B" & Range("F" & Rows.Count).End(xlUp).Row).Formula = "=G2&IF(I2="""","""","" - Manhole # ""&I2)&IF(N2="""","""","" - ""&N2)"  
    End With
End Sub

Remove the red highlighted word... you declard inputData as a String variable and the keyword Set is used only with Object type variables.

 

[Br0ck]

Remove the word "Set" and see what happens.

 

[mikerickson]

You are using Set when you should be using Let.

Let inputData = Application.InputBox("Enter Name:", "Collect User Input", Type:=2)

 

[some_evil]

Thanks everyone, removing the 'SET' has fixed it

Cheers