Need help tweaking VBA code

[Russk68]

Hi all
I am using this code to fill in numbers in sequential order when double clicking in a cell. So, if number 1 is in F1 and I double click in F10, 2 thru 10 will autofill down.
The issue that I am having is that it is copying the format as well. I would like just the values to copy down.


Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim c As Variant: c = Split(Target.Address, "$")(1)
Select Case c
Case "F"
Cancel = True
Target.End(xlUp).AutoFill Range(Target.End(xlUp), Target), xlFillSeries
End Select
End Sub

Thank you!

Russ

 

[igold]

Gee Russ, I am reading your signature line... As a grateful member since 2006, and if words alone cannot explain how grateful you are, and after 335 posts, how about using Code Tags!

 

[brownbread]

Thanks for that, it's a really nice bit of code. I can't find a neat 'values only' fix - seems like we need a hybrid of xlFillSeries and xlFillValues. So... I suggest this less neat fix to achieve the goal:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim c As Variant: c = Split(Target.Address, "$")(1)
    Select Case c
    Case "F"
    Cancel = True
    For Each cell In Range(Target.End(xlUp), Target)
        cell.Value = cell(0, 1).Value + 1
    Next cell
    End Select
End Sub

 

[Russk68]

So sorry I offended you! I don't know what code tags are.

 

[igold]

Here you go...

https://www.mrexcel.com/forum/board-announcements/515787-how-post-your-vba-code.html

 

[Russk68]

Hi brownhead
Thank you very much for your reply!
After using your code I got a run time err 13 "Type Mismatch"

This line was hilighted:
cell.Value = cell(0, 1).Value + 1

 

[Russk68]

Thank you!

 

[brownbread]

Hi brownhead
Thank you very much for your reply!
After using your code I got a run time err 13 "Type Mismatch"

This line was hilighted:
cell.Value = cell(0, 1).Value + 1

Ah OK... let's do this instead - this will work with text-based entries as well as pure numbers. It uses your original xlFillSeries so the fill is good. But, it takes a "backup" of the cell formats beforehand, and then copies those formats back afterwards... a bit clunky? But quite an efficient way of saving and returning formats:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim c As Variant: c = Split(Target.Address, "$")(1)
    Select Case c
    Case "F"
    Cancel = True
    Columns(7).Insert
    Target.End(xlUp).AutoFill Range(Target.End(xlUp), Target), xlFillSeries
    Range(Target.End(xlUp), Target).Offset(0, 1).Copy
    Range(Target.End(xlUp), Target).PasteSpecial xlPasteFormats
    Columns(7).Delete
    End Select
End Sub

 

[Russk68]

I should have mentioned that I am using Excel on a Mac but not sure if it makes a difference.

The last code freezes Excel. I force quit a few times.

This code that I also use, copies the value down but does not increase by 1 down a column but it doesn't copy borders. Would it be possible to tweak this code to increase a value by 1?

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim c As Variant:    c = Split(Target.Address, "$")(1)
    Select Case c
        Case "F"
            Cancel = True
            Target.End(xlUp).Copy
            Range(Target.End(xlUp), Target).PasteSpecial (xlPasteValues)
    End Select
    End Sub

Thanks!

 

[brownbread]

It works OK for me on Excel for Mac 365, so it's tricky to test, but we can simplify the code a bit. Try this:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Target.Column = 6 Then
        Columns(Target.Column + 1).Insert
        Target.End(xlUp).AutoFill Range(Target.End(xlUp), Target), xlFillSeries
        Range(Target.End(xlUp), Target).Offset(0, 1).Copy
        Range(Target.End(xlUp), Target).PasteSpecial xlPasteFormats
        Columns(Target.Column + 1).Delete
    End If
End Sub

If that still crashes, try adding a 'Stop' line after the 'If Target.Column = 6' line, then run it again, and when it enters debug mode, press CMD+SHIFT+i to step through line-by-line, to see which line is causing our issue.