Posts by honu

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

  • Find Value In Column & Copy Entire Row

    thanks for that simon.


    i have another small question, related to this.


    what would be the best way to search for a value in a cell (an unique one) using an input box and then if the value is found copying the whole row of the cell onto a new worksheet in the next available row within a table.


    I am currently using the following archaic codes:


    x$ = InputBox("Please Enter Catalogue Number")


    For i = 4 To 500


    If Sheet2.Cells(i, 1) = x$ Or Sheet2.Cells(i, 7) = x$ Or Sheet2.Cells(i, 8) = x$ Or Sheet2.Cells(i, 9) = x$ Then


    Sheet1.Cells(6, 1) = Sheet2.Cells(i, 1)
    Sheet1.Cells(6, 2) = Sheet2.Cells(i, 2)
    Sheet1.Cells(6, 3) = Sheet2.Cells(i, 3)
    Sheet1.Cells(6, 4) = Sheet2.Cells(i, 4)
    Sheet1.Cells(6, 5) = Sheet2.Cells(i, 5)
    Sheet1.Cells(6, 6) = Sheet2.Cells(i, 6)
    End If


    Next i


    End Sub


    this has the problem that i can't fit it to the next available row and have to be specific for each row.


    it would also be great if i could put a messagebox in cases where the data is not found, basically saying that the item does not exist and if a simple excel Ctrl+F button or something similar could be attached to the msgbox so that the item can be searched and then added to the next available row.


    Thanks for the help in advance!

  • Copy 1st X Rows To Another Worksheet

    Apologies if this has been posted before but I need some help on a macro I am currently working on.


    There is a sales sheet which basically produces a sales receipt and a report sheet which should keep track of all the items sold.


    so i was wondering how a simple do loop or equivalent command could be used to copy about 15 rows (6 cells from each row) from the sales sheet to the report sheet (technically starting from the first available empty row in the report sheet) each time a sales receipt is produced.


    any help will be greatly appreciated!


    thanks!!!