'REPLACE next line with block to write to table use offset write the location last StrReviewedD = CStr(Day(datReviewed)) & "/" & CStr(Month(datReviewed)) & "/" & CStr(Year(datReviewed)) ' CStr(datReviewed) StrHoldingD = CStr(Day(datHolding)) & "/" & CStr(Month(datHolding)) & "/" & CStr(Year(datHolding)) ' CStr(datHolding) StrFinalD = CStr(Day(datFinal)) & "/" & CStr(Month(datFinal)) & "/" & CStr(Year(datFinal)) 'CStr(datFinal) If strStatus = "In the holding area, awaiting review" Then StrStatus = Trim(rngCell.Offset(0, 5).Value) StrTestedLoc = rngCell.Offset(0, 4).ValueĭatReported = CDate(rngCell.Offset(0, 3).Value) ' after searching each location, if date="" then write to cell " "įor Each rngCell In Range("incidentRange") ' default dates - tested date needs to be earlier to be replaced 'consider using R1C1 if can figure it out 'need to add by using offset of form range"rngResultsTable".offset(row+1,col) 'creates dynamic named range NB uses headers as 1st row If wsSheet.Name "Datix listing report" ThenĪctiveCell.FormulaR1C1 = "In holding area, awaiting review"ĪctiveCell.FormulaR1C1 = "Being reviewed"ĪctiveCell.FormulaR1C1 = "Awaiting final review" 'inserts new worksheet called "Tables", sets up column titles, widths, BOLD Name:="incidentRange", RefersTo:="=OFFSET('Datix listing report'!$A$2,0,0,COUNTA('Datix listing report'!$A:$A)-1,1)"ĭim datReported, datHolding, datReviewed, datFinal As Dateĭim strHoldingD, strReviewedD, strFinalD As Stringĭim intHolding, intBeingRev, intFinal As Integer is a random date in the future that I use to detect whether any dates have not been found in that category. I can't post a sample sheet due to the nature of the data within.
I am really cracking my head with this one any suggestions or help would be gratefully received. Is there any way of forcing Excel to handle the dates in UK format OR any other suggestions?
#How to format date cells in excel 2010 code#
The code needs to be fairly robust because it will end up being used by non-programmers.
#How to format date cells in excel 2010 manual#
I will have to run the macro twice each month sometimes the raw data on the spreadsheet can be thousands of lines long, so manual checking is not an option. I've tried using Day(date) and Month(date) to extract the day and month values, convert them into strings and putting them together to force it to do the correct output, but it really does not seem to work. When the date is in a more ambiguous form, then it is often (but not always) converted into a US format (eg 1st June 2017 becomes instead of ). The original datasheet has dates in UK format throughout. Dates are read from the original sheet using CDate. When the date is clearly UK format (eg ) it is written in the correct format. The macro sorts and finds the appropriate dates as required, however, when I write the dates to a table in another sheet confusion abounds! I have a macro that searches for dates in an imported Excel spreadsheet and counts records meeting certain criteria.
0 kommentar(er)
