Error when cell A1 is not active and xlInsideVertical border formatthrowing error 1004

[sbitaxi]

Hi:

I am suddenly getting errors from my code in segments that have worked
completely fine since I created it. Now, after adding code at the end
of the macro to format my report, I am getting errors that were never
there before.

It seems to be rejecting a range assignment SrcHdrRng, but it resolves
when I set the active cell to A1. Why does this happen?

Dim SrcHdrRng as Range
Dim MyCell as Range

Set SrcHdrRng = Range("A1:" & (ActiveSheet.Cells(1,
Columns.Count).End(xlToLeft).Address))

Set MyCell = SrcHdrRng.Find(What:="FirstName", _
After:=ActiveCell, LookIn:=xlFormulas, _
Lookat:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext,
MatchCase:=False).EntireColumn

As well, I am getting an error 1004 on the following code when I try
to set the Borders(xlInsideVertical) for the range. Any help is
greatly appreciated.

With Range("A5:" & (ActiveSheet.Cells(1,
Columns.Count).End(xlToLeft).Address) & DestLast)
With .Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
With .Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeBottom)
.LineStyle = xlDouble
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
End With

 

[sbitaxi]

Correction - ignore the Border issue, I realize that I was setting the
range address improperly with contradictory row references.

 

[Dave Peterson]

What's destlast?

With Range("A5:" _
& (ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Address) & DestLast)

And if that range only has one column, you won't be able to add the inside
vertical border.

 

[sbitaxi]

Odd, my reply didn't post.

DestLast is the last row of data in the worksheet, set dynamically
using the LastRow function I obtained from Ron de Bruin.

I guess I don't really understand what the line of code is doing.

 

[Dave Peterson]

Maybe you want:

With Range("A5", DestLast)

Odd, my reply didn't post.

DestLast is the last row of data in the worksheet, set dynamically
using the LastRow function I obtained from Ron de Bruin.

I guess I don't really understand what the line of code is doing.

 

[sbitaxi]

That will only select the range in Column A. I'm trying to get the
last used column as well without hardcoding it.
This nearly does it but doesn't extend all the way across to the last
4 columns for some reason.

Range("A4:" & Cells(DestLast,
DestCols).End(xlToLeft).Address).Offset(1, 0)

DestCols is an incremental count of the columns in the spreadsheet as
they are copied over from the source.

 

[sbitaxi]

Dave, this works:

Range("A4:" & Cells(DestLast, DestCols).Address).Offset(1, 0)

Read the help file on Cells and also realized that the .End(xlLeft)
was overriding the column assignment from DestCols.

Thanks for your help.



Steven

 

[Dave Peterson]

So DestLast and DestCols return numbers?

Range("A4", Cells(DestLast, DestCols)).Offset(1, 0)

should work, too.