View Full Version : Solved: With-End With problem
YellowLabPro
10-07-2007, 09:29 AM
I changed this
wsTarget.Range("A4:T" & lrTarget).Delete
wsTarget.Range("A4:T" & lrTarget).Value = wsSource.Range("A4:T" & lrSource).Value
wsTarget.Range("A4:T" & lrTarget)..Font.Bold = False
To this
With wsTarget.Range("A4:T" & lrTarget)
.Delete
.Value = wsSource.Range("A4:T" & lrSource).Value
.Font.Bold = False
End With
But this line
.Value = wsSource.Range("A4:T" & lrSource).Value
is giving me an error-
run time 424
Object required
Any ideas why this is not a valid block?
Norie
10-07-2007, 09:40 AM
Doug
You're deleting the range object immediately before you try and use it's value property.
YellowLabPro
10-07-2007, 09:50 AM
Norie,
I dont think so. But I say this cautiously....
Let me repost.
I am deleting the Target, then telling it to use the Source sheet.
This works
With wsTarget
.Range("A4:T" & lrTarget).Delete
.Range("A4:T" & lrSource).Value = wsSource.Range("A4:T" & lrSource).Value
.Range("A4:T" & lrSource).Font.Bold = False
End With
This does not work
With wsTarget.Range("A4:T" & lrTarget)
.Delete
.Value = wsSource.Range("A4:T" & lrSource).Value
.Font.Bold = False
End With
Norie
10-07-2007, 09:54 AM
No you are deleting the range object.:)
That might not be technically the right phrase but I suggest you try stepping throughh this code and add a watch to rng.
Set rng = wsTarget.Range("A4:T" & lrTarget)
With rng
.Delete
.Value = wsSource.Range("A4:T" & lrSource).Value
.Font.Bold = False
End With
YellowLabPro
10-07-2007, 10:17 AM
I will try that, thanks Norie
Bob Phillips
10-07-2007, 10:45 AM
When you delete a cell, if you are pointing at that cell your reference is lost.
This codes resets the pointer to the new cell that now occupies the position of the deleted cell
With wsTarget
.Range("A4:T" & lrTarget).Delete
.Range("A4:T" & lrSource).Value = wsSource.Range("A4:T" & lrSource).Value
.Range("A4:T" & lrSource).Font.Bold = False
End With
whereas this code has not been reset (because the cell is included in the With), so itsi trying to pibt at the cell the was deleted, which is invalid
With wsTarget .Range("A4:T" & lrTarget).Delete
.Range("A4:T" & lrSource).Value = wsSource.Range("A4:T" & lrSource).Value
.Range("A4:T" & lrSource).Font.Bold = False
End With
YellowLabPro
10-07-2007, 11:03 AM
Bob,
Possible you pasted the same line of code in the two examples? I see no difference between the two lines of code.
Bob Phillips
10-07-2007, 11:34 AM
It got screwed up, look at it now I have corrected it.
YellowLabPro
10-07-2007, 11:46 AM
ok.... got it
gracias
mdmackillop
10-07-2007, 01:41 PM
Instead of Delete, use ClearContents
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.