Plumby Baby
09-22-2011, 02:46 AM
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
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