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


S

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
 
Ad

Advertisements

S

sbitaxi

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

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.
 
S

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.
 
D

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.
 
S

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.
 
Ad

Advertisements

S

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
 
Ad

Advertisements

D

Dave Peterson

So DestLast and DestCols return numbers?

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

should work, too.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top