Posts by DezB

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.

Return_on_Dates.xlsx

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

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

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

Dezbsample tracker.xlsm

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

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