Help With Selecting Common Column in Multiple Selections

Thanks for the reply.

I can't get that to work in my workbook, copied to a blank one and all looks good. I beleive its as I have some other data selected prior to your code. I'll have a tinker around.

Thanks

I can't get it to work with my macro, v strange.

I originally select rRange at the beginning of the macro

To reselect just before your code I should insert

Range(rRange).Select

right? Only I get a "method range of object global failed" message

Any ideas?

Presumably, you are selecting several cells, some of which are in Column("L"), before you run the code.

Please post your entire code here and let us know what steps you take before running it. And if it is not a "Selection_Change" routine, how you start the routine.

OK, After getting the right range to be selected prior to that code, it now doesn't appear to do anything, would hidden cells, grouped columns affect it? I am assuming that something in my setup has got in the way as no formulas are changed, and no cells turned bold?

Here is the whole code....

Sub Sum_Rates()
'
' Sum Cumulative Rates Macro
'
' Copy and Sort
Dim rRange As Range
Dim dRange As Range
Dim fRange() As Range
Dim dValue As Integer

Application.DisplayAlerts = False
Set rRange = Application.InputBox(Prompt:= _
"Please select a range. This will calculate the cumulative quantity banding for the selected range", _
Title:="Input Range", Type:=8)
On Error GoTo 0
Application.DisplayAlerts = True

If rRange Is Nothing Then
Exit Sub
Else

Sheets("Summary").Visible = True

rRange.Copy
ActiveWorkbook.Worksheets("Summary").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Range("A1:Q1049").Select

ActiveWorkbook.Worksheets("Summary").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Summary").Sort.SortFields.Add Key:=Range("A1:A1049" _
), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

With ActiveWorkbook.Worksheets("Summary").Sort
.SetRange Range("A1:Q1049")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

'Remove Duplicates
Columns("B:J").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Columns("C:J").Select
Selection.Delete Shift:=xlToLeft

Sheets("Summary").Select
Col = Range("A1").Column
Columns(Col).Select
Selection.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("D1"), Unique:=True


'Sum IF
Range("E1").Formula = "=SUMIF(Summary!D:D,""=""&D1,Summary!B:B)"
Range("E1").Select
Selection.AutoFill Destination:=Range("E1:E" & Range("D1").End(xlDown).Row)

Range("F1").Formula = "=ROUND($A1,0)"
Range("F1").Select
Selection.AutoFill Destination:=Range("F1:F" & Range("D1").End(xlDown).Row)

Range("G1").Formula = "=VLOOKUP($F1,Column!$A$2:$E$35,3,TRUE)"
Range("G1").Select
Selection.AutoFill Destination:=Range("G1:G" & Range("D1").End(xlDown).Row)

Range("H1").Formula = "=VLOOKUP($F1,Column!$A$2:$E$35,4,TRUE)"
Range("H1").Select
Selection.AutoFill Destination:=Range("H1:H" & Range("D1").End(xlDown).Row)

Range("I1").Formula = "=VLOOKUP($F1,Column!$A$2:$E$35,5,TRUE)"
Range("I1").Select
Selection.AutoFill Destination:=Range("I1:I" & Range("D1").End(xlDown).Row)

Range("J1").Formula = "=VLOOKUP($D1,'Schedule items'!$A$1:$H$9000,IF(ABS(SUM($E1))<$G1,4,IF(ABS($E1)<$H1,5,IF(ABS($E1)<$I1,6,7))),FALSE)"
Range("J1").Select
Selection.AutoFill Destination:=Range("J1:J" & Range("D1").End(xlDown).Row)

Sheets("Summary").Visible = False

Calculate



' Macro
Sheets("Front Sheet").Select
rRange.Font.Bold = False
Dim Cel As Range
For Each Cel In Selection
If Not Intersect(Cel, Columns(12)) Is Nothing Then
Cel.Formula = "=VLOOKUP($A" & dValue & ",'Summary'!$D$1:$J$1500,7,TRUE)"
Cel.Font.Bold = True
End If
Next Cel


'END
End If

End Sub

The data being selected by rRange is basically

A106:Q132,A140:Q149

But could vary

I think it is probably down to how I have re-selected the data to be used from the reformatting of column 12.

SamTYler said:

Presumably, you are selecting several cells, some of which are in Column("L"), before you run the code.

Please post your entire code here and let us know what steps you take before running it. And if it is not a "Selection_Change" routine, how you start the routine.

Click to expand...

OK I had forgotten to select the correct sheet. I get it to work now, only I need the formula to effectively autofill rather than just paste the same formula to each cell, is this possible? Obviously there are mutiple selections so the autofill would have to be applied to each individually varying the start cell for each!