Passing user defined range into function?

[Plumby Baby]

Hi everyone!! :hi:

Can you please tell me what i'm doing wrong in the below code. It all works apart from the last Sub (Sub Macro1(wb2 As Workbook, rngToChange As Range).

This last sub should effect a change by ensuring there is a space 3 spaces from the right. What i'm struggling with is how to pass the user selected range (defined in earlier subs) to this function. Can anyone help? Its twisting my melon man! :banghead:


Sub OpenBookGetRange()

Static wb2Path As String: wb2Path = Application.GetOpenFilename("Excel Files, *.xls*")
If wb2Path = "False" Then Exit Sub

Static wb1 As Workbook: Set wb1 = ActiveWorkbook
Static wb2 As Workbook: Set wb2 = Workbooks.Open(wb2Path)

On Error Resume Next
Static wb2Rng As Range
Set wb2Rng = Application.InputBox(Title:="Select Range", _
Prompt:="Select a range in " & wb2.Name, _
Type:=8)
If wb2Rng Is Nothing Then
MsgBox "No range selected, exiting macro."
wb2.Close False
Exit Sub
End If

NextMacro wb2, wb2Rng


End Sub

Sub NextMacro(wb2 As Workbook, Rng As Range)

Rng.Font.Bold = True
MsgBox "Workbook: " & wb2.Name & Chr(10) & _
"Sheet: " & Rng.Worksheet.Name & Chr(10) & _
"Cells: " & Rng.Address & Chr(10) & Chr(10) & _
"Those cells have been bolded"

Macro1 wb2, Rng

End Sub

Sub Macro1(wb2 As Workbook, rngToChange As Range)
'
' 3 SPACE ALWAYS FROM THE RIGHT
'
rngToChange.FormulaR1C1 = _
"=LEFT(SUBSTITUTE(RC[-1],"" "",""""),LEN(SUBSTITUTE(SUBSTITUTE(RC[-1],"" "",""""),"" "",""""))-3) & "" "" & RIGHT(SUBSTITUTE(RC[-1],"" "",""""),3)"

End Sub

[Bob Phillips]

At a glance it looks fine, what is (not) happening?

[Plumby Baby]

Hi xld,

Basically its putting the function in the selected range rather than applying itself to it. Hope that makes sense!

Thanks for replying.

Georgie Best, oh Georgie, Georgie, Georgie!

[Bob Phillips]

NOt really. I ran the code and it bolded and applied a formula to my selected range. Which is what the code seems to be intended to do.

[Plumby Baby]

Did the formula actually change the text in your range? Its supposed to put a space three characters from the right to whatever in the cell. When I run it, it puts the formula in the range (but doesn't actually apply it) and then i get a value error message.

[Bob Phillips]

You can't have the formula and a value in the same cell.

[Aflatoon]

Cross-posted here (http://www.excelforum.com/excel-programming/793382-passing-user-defined-parameter-onto-next-sub.html), and seemingly solved.