Please Help with Short Macro
 

What I am trying to do with this macro is when run, it will prompt the user
for a range of cells in excel, once selected it needs to take the range (i.e.
A1-C1) and then format the cells as follows, A1,";",B1",";",C1 all within one
cell. It would also be nice to limit the range to a max of 225 cells. I can
see ALOT of people using this macro so any help would be appreciated. This
is what I have so far and it may not be very helpful, I am trying to combine
2 separate macros, one that gets a user range and one that formats the cells
into the proper format.

Sub GetUserRange()
Dim UserRange As Range

Output = 565
Prompt = "Select a cell for the output."
Title = "Select a cell"

' Display the Input Box
On Error Resume Next
Set UserRange = Application.InputBox( _
Prompt:=Prompt, _
Title:=Title, _
Default:=ActiveCell.Address, _
Type:=8) 'Range selection

' Was the Input Box canceled?
If UserRange Is Nothing Then
MsgBox "Canceled."
Else
UserRange.Range("A1") = Output
End If

(THIS IS WHERE THE MACRO STARTS THAT FORMATS THE CELLS CORRECTLY)
x = 1

Cells(5, 2).Value = ""
While Cells(x, 1).Value < ""
Cells(5, 2).Value = Cells(5, 2) & Replace(Cells(x, 1).Value, " ", "") &
";"
x = x + 1
Cells(4, 3).Value = x - 1
Over_Count = Abs(Cells(4, 5))
Wend
Cells(5, 2).Copy

If x C_Value Then
If Over_Count = 1 Then Vals = " value. " Else Vals = " values. "
UserForm1.Label4 = Over_Count & Vals
UserForm1.Show

Else
UserForm2.Show

End If
Finish:


End Sub

Please Help with Short Macro
 

Maybe you could merge this into your code:

Option Explicit
Sub GetUserRange()
Dim UserRange As Range
Dim Output As Long
Dim myPrompt As String
Dim myTitle As String
Dim myCell As Range
Dim myDelim As String
Dim myString As String

myDelim = ","";"""

Output = 565
myPrompt = "Select a cell for the output."
myTitle = "Select a cell"

' Display the Input Box
Set UserRange = Nothing
On Error Resume Next
Set UserRange = Application.InputBox( _
Prompt:=myPrompt, _
Title:=myTitle, _
Default:=ActiveCell.Address, _
Type:=8) 'Range selection
On Error goto 0

' Was the Input Box canceled?
If UserRange Is Nothing Then
MsgBox "Canceled."
Exit Sub '???
End If

If UserRange.Cells.Count 225 Then
MsgBox "Too many cells!"
Exit Sub
End If

UserRange.Range("A1") = Output

myString = ""
For Each myCell In UserRange.Cells
myString = myString & myDelim & myCell.Value
Next myCell

myString = Mid(myString, Len(myDelim) + 1)

MsgBox myString
End Sub

But you're putting 565 in the first cell. You'll never see that first cell's
value.



zulfer7 wrote:

What I am trying to do with this macro is when run, it will prompt the user
for a range of cells in excel, once selected it needs to take the range (i.e.
A1-C1) and then format the cells as follows, A1,";",B1",";",C1 all within one
cell. It would also be nice to limit the range to a max of 225 cells. I can
see ALOT of people using this macro so any help would be appreciated. This
is what I have so far and it may not be very helpful, I am trying to combine
2 separate macros, one that gets a user range and one that formats the cells
into the proper format.

Sub GetUserRange()
Dim UserRange As Range

Output = 565
Prompt = "Select a cell for the output."
Title = "Select a cell"

' Display the Input Box
On Error Resume Next
Set UserRange = Application.InputBox( _
Prompt:=Prompt, _
Title:=Title, _
Default:=ActiveCell.Address, _
Type:=8) 'Range selection

' Was the Input Box canceled?
If UserRange Is Nothing Then
MsgBox "Canceled."
Else
UserRange.Range("A1") = Output
End If

(THIS IS WHERE THE MACRO STARTS THAT FORMATS THE CELLS CORRECTLY)
x = 1

Cells(5, 2).Value = ""
While Cells(x, 1).Value < ""
Cells(5, 2).Value = Cells(5, 2) & Replace(Cells(x, 1).Value, " ", "") &
";"
x = x + 1
Cells(4, 3).Value = x - 1
Over_Count = Abs(Cells(4, 5))
Wend
Cells(5, 2).Copy

If x C_Value Then
If Over_Count = 1 Then Vals = " value. " Else Vals = " values. "
UserForm1.Label4 = Over_Count & Vals
UserForm1.Show

Else
UserForm2.Show

End If
Finish:

End Sub

--

Dave Peterson