Input Box selects a cell location to use in code

[Guest]

I have the following simple code:

ActiveCell.FormulaR1C1 = "=TRIM(RC[5])&RC[11]"

eg above concatenates the cells 5 & 11 columns along.

What I want is for the user, by way of an input box, to be able to choose
the columns, say 2 & 6.

Can anyone help with this please?

 

[Guest]

This is one way:

firstCol = Application.InputBox("Enter First Column Number", "First Column",
Type:=1)
scndCol = Application.InputBox("Enter Second Column Number", "Second
Column", Type:=1)
ActiveCell.FormulaR1C1 = "=TRIM(RC[firstCol])&RC[scndCol]"

 

[Guest]

Thanks, but I get application-defined of Object-defined error, so do I need
to add something else?

This is one way:

firstCol = Application.InputBox("Enter First Column Number", "First Column",
Type:=1)
scndCol = Application.InputBox("Enter Second Column Number", "Second
Column", Type:=1)
ActiveCell.FormulaR1C1 = "=TRIM(RC[firstCol])&RC[scndCol]"

I have the following simple code:

ActiveCell.FormulaR1C1 = "=TRIM(RC[5])&RC[11]"

eg above concatenates the cells 5 & 11 columns along.

What I want is for the user, by way of an input box, to be able to choose
the columns, say 2 & 6.

Can anyone help with this please?

 

[Dave Peterson]

Option Explicit
Sub testme()
Dim FirstCol As Long
Dim ScndCol As Long

FirstCol = Application.InputBox("Enter First Column Number", _
"First Column", Type:=1)

ScndCol = Application.InputBox("Enter Second Column Number", _
"Second Column ", Type:=1)

ActiveCell.FormulaR1C1 = "=TRIM(RC[" & FirstCol & "])&RC[" & ScndCol & "]"

End Sub

Thanks, but I get application-defined of Object-defined error, so do I need
to add something else?

This is one way:

firstCol = Application.InputBox("Enter First Column Number", "First Column",
Type:=1)
scndCol = Application.InputBox("Enter Second Column Number", "Second
Column", Type:=1)
ActiveCell.FormulaR1C1 = "=TRIM(RC[firstCol])&RC[scndCol]"

I have the following simple code:

ActiveCell.FormulaR1C1 = "=TRIM(RC[5])&RC[11]"

eg above concatenates the cells 5 & 11 columns along.

What I want is for the user, by way of an input box, to be able to choose
the columns, say 2 & 6.

Can anyone help with this please?

 

[Guest]

Sorry about that, Archie. I don't work with Worksheet formulas that much.
Here is the corrected code.

Sub inptbx()
Dim firstCol As Long, scndCol As Long
firstCol = Application.InputBox("Enter First Column Number", "First
Column", Type:=1)
scndCol = Application.InputBox("Enter Second Column Number",
"SecondColumn ", Type:=1)
ActiveCell.FormulaR1C1 = "=TRIM(RC[" & firstCol & "]) & TRIM(RC[" &
scndCol & "])"
End Sub

Thanks, but I get application-defined of Object-defined error, so do I need
to add something else?

This is one way:

firstCol = Application.InputBox("Enter First Column Number", "First Column",
Type:=1)
scndCol = Application.InputBox("Enter Second Column Number", "Second
Column", Type:=1)
ActiveCell.FormulaR1C1 = "=TRIM(RC[firstCol])&RC[scndCol]"

I have the following simple code:

ActiveCell.FormulaR1C1 = "=TRIM(RC[5])&RC[11]"

eg above concatenates the cells 5 & 11 columns along.

What I want is for the user, by way of an input box, to be able to choose
the columns, say 2 & 6.

Can anyone help with this please?

 

[Guest]

Thanks Guys, your help is fantastic.

Sorry about that, Archie. I don't work with Worksheet formulas that much.
Here is the corrected code.

Sub inptbx()
Dim firstCol As Long, scndCol As Long
firstCol = Application.InputBox("Enter First Column Number", "First
Column", Type:=1)
scndCol = Application.InputBox("Enter Second Column Number",
"SecondColumn ", Type:=1)
ActiveCell.FormulaR1C1 = "=TRIM(RC[" & firstCol & "]) & TRIM(RC[" &
scndCol & "])"
End Sub

Thanks, but I get application-defined of Object-defined error, so do I need
to add something else?

This is one way:

firstCol = Application.InputBox("Enter First Column Number", "First Column",
Type:=1)
scndCol = Application.InputBox("Enter Second Column Number", "Second
Column", Type:=1)
ActiveCell.FormulaR1C1 = "=TRIM(RC[firstCol])&RC[scndCol]"


:

I have the following simple code:

ActiveCell.FormulaR1C1 = "=TRIM(RC[5])&RC[11]"

eg above concatenates the cells 5 & 11 columns along.

What I want is for the user, by way of an input box, to be able to choose
the columns, say 2 & 6.

Can anyone help with this please?