Posts by DezB

    Hi vcoolio


    I've hit a strange snag with this. The archive sheet now has 270 entries where the macro has been used to take rows of data from the active sheet to the archive sheet.


    However it's now stopped transferring any further data. When the macro is run, it takes the data out of the row that has the "Yes" selected, does nothing then displays the message box, however this says 0 rows transferred, but the data just disappears , very strange... Any ideas please? I've tried to see if there is any limitations set on the number of rows but there doesn't seem to be.


    Kind regards


    DezB

    Ho Roy.


    Many thanks for getting back to me, much appreciated. I'm still having problems and can't figure out why. I've attached a sample and would really appreciate it if you could tell me where I'm going wrong please?


    I've tried various formulae but all do the same thing except yours which only counts one occurrance of a month selected, the others just count all entries as the same month, really peculiar.


    Kind regards


    DezB


    by_month.xlsx

    Hi bosco_yip


    My apologies for taking so long to thank you for your help, I should have done it earlier.. :-(


    I'll try harder in future...


    Kind regards


    DezB

    Hi Folks


    I have a formulae that counts the number of dates in a column by the month that they are in:


    =SUMPRODUCT(--(MONTH(A2:A398)=B2))


    However, although it works for months 2-12, if I select 1 to count any dates in January it counts the entire range?? For example the column has 397 cells, if I leave them all empty it counts 397.


    If I enter a date, say, 01/02/2021 and select 2 in B2 it counts 1, if I then reselect 1 in B2 it counts 396??? What am I doing wrong, it's driving me nuts...


    Really hoping someone can help please.


    Kind regards and thanks in advance :-)


    DezB

    Hi folks


    I'm struggling with a multiple vlookup and was hoping for some help/advice please.


    I need the vlookup to look at a reference number in column B on sheet 1, find that reference number on sheet 2, then look up a word on sheet one in column C and if that word is in column Y in the corresponding row on sheet 2 return the value (text) that is in column X on sheet 2. If there is nothing in Column x then do nothing.


    I got as far as:


    =IFERROR(VLOOKUP(B2,'Control sheet '!B:B,2,0),(IFERROR(VLOOKUP(B2,'Control sheet '!Y:Y,3,0),IFERROR(VLOOKUP(B2, 'Control sheet '!X:X,0)))

    But the last bit is wrong and I can't figure out how to just get it to return the value in column Y


    Any advice please?


    Kind regards


    DezB

    Files

    • vlookup.xlsx

      (10.06 kB, downloaded 25 times, last: )

    Hi Both.


    Sorry to be a pain, but could I ask, if I need to add more columns, I understand that I need to change the cell that contains "Yes" from P3 to whatever the new cell is but what I don't understand is the line of code:


    .Offset(1, -15).Resize(, 16).Copy ws2.Range("A" & Rows.Count).End(3)(2)


    I'm sure that the "16" refers to the number of columns to use but could you explain to me what the -15 and the (3) (2) bit means.


    I may be coming across as bit dense and I apologise but VBA is not my strong point...


    Many thanks again.


    Kind regards


    DezB

    Hi Folks


    VBA is not my strong point, but I'm trying set something up where if the word "Yes" is selected in a particular cell (col P) then, when the macro is run, it takes the contents of that row from Column A through to P and moves those contents to the next available row on another sheet.


    The caveat is that multiple rows may have Yes selected before the macro is run. In that case it needs to go through them one by one and move each of the selected rows to the other sheet.


    The code below seems to work to a point but only for one selection at a time. In addition it also overwrites what has also already been moved to the other sheet.


    Any advice or help please, I'm well and truly stuck..




    Kind regards


    DezB

    Hi Folks.


    Not sure if this is the correct section but I'm wondering if it is possible to have a cell containing something as simple as "click here to show chart" that when clicked shows a chart from another sheet with a close or X to close?


    Any help much appreciated please? :-)


    Kind regards


    DezB

    Ha, sorted it:


    =SUMIFS(Paste_Sheet!$F:$F,Paste_Sheet!$C:$C,$B$13:$B$18,Paste_Sheet!$D:$D,C$12,Paste_Sheet!$N:$N,$B$12)


    Moved the source cells and everything worked... :-)