a simple way to select and clear these blank cells to make them blank: Press ctrl + a or pre-select your range Press ctrl + f Leave find what empty and select match entire cell contents. Hit find all Press ctrl + a to select all the empty cells found Close the find dialog Press … Read more
A common trick is to check like this: trim(TextBox1.Value & vbnullstring) = vbnullstring this will work for spaces, empty strings, and genuine null values
This is a bit outside the scope of your question, but to avoid any potential confusion for readers who are new to VBA: End and End Sub are not the same. They don’t perform the same task. End puts a stop to ALL code execution and you should almost always use Exit Sub (or Exit … Read more
You can convert the value to a date using a formula like this, next to the cell: =DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2)) Where A1 is the field you need to convert. Alternatively, you could use this code in VBA: Sub ConvertYYYYMMDDToDate() Dim c As Range For Each c In Selection.Cells c.Value = DateSerial(Left(c.Value, 4), Mid(c.Value, 5, 2), Right(c.Value, 2)) … Read more
You can actually do this and have Excel show a clickable link. Use this format in the CSV file: =HYPERLINK(“URL”) So the CSV would look like: 1,23.4,=HYPERLINK(“http://www.google.com”) However, I’m trying to get some links with commas in them to work properly and it doesn’t look like there’s a way to escape them and still have … Read more
You don’t need the CELL() part of your formulas: =INDIRECT(ADDRESS(B1,B2)) or =OFFSET($A$1, B1-1,B2-1) will both work. Note that both INDIRECT and OFFSET are volatile functions. Volatile functions can slow down calculation because they are calculated at every single recalculation.
Right click on Cell B1 and choose Format Cells. In Custom, put the following in the text box labeled Type: [h]:mm:ss.000 To set this in code, you can do something like: Range(“A1”).NumberFormat = “[h]:mm:ss.000” That should give you what you’re looking for. NOTE: Specially formatted fields often require that the column width be wide enough … Read more
Use the following formula: =INDEX(Sheet2!$F$1:$F$100,MATCH(1,INDEX((Sheet2!$A$1:$A$100=A1)*(Sheet2!$B$1:$B$100=B1),),0)) Sheet 2 for reference
Yup: =SUMPRODUCT(–ISNUMBER(SEARCH($C$1:$C$6,A1)))>0
You could use a PivotTable and a lookup formula to accomplish this. Below is some simplified data in an Excel Table (aka ListObject), and below that is a PivotTable with a suitable lookup formula down the right hand side. The PivotTable has the Reg No in it as well as the RecordID field in the … Read more