entering row and col for a cell in vba

[lezawang]

Hi
Is it possible to enter both row, col in the inputbox at same time. Please see the code below. I have to ask user to enter row, col 4 times. I was thinking if I can ask user only 2 times to enter row and col of each cell at same time. Thank you

' cell merge
Sub cellmerge()
Dim x As Integer
Dim y As Integer
Dim n As Integer
Dim m As Integer
x = InputBox("first cell row")
y = InputBox("first cell col")
n = InputBox("second cell row")
m = InputBox("second cell col")
ThisWorkbook.ActiveSheet.Range(Cells(x, y), Cells(n, m)).Merge
End Sub

ps: I used indent but i lose it when i save this post!

 

[Eric W]

You could parse the result of the InputBox like this:

' cell merge
Sub cellmerge()
Dim x As Long, y As Long, n As Long, m As Long
Dim inpt As String, splt As Variant

    inpt = InputBox("Enter row,column:")
    splt = Split(inpt, ",")
    x = splt(0)
    y = splt(1)
    inpt = InputBox("Enter second row,column:")
    splt = Split(inpt, ",")
    n = splt(0)
    m = splt(1)
    ThisWorkbook.ActiveSheet.Range(Cells(x, y), Cells(n, m)).Merge
End Sub

To maintain indentation, use code tags. Like this:

[ code]

... code goes here ...

[ /code]

Remove the spaces from the codes though. Or you could just select your entire code and click the # button from the menu, which will automatically insert them.

 

[Fluff]

Have a look at

Application.InputBox

Also when posting code please use code tags, the # icon in the reply window

 

[Rick Rothstein]

Hi
Is it possible to enter both row, col in the inputbox at same time. Please see the code below. I have to ask user to enter row, col 4 times. I was thinking if I can ask user only 2 times to enter row and col of each cell at same time. Thank you

' cell merge
Sub cellmerge()
Dim x As Integer
Dim y As Integer
Dim n As Integer
Dim m As Integer
x = InputBox("first cell row")
y = InputBox("first cell col")
n = InputBox("second cell row")
m = InputBox("second cell col")
ThisWorkbook.ActiveSheet.Range(Cells(x, y), Cells(n, m)).Merge
End Sub

If you use this code, the user can input the range normally (for example, like this C4:K9) or, alternately, they can simply select the range just like selecting any range (mostly likely using the mouse, but using keystrokes is also possible).

ps: I used indent but i lose it when i save this post!

If you select the code and then click the # symbol in the ribbon at the top, it will automatically place code tags around the selected code and your spacing will be preserved.

 

[Rick Rothstein]

If you use this code, the user can input the range normally (for example, like this C4:K9) or, alternately, they can simply select the range just like selecting any range (mostly likely using the mouse, but using keystrokes is also possible).

Hmm! Looks like I had forgotten to post the code line

Application.InputBox("Either enter or select the range to merge", Type:=8).Merge