Posts by pike

    Hi atrans,

    just remember VBA is loosely object based so you need to qualify the object for the name

    or a reversed transpose

    Hi ,

    maybe consider

    Code
    1. Option Explicit
    2. Sub TransposePaste_Index_dbArray()
    3. Dim dbArray
    4. With Sheets("Data Sheet Copy")
    5. dbArray = .[A1].CurrentRegion
    6. End With
    7. With Sheets("NewSheet")
    8. .[A1].Resize(UBound(dbArray, 2), UBound(dbArray)) = Application.Transpose(Application.Index(dbArray, Evaluate("Row(1:" & UBound(dbArray) + 1 & ")"), Evaluate("Column(1:" & UBound(dbArray, 2) + 1 & ")")))
    9. End With
    10. End Sub

    What is the best way to turn on and off screen updating and other applications to reduce interaction with code and screen.

    one way AutoDisplay routine below at the start and finish of synatx for code with more that one update

    Code
    1. Sub AutoDisplay()
    2. Application.ScreenUpdating = Not Application.ScreenUpdating
    3. Application.DisplayAlerts = Not Application.DisplayAlerts
    4. Application.Calculation = Array(xlCalculationManual, xlCalculationAutomatic)(CInt(Application.Calculation = xlCalculationAutomatic) + 1)
    5. End Sub

    Hi,

    Maybe Worksheet Change event

    You could try something like this;


    Code
    1. Option Explicit
    2. Sub test()
    3. Dim dbarray
    4.   dbarray = Range("B5:N23").Value
    5.   Range("B5:N23").Value = Application.index(dbarray, , Array(1, 3, 1, 5, 1, 7, 1, 9, 1, 11, 1, 13, 1))
    6. End Sub

    hello mike,

    sumproduct is ok if the arrays are not to big and slow. You can add another array to the sumproduct like ,,,, =SUMPRODUCT(--ISNUMBER(FIND($B6,'2020Activity'!$E$2:$E$227))*('2020Activity'!$A$2:$A$227>=$C$3)*('2020Activity'!$A$2:$A$227<=$C$4),'2020Activity'!$C$2:$C$227,--($P$2:$P$227=$B$1)

    it hard to say exactly what you need without knowing the range and data layout.

    Click Here for a better explanation of adding another array.


    other ways are index match combination like =INDEX($D$2:$D$13,MATCH(1,INDEX(($A$2:$A$13=E2)*($B$2:$B$13=F2)*($C$2:$C$13=$G$1),0),0))


    or lookup =LOOKUP(2,1/($A$2:$A$13=E2)/($B$2:$B$13=F2)/($C$2:$C$13=$G$1),$D$2:$D$13)

    then it would be,,,

    Code
    1. Option Explicit
    2. Sub test()
    3. Dim lngRow As Long
    4. With Sheets("Master")
    5. lngRow = .Cells(Rows.Count, 6).End(xlUp).Row
    6. Worksheets("Sheet1").Range("A1").Resize(lngRow - 10, 4).Value = .Range("C11:F" & lngRow).Value
    7. End With
    8. End Sub

    or

    Code
    1. Option Explicit
    2. Sub test()
    3. Dim lngRow As Long
    4. With Sheets("Master")
    5. lngRow = .Cells(Rows.Count, 6).End(xlUp).Row
    6. Worksheets("Sheet1").Range("A" & Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row + 1).Resize(lngRow - 10, 4).Value = .Range("C11:F" & lngRow).Value
    7. End With
    8. End Sub

    One of these two code may be ok , unless you just need the values


    Code
    1. Option Explicit
    2. Sub test()
    3. With Sheets("Master")
    4. .Range("C11:F" & .Cells(Rows.Count, 6).End(xlUp).Row).Copy Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
    5. End With
    6. End Sub

    or

    Code
    1. Option Explicit
    2. Sub test()
    3. With Sheets("Master")
    4. .Range("C11:F" & .Cells(Rows.Count, 6).End(xlUp).Row).Copy Worksheets("Sheet1").Range("A1")
    5. End With
    6. End Sub