Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: Cell Formatting/Now what am I doing wrong?

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Posts
    119
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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????????????

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Florida
    Posts
    82
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Posts
    119
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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!!!!

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Florida
    Posts
    82
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Posts
    119
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  6. #6
    Board Regular
    Join Date
    Mar 2002
    Location
    Sydney/Brisbane , Australia
    Posts
    542
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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%


  7. #7
    Board Regular
    Join Date
    Feb 2002
    Location
    Perth Australia
    Posts
    1,584
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  8. #8
    Board Regular
    Join Date
    Mar 2002
    Posts
    119
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •