Posts by Merren1897

    rory yes, i couldn't figure out the R1C1 advice you gave me and was running against the clock ;) late for me to ask for help too :)


    Code
    Set PCache = ActiveWorkbook.PivotCaches.Create _
    (SourceType:=xlDatabase, SourceData:="'" & PRange.Worksheet.Name & "'!" & PRange.Address(ReferenceStyle:=xlR1C1)). _
    CreatePivotTable(TableDestination:=PSheet.Cells(1, 1), _
    TableName:="ForwinPivotTable")


    changing the lines here still gives me a debug mismatch error on the last Tablename line.


    btw, the you code you had provided in response above works !! much appreciated.

    luckily was able to solve it . the formatting doesn't, if anyone knows the reason for that? It is not coming up as USD in red for negative.


    Code
        With ActiveSheet.PivotTables("ForwinPivotTable")
        .CalculatedFields.Add "GP/kg (Eur)", "= 'Gross Profit (EUR)'/ 'Export Chargeable Weight (kg)'"
        .PivotFields("GP/kg (Eur)").Orientation = xlDataField
        .Position = 1
        '.Function = xlSum
        .NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"
        .Name = "GP/kg (EUR)"
        End With

    I hit a road block and cannot figure out why the calculated field at the end of the code is not populating?


    thanks for any pointers


    I have a code that matches a cell value in Column C on Sheet1 to a pivot table on Sheet3 and then copies certain columns over.

    • Code will check how many entries there are on Sheet1 that need to be checked
    • Loop 2: For every value in Column C/Sheet1 with a match in Column A on Sheet 2 it will then copy over the corresponding data from Column B,C,D,E.
    • Since there are multiple matches possible by value/Sheet I am limiting the data pull to three matches (three loops in the code). To achieve that I am increasing i +1 or i+2 to get the next row in the pivot table.

    The table on Sheet 2 is sometimes 10,000+ rows and excel crashes.

    Does anyone have an idea how to speed up the loop codes (Loop2,3,4 are the same) to make it less work intensive e.g. array possibly? They are causing the lock up since I think the code keeps running up and down column A.


    I have a list of reference #s that I am matching to 2 data sets and if a match is found, the macro will pull certain fields over.


    For the 1st database I don't have multiple matches by reference # but in the second database I have multiple matches by reference # and I would like to pull only the most recent entries (preferably 2 or 3).


    The code right now will put the data in specific columns; for database 2 in Column H,I,J,K. In case there are up to 3 matches by reference # I would like the code to continue and add the second match in Column L,M,N,O and so on for the third.


    The code is below. While I was searching I was wondering if this code might help sorting by date?


    Code
    Option Explicit
    
    Public Sub TestMe()
        Dim dateRanges As Range    Set dateRanges = Range("D1:D11")
        Dim mn As Variant    With Application        
        mn = .Match(.Min(dateRanges), dateRanges, 0)    End With
        MsgBox Range("E" & mn).Value2
    
    End Sub


    this is the code i have right that is working for simple match




    i did a F8 step into the code to see how it behaves..


    until line 13 variable i will get to 21



    here variable iii will result in -4



    the next line will add 4 to it and iii is = 0 which then causes the next line to be subscript out of range.


    on my side, for some reason variable iCOl is 0 even though the currency is USD.


    so as an alternative, maybe we just add a column with a set identifier, which would vary in location, e.g. "XXXX" and after that monitoring would start so that the code could simply try to find 4 x's. (1.6c)

    I was looking at the header "HDRS" value and when it gets to the subscript error it reads 1 to 71.

    there are 17 headers in v 1.6

    17*4 is 68 + 3 is 71


    so if the code would start monitoring for changes shouldn't it start at Column H (to Column Q is 10 Columns).


    if it would account for 4 each that would be 40 + the info columns (A-G) 7 columns = 47 ?

    I was thinking, could that portion be dynamic in a way?

    So that the user could specify the column where the numeric values start ?


    so i think where 4 is mentioned in the code lines below, a link to cell would have to be entered, right ?:

    Code Line 15 For ii = 4 To UBound(x, 2) 'could 4 be linked to cell E1 in the sheet "data_changes"?

    Code Line 16 iii = iii + 4


    Code Line 31 For i = 4 To UBound(x, 2)

    Code Line 31 ii = ii + 4


    changing those give me a subscript out of range error though.