Center Across Celection

[Dixie77]

Hi All,

I'm hoping someone can point me in the right direction, I have the below code that looks for a number in column A if it finds it then the adjacent cells CentreAcrossSelection. I've found this is much easier than merging the cells.

But I'm getting a run time '91' error message when running?

Public Sub CenterValues()
Dim rng As Range, _
rng1 As Range, _
LR As Long

With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With
LR = Range("A" & Rows.Count).End(xlUp).Row
With Range("A1:A" & LR)
Set rng = .Find("1", LookIn:=xlValues, lookAt:=xlPart)
If Not rng Is Nothing Then
rng1 = rng.Address
Do
rng.Resize(1, 7).HorizontalAlignment = xlCenterAcrossSelection
Set rng = .FindNext(rng)
Loop While Not rng Is Nothing And rng.Address <> rng1
End If
End With
With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
End With
End Sub

Any ideas why this is happening? and any tips on how to correct?

Thanks in advance for looking

Rob

 

[Joe4]

rng and rng1 are Ranges
rng.Address returns a String
You cannot set a Range equal to a String
Try:

Set rng1 = rng

 

[Dixie77]

Hi Joe4,

Thanks for the assist,

I'm now getting an error when it gets to the below line,

Loop While Not rng Is Nothing And rng.Address <> rng1

When I change rng.Address to rng, the code runs but nothing happens?

Any ideas?

Thanks again for the help

Rob

 

[Joe4]

You can try:

[COLOR=#FF0000]rng.Address <> rng1.Address
[/COLOR]

If it is not giving you the expected results, please lay out an actual data example for us to test.

 

[Dixie77]

Hi Joe4,

That has worked a treat, I can't believe it was something as simple as that.



Thanks a lot for the pointers

Regards

Rob