Cell Formatting/Now what am I doing wrong?

[d-alan]

Column B:D are merged for each row.
I have a formula in b16 that I want to copy to B17:b43 using a macro.
I use the following code:
Range("b16").Select
ActiveCell = ("= VLOOKUP(E15,[logs.xls]salesloghidden!$F:$H,3,FALSE)")
ActiveCell.Copy
Range("b16:b43").PasteSpecial (xlPasteFormulas)
This works except for:
row 32 to 41 it is pasting the formula in two cells even though they remain merged.
41 to 43 it is pasting in all three cells even though they remain merged.
Visibly you can see the info overlapping in the merged cells, but if you select itthe cells act as one and retain the merged property.
Any ideas????????????

[kinkyparamour]

I have had problems like that before and my solution was to go to edit,clear, and all. Then reformat the cells as needed. I know there has to be a better cure but this worked for me.........

Denny

[ This Message was edited by: kinkyparamour on 2002-04-01 18:19 ]

[d-alan]

I just went edit>cell>format>clear
and it seems to have worked for the pasting portion.
I stopped the macro right after this event and in merged "copy cell" is highlighting only the B:column -portion of the merged cell.
So that is still not working.
And the real problem is that I am using the following code later in the macro
For Each cell In Range("qty")'the merged B:D column
If cell.Text = "#N/A" Then
cell.Value = ""
End If
next cell
This is taking almost 10 seconds to complete, I have a feeling it is looking in B then c then d making the macro 3 times longer than it needs to be.
Ahhhhhhhh,
another computer is about to defy gravity!!!!

[kinkyparamour]

Before you kill a perfectly good computer why don't you post your code and let one of these guys look at it?. I know nothing about VBA so i can't help you there but these guys are pretty sharp and always willing to help......

Denny

[d-alan]

Thanks Denny,

I am not sure about the perfectly good computer, (or user for that matter!)
But I do agree with you about these guys on this forum. Thanks in advance.

Here is the entire code:
Sub saleload()
Application.ScreenUpdating = False
Workbooks("logs.xls").Activate
Sheets("salesloghidden").Select
Cells.ClearContents
Sheets("saleslog").Select
Range("salesloginfo").Select
Selection.AutoFilter Field:=2, Criteria1:=sales2.satim2.Text'textbox mm:dd:yy hh:mm:ss format
Selection.Copy
Workbooks("logs.xls").Activate
Sheets("salesloghidden").Select
Range("a1").PasteSpecial
Application.CutCopyMode = False
ActiveWorkbook.Names.Add Name:="salesinfo", RefersTo:=Selection
Workbooks("display-forms.xls").Activate
Worksheets("orderform").Select
Range("F10").FormulaR1C1 = "=[logs.xls]saleslog!R6C5"
Range("q6").FormulaR1C1 = "=[logs.xls]saleslog!R6C1"
Range("p1").FormulaR1C1 = "=[logs.xls]saleslog!R6C2"
Range("n11").FormulaR1C1 = "=[logs.xls]saleslog!R6C12"
Range("E11").FormulaR1C1 = "=[logs.xls]saleslog!R6C18"
Range("e7").FormulaR1C1 = "=[logs.xls]saleslog!R6C4"
Range("d8").FormulaR1C1 = "=[logs.xls]saleslog!R6C19"
Range("l47").FormulaR1C1 = "=[logs.xls]saleslog!r6c11"
Range("c9").FormulaR1C1 = ("=VLOOKUP('display-forms.xls'!adgal1,'info-sheets.xls'!adeal,3,FALSE)")
Range("j9").FormulaR1C1 = ("=VLOOKUP('display-forms.xls'!adgal1,'info-sheets.xls'!adeal,4,FALSE)")
Range("k9").FormulaR1C1 = ("=VLOOKUP('display-forms.xls'!adgal1,'info-sheets.xls'!adeal,5,FALSE)")
Range("j7") = ("=VLOOKUP('display-forms.xls'!dealerg,'info-sheets.xls'!deal,(MATCH(allo,'[info-sheets.xls]Dealer'!$1:$1,0)),FALSE)")
Range("n7").Value = Range("dealerg").Text
Range("n8").Value = Range("d8").Text
Range("n9").Value = Range("c9").Text + " , " + Range("j9").Text + " " + Range("k9").Text
Range("b15").Select
ActiveCell = ("= VLOOKUP(E15,[logs.xls]salesloghidden!$F:$H,3,FALSE)")
ActiveCell.Copy
Range("b16:b43").PasteSpecial (xlPasteFormulas)
For Each cell In Range("qty")'b:d merged columns
If cell.Text = "#N/A" Then
cell.Value = ""
End If
Next cell
Application.ScreenUpdating = True
End Sub

[Qroozn]

Excuse me if this is belittling but

Have you already got application.screenupdating = false
and
application.screenupdating = True

It is a major time saver for my macros and can cut their time by 90%

[Derek]

Hi
With your original post, have you tried code like:
Range("B16:D16").Select
Selection.AutoFill Destination:=Range("B16:D43"), Type:=xlFillDefault
Range("B16:D43").Select

regards
Derek

[d-alan]

Thanks-Derek,
Selection.AutoFill Destination definetly seemed to help. I am not familar with method and am curious to know where the best applications for it should be used.
I also removed the 'for each....next loop which deleted #N/A and referenced each cell individually. I gave up space but it has increased the speed. From 10 seconds it is now down around 2, and from the other macros I have running it seems about right.
Thanks all for your help.