Search:

Type: Posts; User: steve the fish; Keyword(s):

Page 1 of 6 1 2 3 4

Search: Search took 0.21 seconds.

  1. Replies
    2
    Views
    42

    Re: tidy vba

    Lets take a little part:


    Range("F2:G2").Select
    Selection.Copy
    Range("F3").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=True

    You...
  2. Re: Struggling with finding a method to find and replace data

    Find and replace have various parameters such as 'entire cell contents' etc. Excel remembers the last used setting so the line Fluff added id have thought is there to reset the find replace dialog to...
  3. Re: Cross reference cell values then copy data between sheets

    Hi. Try this:

    =MOD(LOOKUP(2,1/(RIGHT(Source!$H$1:$H$1000,3)=""&A1),Source!$R$1:$R$1000),1)

    Alter the ranges as necessary.
  4. Replies
    10
    Views
    141

    Re: Array Formulas not necessary

    All i can tell you is neither of those would work on my version without CTRL-SHIFT-ENTER. Are you sure you dont have an INDEX in there somewhere?
  5. Replies
    10
    Views
    135

    Re: VBA Arrays

    Bit long winded but should work:


    arrFilterOut = Array("Person 14", "Person 10", "Person 3", "Person 4", "Person 5")
    arr = ActiveSheet.ListObjects("Table1").ListColumns(1).DataBodyRange
    With...
  6. Re: INDEX / MATCH / OFFSET / AVERAGE Formula

    If you look at the formula it has two match formulas in it. The +1 and the +3 are being used to manipulate where the average range starts and ends. So plus one means it starts one cell to the right...
  7. Re: VBA: right of a character and include that character

    Thanks Rick i wasnt aware of that.
  8. Re: INDEX / MATCH / OFFSET / AVERAGE Formula

    Hi. This would work:

    =AVERAGE(INDEX(C3:J3,MATCH($B3,$C$1:$J$1,0)+1):INDEX(C3:J3,MATCH($B3,$C$1:$J$1,0)+3))

    but i dont know if you already know which row the lookup cell (the left red 22222) is...
  9. Re: VBA: right of a character and include that character

    I presume those spaces arent meant to be there around the " / " in your code? You could just add 1 eg


    Right(elem.innerText, Len(elem.innerText) - InStrRev(elem.innerText, "/") + 1)

    or use mid...
  10. Replies
    12
    Views
    121

    Re: Hello board - finding a date in a column

    Ok so i read the first post again. We need to look for the last day of the month but it isnt necessarily the last day of the month. I cant see anywhere where you have stated how you would deduce this...
  11. Replies
    12
    Views
    121

    Re: Hello board - finding a date in a column

    I dont think you understand my question. How would anyone know which day to look for? What are the rules? Once a rule is established what is it you want to do with this date?
  12. Replies
    12
    Views
    121

    Re: Hello board - finding a date in a column

    Oh april doesnt have 31 days but you know what i mean i hope!
  13. Replies
    12
    Views
    121

    Re: Hello board - finding a date in a column

    You havent really suggested how anyone would know what is your last day of the month. Why is it the 29th? Is it because the 30th and 31st are weekend days?
  14. Replies
    2
    Views
    48

    Re: Index/Sumif or Sumproduct help

    Something like:

    =INDEX($C$2:$F$5,MATCH($Q$1,$B$2:$B$5,0),MATCH($Q$3,$C$1:$F$1,0))

    or

    =SUMIFS(INDEX($C$2:$F$5,,MATCH($Q$3,$C$1:$F$1,0)),$B$2:$B$5,$Q$1)
  15. Replies
    2
    Views
    85

    Re: Formula to calc YTD Target

    Is column H a date formatted to look like it does or text? Test one of the dates with =ISNUMBER(H2). If i read right you want a year on year comparison?
  16. Re: VBA If And Or query on strings containing times

    What does Mid(MyCell, 4, 2) produce?
  17. Re: Nesting Sumproduct and Sumifs not working

    You can do that with a sumproduct sumifs:

    =SUMPRODUCT(SUMIFS(D2:D7,A2:A7,F7,B2:B7,Condition_List))
  18. Re: VLOOKUP not finding data that is present

    Your formula is wrong but if its showing na error then its not finding the lookup value in column D of the sheet. Use CTRL-F to confirm the value is there. Make sure entire cell contents is checked.
  19. Replies
    5
    Views
    73

    Re: IF Statement - brain fog!

    There is a principle of mathematics that multiplication and division are done before addition and subtraction so maybe put some brackets around this part:

    C4-D4*G4

    to become

    (C4-D4)*G4
    ...
  20. Replies
    6
    Views
    112

    Re: Array Formula Issue

    Its because AND doesnt produce an array. If you need AND use multiplication.
  21. Re: converting a 6 digit string into a 3 character string(HELP)

    Hi. Test this:

    =CHAR(MID(A1,LEN(A1)-4,1)+IF(MID(A1,LEN(A1)-5,1)="3",48,64))&CHAR(MID(A1,LEN(A1)-2,1)+IF(MID(A1,LEN(A1)-3,1)="3",48,64))&CHAR(MID(A1,LEN(A1),1)+IF(MID(A1,LEN(A1)-1,1)="3",48,64))
  22. Replies
    4
    Views
    62

    Re: SUMIF - dates, ignore times?

    You can using SUMIFS. Greater than or equal to the date and less than the date +1.
  23. Replies
    5
    Views
    98

    Re: Lookup and Populate cell

    Put this in A3 and copy down 31 cells. You may need to change the year if it happens you need a leap year February. Is that what you mean?

    =IFERROR(DAY(ROWS($A$1:A1)&LEFT($A$2,3)&2019),"")

    May...
  24. Re: VBA using a set range inside an indirect formula

    You could do this. YearRng then becomes a string which you can concatenate into the formula string:


    YearRng = Application.InputBox("Please select the year range for the first location in your...
  25. Re: slow formula. Is there a faster way?

    You still didnt really answer what i was thinking as if you delete row 84 your formula then has a different lookup value. It may be best to give a small sample of your sheet so we could take a look.
Results 1 to 25 of 150
Page 1 of 6 1 2 3 4