Cell Formatting/Now what am I doing wrong?

d-alan

Board Regular
Joined
Mar 26, 2002
Messages
119
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????????????
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
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
 
Upvote 0
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!!!!
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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%
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,563
Messages
6,114,329
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top