PDA

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