Posts by Smallman

  • Sort data onto worksheets based on value

    Re: Sort data onto worksheets based on value


    As an alternative the following loops through the sheets with the Input sheet on the far left, output sheets cascade to the right.



    File attached to show workings.


    Take care


    Smallman

  • Copy/Paste top 10 visible rows/columns

    Re: Copy/Paste top 10 visible rows/columns


    Hi Ryan


    We meet again?!?


    This gets you over the line but you won't like it.



    Hope this puts you of the mindset that just copying the data in its fullest filtered form and then deleting the rows which exceed row 10 in your destination sheet. This will require less code and no loop.


    Take care


    Smallman

  • Copy and paste dynamic range from one sheet to another

    Re: Copy and paste dynamic range from one sheet to another


    Hi Codepro


    Give the following a try. It kind of builds on my prior post. Hope it helps.


    Code
    Sub MoveVals()
Dim lr As Long
Dim sh As Worksheet
Set sh = Sheet1
lr = sh.Range("A" & Rows.Count).End(xlUp).Row
sh.Range(sh.Cells(lr, 1), sh.Cells(lr, 1).End(xlUp).Offset(-1, 3)).Copy
Sheet2.[a1].PasteSpecial xlPasteValues
End Sub


    Take care


    Smallman

  • Autofill isn't working VBA

    Re: Autofill isn't working VBA


    Hi Codepro


    The end XLup part goes to the very bottom of the specified range, comes up and as soon as it hits a value it is assigned. I used the (2) to offset 1 row. It is not really that intuitive as you would expect (1) would be the offset result. However, (2) seems to be what is required to offset an extra row. It avoids writing Offset(1) and is very useful for saving space. The second part of your question is the offset by columns so offset has two arguments Offset(Row Offset, COlumn Offset) in the second example I am offsetting from column 3 to column 4 to fill the range.


    Hope this helps


    Take care


    Smallman

  • Autofill isn't working VBA

    Re: Autofill isn't working VBA


    Hi CodePro


    Give the following a try.


    Code
    Sub Goski()
    Cells(Rows.Count, 4).End(xlUp)(2) = "Sum of ABB"
    Range(Cells(Rows.Count, 4).End(xlUp)(2), Cells(Rows.Count, 3).End(xlUp).Offset(, 1)) = "=IF(RC[-3]="""",RC[-1],R[-1]C)"
End Sub


    Take care


    Smallman

  • Partial lookup value

    Re: Partial lookup value


    Mr Redli


    In my working career, I have never come across a process I could not change. You get a lot of time at work and can always improve things which have been created with questionable construction.


    Take care


    Smallman

  • Partial lookup value

    Re: Partial lookup value


    Thanks for finding the link and fixing it.


    I originally put the link in then tried to make it look pretty. Will repair the original.


    Kjbox - it can be done but i never link formula to another workbook - ever. My workbooks are always stand alone.


    Take care


    Smallman

  • Points Averaging Sheet using replace function

    Re: Points Averaging Sheet using replace function


    Hi


    Sorry for the slow reply. First time at a computer in a day or so.


    For multiple rows you might want a loop. This is not tested but something like the following - it is designed to work between rows G2 and G7.



    Take care


    Smallman

  • Points Averaging Sheet using replace function

    Re: Points Averaging Sheet using replace function


    I can't open your attachment but set up something which runs from Row 2 not 1. Include headings in Excel spreadsheets is for the best.


    Code
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, [G2]) Is Nothing Then
        Application.EnableEvents = False
        [b2:F2] = [C2:G2].Value
        [G2] = ""
    End If
Application.EnableEvents = True
End Sub


    This code goes in the workbook module. Remember run it from Row 2 where G2 changes. File attached to show workings.


    Take care


    Smallman

  • Insert a blank row below a sequencial group

    Re: Insert a blank row below a sequencial group


    I think you need to add more than 1 blank row. Code needs to check if the sequence of 1s ends and if there is a matching sequence that ends. As much as I despise using Select this is one instance where it rocks.


    Code
    Sub AddBlankRows()
    Dim rng As Range
    Set rng = Range("E1", Range("E65536").End(xlUp)).Offset(, 1)
 
    rng.Offset(1) = "=IF(AND(ROW()<>2,D1<>D2),1,0)"
    rng.AutoFilter 1, 1
    rng.Offset(1).SpecialCells(xlCellTypeVisible).Select
    rng.AutoFilter
    Selection.EntireRow.Insert
End Sub


    File attached to prove workings.


    Take it easy


    Smallman

  • sort cells by first word

    Re: sort cells by first word


    Hi primatt


    Welcome to the forum. If you open the file in the link Syywriter provided, it is almost exactly what you have asked for. Even the words are eerily similar. Let us know if you have trouble adapting the solution.


    Skywriter thanks for the link :)



    Take care


    Smallman

  • Run Code on Excel Worksheets

    Re: Run the code for all excel sheets


    Alternative method.


    Code
    Option Explicit
Sub HideFaster()
Dim i As Integer
    For i = 2 To Sheets.Count
        Sheets(i).[A6:A20].AutoFilter 1, "<>0", , , 0
    Next i
End Sub


    Take care


    Smallman

  • Why is Option Explicit optional?

    Re: Why is Option Explicit optional?


    Write code as well as you possibly can but for goodness sake, for all concerned, DECLARE your variables. It does make your code easier to read. If you are not planning on being the custodian of a file for the term of your natural life for the love of god give the poor souls that pick up your file a fighting chance to understand what you are trying to do. Using Option Explicit is like creating a management summary for all who gaze upon your book of code. Happy coding!!!


    Take care


    Smallman