Posts by KjBox

  • Copying rows from one sheet to another excluding "empty" rows

    Quote

    it works but it doesn't carry across the amounts for unit price - it makes them all 1

    I am totally confused, your example shows exactly what my macro will produce. You ask that column D values should not be included in the result, column D is Unit Price, now you are saying that all Unit Prices in the result are 1 (in fact Amount is 1) and you now want Unit Price included in the result.


    Why don't you attach a file where you manually create the Xero Export sheet so we know exactly what the required result should be.

  • Copying rows from one sheet to another excluding "empty" rows

    Try this


  • Copying rows from one sheet to another excluding "empty" rows

    The simplest way is probably to copy across all the data then delete column 4. Try this


  • Compare 2 Worksheets - Show List of Variances on 3rd Sheet

    Your welcome.


    Note that if you go with the divider Column method the that column can be hidden in the data. You would need to add a bit of code right at the end to hide it again in the Changes sheet.


    Code
    1. .Columns(lCol).Resize(, UBound(Hdrs) + 1).ColumnWidth = 12
    2. .Columns(lCol - 1).Hidden = 1
    3. End With
    5. End Sub

    Also delete the "On Error Resume Next" that you added.

  • Transaction to Inventory excel VBA using macro

    Here is the code with comments added, I hope it will help you follow and understand what each part does!

  • Compare 2 Worksheets - Show List of Variances on 3rd Sheet

    All you need do is add 1 to the value of i

    Code
    1. With Original
    2. For i = 2 To .UsedRange.Columns.Count
    3. If .Cells(2, i).Value = "xxxx" Then
    4. lCol = i + 1: Exit For
    5. End If
    6. Next
    7. End With


    Note I changed UsedRange.Rows to UsedRange.Columns.


    That was an error by me originally, .Rows just happened to work with the sample file but might not with your actual file.

  • VBA code to make process more efficient

    It was overwriting because of the protection of the new sheets, my fault, when the code adds a new sheet it gets protected, but I forgot to include UserInterfaceOnly = True. That is now fixed.


    I am attaching a workbook where I added 3 new sheets and added data to each. The Site Totals Table updates correctly, but, it does throw up a problem.


    You will see that I added data for 2 different Rooms for Science Block Level G, they have different Difficulty Factors (1 & 3). Since there is just 1 "Difficulty" entry for each row of the Site Totals Table, the code only records the last entered, or any data update, Difficulty Factor (in this case 3 for tiling the Chemistry Lab). Not sure of the best way to overcome this issue, maybe an average of all entered Difficulty Factors for the same Block/Area & Level? Alternatively a new row for every room on each level?


    I have also added code that will sort the Site Totals Table by the first column, so all Block/Areas are grouped no matter when they are entered. Also code to sort and group the Sheet Tabs.


    Here is an illustration of why Excel Built-in Tables (called ListObjects by VBA) are easier to work with. This code snippet

    Code
    1. If IsError(Application.Match(sShtNm, .ListColumns(1).Range, 0)) Then
    2. If .ListRows(1).Range.Columns(1) <> "" Then .ListRows.Add , 1
    3. With .ListRows(.ListRows.Count).Range
    4. .Columns(1) = sShtNm
    5. .Columns(2).Resize(, UBound(y)) = y
    6. End With
    7. Else
    8. lRow = Application.Match(sShtNm, .ListColumns(1).Range, 0) - 1
    9. .ListRows(lRow).Range.Columns(2).Resize(, UBound(y)) = y
    10. End If

    Checks if the Block/Area & Level are already in the Table, if not then a new Table Row is added (only if the Table already contains some data, if not the the first Table Row is used) and the data placed in that new Table Row, otherwise the correct Table Row gets identified and the data in that row is updated. No need to check for "the next empty row", also any formulas in the Table are automatically copied down to the newly added row.


    I am attaching 2 files, one empty with the modified code, the other with 3 extra sheets with data added so you can see the issue with Difficulty Factor.

  • Compare 2 Worksheets - Show List of Variances on 3rd Sheet

    You have not noticed the parts of the formula that are in parentheses!


    ReDim z(1 To UBound(x, 1) - 1, 1 To UBound(x, 2) * 4 - ((lCol - 1) * 3))


    Ubound(x,1) = 20, UBound(x,2) = 17 and lCol (which is the Column where monitoring needs to start) = 8


    so


    z(1 To 8 - 1, 1 To 17 * 4 - ((8 - 1) * 3)) equates to z(1 to 7, 1 to 68 - (7 * 3)) which, in turn, = z(1 To 7, 1 To 68 - 21) = z( 1 To 7, 1 To 47)


    Hdrs is the ReDimmed 1 to UBound(z, 2), which is 1 to 47


    Rename the file you are testing with and send it to me I can then see if something weird happened when Uploading and/or Downloading.