Hi everyone,
I'm fairly new to VB and am trying to incorporate the following subs...
Sub Macro2()
'
' COPY AND PASTE SPECIAL VALUES
'
Columns("B:B").Select
Selection.Copy
Range("C1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.Replace What:="#VALUE!", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Columns("B:B").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Range("C1").Select
End Sub
Sub Macro3()
'
' REPLACE 'O'S WITH ZEROS
'
Columns("B:B").Select
Selection.Replace What:="o", Replacement:="0", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub
Sub Macro4()
'
' CONFIRMS WHETHER POSTCODES ARE WITHIN PDCS LIST
'
Range("C1").Select
ActiveCell.FormulaR1C1 = _
"=IF(COUNTIF(PDCS!R2C1:R33679C1,Sheet1!RC[-1]),""Yes"", ""No"")"
Range("c1:c" & Range("b" & Rows.Count).End(xlUp).Row).Select
Selection.FillDown
End Sub
...Into the below code. I've tried to understand how the Sub NextMacro works but I've failed big time. Basically I need all the above subs to refer to the range that the user defines in the macro below.
Sorry, this is probably asking too much. Maybe just an explanation of how one of them is done so i can learn for myself. Its hair pulling!!
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)
Dim arrVals As Variant: arrVals = Rng.Value
Dim strTemp As String
Dim r As Long, c As Long
For Each arrVals In wb2
wb2.Cells.Replace What:="o", Replacement:="0", _
LookAt:=xlPart, MatchCase:=False
Rng.Value = arrVals
Next Macro22
End Sub
Bookmarks