Search:

Type: Posts; User: StephenCrump; Keyword(s):

Page 1 of 6 1 2 3 4

Search: Search took 0.13 seconds.

  1. Replies
    7
    Views
    191

    Re: VBA Match Array Error 1004

    This works for me, and updates A3 and B3 to A4 and B4 etc, as you require:


    Dim LastRow As Long

    LastRow = Worksheets("PR Dist SSD Report").Range("A" & Rows.Count).End(xlUp).Row

    With...
  2. Replies
    7
    Views
    191

    Re: VBA Match Array Error 1004

    Can you please post the code you are using?
  3. Re: Is there a Excel formula range to automatically move month on month?

    Welcome to the Forum!

    The first problem with your formula is that you are using the CHOOSE function incorrectly. If you are looking for a value in a range you'll need MATCH.

    The better method...
  4. Replies
    7
    Views
    191

    Re: VBA Match Array Error 1004

    Your formulae haven't posted very well, but I think what you've got is:

    Range("D3").FormulaArray = "=IF(INDEX('Raw Data for PR Dist'!R7C5:R100000C5,MATCH(1,('PR Dist SSD Report'!RC[-3]='Raw Data...
  5. Replies
    4
    Views
    179

    Re: Rotate a list

    You have defined fc and lc as integer, but haven't assigned any values.

    Therefore Cells(lc - 2, 17) is Cells(-2,17) which is causing the error message.

    The easier way / way around it depends on...
  6. Re: VBA Code for an accumulator & how to install it correctly.

    Perhaps something like:

    If SOH for any item falls to zero, record the date-out-of-stock (static value)

    When SOH reurns to positive - presumably the trigger for this is that a new stock amount...
  7. Replies
    1
    Views
    107

    Re: Converting formula to VBA

    One immediate observation is that you won't necessarily need to use VLOOKUP in every row:

    =IF(D1=SSCC_counter_H!D1,IF(ISNUMBER(MATCH(D1,SSCC_bin_H!A:A,)),"A",""),"")

    We can also write much...
  8. Re: VBA Code for an accumulator & how to install it correctly.

    We'll need a bit more detail about when you want the running totals to recalculate?

    One possible scenario might be that each row has a record-specific date, and there is a general calculation date...
  9. Replies
    8
    Views
    293

    Re: Index cannot accept array inputs

    Perhaps easier to see if you rearrange slightly:


    =INDEX(A3:C3,MATCH(TRUE, VLOOKUP(T(IF({1},A3:C3)),$L$1:$M$3,2,) >COUNTIF(H$2:H2,A3:C3),))

    vs

    =INDEX(A3:C3,MATCH(TRUE,...
  10. Replies
    8
    Views
    293

    Re: Index cannot accept array inputs

    I'm not sure what you mean?

    H3: =INDEX(A3:C3,MATCH(TRUE,LOOKUP(A3:C3,L$1:L$3,M$1:M$3)>COUNTIF(H2:H$2,A3:C3),)) array-entered
    Copy down to H4:H9

    This reproduces your assigned choices for all...
  11. Replies
    8
    Views
    293

    Re: Index cannot accept array inputs

    You can produce the same results using:

    H3: {=INDEX(A3:C3,MATCH(TRUE,LOOKUP(A3:C3,L$1:L$3,M$1:M$3)>COUNTIF(H2:H$2,A3:C3),))}
  12. Re: [Solved]: ARRAY number of rows and colums, how to know the correct values for the size...

    That's good to hear, thanks.
  13. Re: Proportionally distribute the weights of questions on an exam when the test-taker answers "N/A" for a question

    Welcome to the Forum.

    Something like this perhaps?

    D2: =IF(C2="N/A",0,B2*B$13/(B$13-SUMIF(C$2:C$11,"N/A",B$2:B$11)))

    <b></b><table cellpadding="2.5px" rules="all" style=";background-color:...
  14. Re: ARRAY number of rows and colums, how to know the correct values for the size...

    If Gas!A6:Axx is numeric, you can use:

    MyRange: =Gas!$A$6:INDEX(Gas!$A:$A,MATCH(9.99999999999999E+307,Gas!$A:$A))

    To create MyRange, select Formula/Name Manager/New... and then type:

    Name: ...
  15. Replies
    4
    Views
    170

    Re: Formula to calculate payments

    What is the actual question you are trying to solve?
  16. Re: ARRAY number of rows and colums, how to know the correct values for the size...

    Try: =IF(ROWS(A$10:A10)>$D$8,"",INDEX(MyRange,SMALL(IF((MyRange>=$C$4)*(MyRange<=$E$4),ROW(MyRange)-ROW(INDEX(MyRange,1))+1),ROWS(A$10:A10))))

    where:

    MyRange:=...
  17. Re: Compile error when attempt to run macro with #If VBA7 = True Then...

    Great!



    VBA's built-in compiler constants are 1 (for True) or 0 (for False).

    So your testing if 1 = True actually returns False.
  18. Re: Compile error when attempt to run macro with #If VBA7 = True Then...

    This works for me, using Win10 Pro, Office365 64bit:


    #If Win64 Then
    Private Declare PtrSafe Function OpenProcess Lib "kernel32" (ByVal dwDesiredAccess As Long, ByVal bInheritHandle As...
  19. Re: Compile error when attempt to run macro with #If VBA7 = True Then...

    What exactly is the error message you're getting?

    Is your VBAProject perhaps missing a reference to the Object Library?
  20. Replies
    10
    Views
    509

    Re: Prorate price formula

    I thought from Post#5 that your $267 was actually $16 x 5/30?

    However, if 11 March to 15 March is 4 days, and not 5 inclusive, then presumably based on a monthly fee of $30 and the effective dates...
  21. Re: Compile error when attempt to run macro with #If VBA7 = True Then...

    Welcome to the Forum!

    You need to test:

    #If Win64 Then '.....
  22. Replies
    10
    Views
    509

    Re: Prorate price formula

    Sorry, I'm lost now.

    You have only provided one example so far.

    Perhaps a few more examples will help us understand what you're trying to achieve, thanks.
  23. Replies
    10
    Views
    509

    Re: Prorate price formula

    You'll need to be give some more thought to how you want your 360 day calendar to work ...

    What if the effective date is, say, 15 Feb 2019, 16 Feb 2019, or 28 Feb 2019?
  24. Re: Macro to loop through multiple rows of cell references

    Did you create a range name called "No"?

    Or try: https://app.box.com/s/896kljkli3cygjsn2iu283lpotdjfowh
  25. Replies
    2
    Views
    187

    Re: Macro to loop through rows of data

    Try: https://www.mrexcel.com/forum/excel-questions/1094086-macro-loop-through-multiple-rows-cell-references.html
Results 1 to 25 of 150
Page 1 of 6 1 2 3 4