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

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

Error when cell A1 is not active and xlInsideVertical borderformat throwing error 1004
 

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

On Aug 6, 4:20*pm, wrote:
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

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

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.

wrote:

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

--

Dave Peterson

Error when cell A1 is not active and xlInsideVertical borderformat throwing error 1004
 

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.


On Aug 6, 5:04*pm, Dave Peterson wrote:
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.





wrote:

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

--

Dave Peterson- Hide quoted text -

- Show quoted text -

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

Maybe you want:

With Range("A5", DestLast)



wrote:

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.

On Aug 6, 5:04 pm, Dave Peterson wrote:
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.





wrote:

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

--

Dave Peterson- Hide quoted text -

- Show quoted text -

--

Dave Peterson

Error when cell A1 is not active and xlInsideVertical borderformat throwing error 1004
 

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.



On Aug 7, 1:52*pm, Dave Peterson wrote:
Maybe you want:

With Range("A5", DestLast)



wrote:

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.

On Aug 6, 5:04 pm, Dave Peterson wrote:
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.

wrote:

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

--

Dave Peterson- Hide quoted text -

- Show quoted text -

--

Dave Peterson

Error when cell A1 is not active and xlInsideVertical borderformat throwing error 1004
 

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

On Aug 7, 1:52*pm, Dave Peterson wrote:
Maybe you want:

With Range("A5", DestLast)



wrote:

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.

On Aug 6, 5:04 pm, Dave Peterson wrote:
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.

wrote:

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

--

Dave Peterson- Hide quoted text -

- Show quoted text -

--

Dave Peterson

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

So DestLast and DestCols return numbers?

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

should work, too.

wrote:

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

On Aug 7, 1:52 pm, Dave Peterson wrote:
Maybe you want:

With Range("A5", DestLast)



wrote:

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.

On Aug 6, 5:04 pm, Dave Peterson wrote:
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.

wrote:

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

--

Dave Peterson- Hide quoted text -

- Show quoted text -

--

Dave Peterson

--

Dave Peterson