Hi Alan
Very good point, thank you.. I'll rearrange the layout and try again.
Much appreciated..
Deryn
Hi Alan
Very good point, thank you.. I'll rearrange the layout and try again.
Much appreciated..
Deryn
Hi Folks
I'm trying to match the dates in C7 and D7 with the dates on the Data sheet and return the data below the correct dates into C8 and D8 on the attached example but not having much luck. I've tried using INDEX and MATCH but keep getting #VALUE error
I'd really appreciate some help if at all possible please.
Kind regards
Deryn
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
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
Hi vcoolio
Yep, you're not wrong, my head is spinning.. -;) but it's really interesting and so very useful, thank you.
Kind regards
DezB
Thank you, er, I think I'll get to that once I've got my head around this part.. The problem is that there are other cells where a Yes/No value must be selected so the way you've done is better..
Thanks again.
DezB
Hi gijsmo
Fantastic, that makes it easier to understand, thank you, I really do appreciate your help.
I'll have a go and let you know how I get on.
Kind regards
DezB
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 gijsmo
Also fantastic, thank you and much appreciated.
Kind regards
DezB
Hi vcoolio
Fantastic, that is amazing, thank you, very much appreciated.
Kind regards
DezB
Hi vcoolio
That worked just fine until I aaded a new row onto the first sheet, selected Yes and ran the macro, then it overwrote the entries on the receiving sheet? Any ideas?
Thnak you for your help, it's really appreciated.
Kind regards
Dezb
I've attached a sample of the workbook, guess I should have done that first... it's Sunday, I'm tired...
Kind regards
Hi vcoolio
The headings are in row 3, there's data and instruction in rows 1 and 2, and the data being moved needs to start at row 3 on the receiving sheet, sorry, I should have mentioned that.
Let me have a go, thank you...
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
Sub ArchiveData()
'Created 03-06-21
Dim xRg As Range
Dim xCell As Range
Dim I As Long
Dim J As Long
Dim K As Long
I = Worksheets("Active_Cases").UsedRange.Rows.Count
J = Worksheets("Archived_Cases").UsedRange.Rows.Count
If J = 1 Then
If Application.WorksheetFunction.CountA(Worksheets("Archived_Cases").UsedRange) = 0 Then J = 0
End If
Set xRg = Worksheets("Active_Cases").Range("P1:P" & I)
On Error Resume Next
Application.ScreenUpdating = False
For K = 1 To xRg.Count
If CStr(xRg(K).Value) = "Yes" Then
xRg(K).EntireRow.Copy Destination:=Worksheets("Archived_Cases").Range("A" & J + 0)
xRg(K).EntireRow.Delete
If CStr(xRg(K).Value) = "Done" Then
K = K - 1
End If
J = J + 1
End If
Next
Application.ScreenUpdating = True
End Sub
Display More
Hi Roy
Worked perfectly, use the userform to create an image of the chart in question, spot on, thank you..
Or even a plan....
Hi Roy.
Now that sounds like a plane, thank you....
Kind regards
Deryn