A formula recalculation does not trigger the Change event. You either need the Calculate event, or use the Change event but monitor the cells that are inputs to the formula, not the formula cell itself.
And you should never just stick On Error Resume Next at the top of your main routine and hope for the best! If anything does go wrong, you won't be alerted to it.
This is by no means the first time you have been told about cross-posting. It will however be the last time you are warned, so in future please make sure that you add the relevant links yourself. Thank you.
FYI, it is not a great idea to select another cell from within a SelectionChange event without disabling events first, then re-enabling them afterwards, otherwise you are liable to create recursion issues.
They're all stored in the registry (there's more than one key for the various options you can set). The exact location will change depending on the version of Office (and therefore VBA) that you have, but it will be something like:
and you can export that as a .reg file, copy it to the new machine (assuming the same version) and then just double-click to install to the registry.
You cannot alter the start or endpoint of a for...next loop within that loop. If you need to, you are using the wrong kind of loop (eg you should be using a Do...While loop), or your logic is wrong (eg you should be looping backwards).
Although it is true that using a Variant uses more memory than may be necessary in many cases, unless that is actually causing you a problem, I wouldn't worry too much about it. It's definitely the simplest option for what you are dealing with.
What is your regional date format?
I'd have thought it would bas as easy to just have a data validation dropdown list with Pass and Fail in it? Even if not, I'd suggest using Form buttons rather than activex - they are more stable and also a lot simpler to assign the same macro to.
Please actually read the rules. They don't say that you cannot post in more than one forum, just that you need to provide links if you do so.
Also please take a minute now to read the forum rules on cross-posting. It looks like you have cross-posted almost every question you have posted here on other sites. If you do not follow the rules in future, you are likely to lose your posting privileges.
Glad we could help.
- Sub Textjoin()
- Dim lastfor As Long
- With Application
- .ScreenUpdating = False
- .Calculation = xlCalculationManual
- End With
- lastfor = Sheet2.Cells(Rows.Count, 148).End(xlUp).Row
- Sheet2.Range("ES3").FormulaR1C1 = _
- Sheet2.Range("ES3").AutoFill Destination:=Sheet2.Range("ES3:ES" & lastfor), Type:=xlFillDefault
- Sheet2.Range("ET3").FormulaR1C1 = _
- Sheet2.Range("ET3").AutoFill Destination:=Sheet2.Range("ET3:ET" & lastfor), Type:=xlFillDefault
- With Application
- .Calculation = xlCalculationAutomatic
- .ScreenUpdating = True
- End With
- End Sub
and also remove the Screenupdating commands from the function code.
It's probably quicker to use arrays for the processing, like this:Code
- Public Function ConcatStringConditional(rngCritCol As Range, rngCrit As Range, rngConcat As Range) As String
- Dim i As Long
- Application.ScreenUpdating = False
- Dim CriteriaSet, DataSet, criterion
- criterion = rngCrit.Value2
- CriteriaSet = rngCritCol.Value2
- DataSet = rngConcat.Value2
- For i = 1 To UBound(CriteriaSet)
- If CriteriaSet(i, 1) = criterion Then
- ConcatStringConditional = ConcatStringConditional & vbCrLf & Format(DataSet(i, 1), rngConcat.Cells(i, 1).NumberFormat)
- End If
- Next i
- If Len(ConcatStringConditional) <> 0 Then ConcatStringConditional = Mid$(ConcatStringConditional, 3)
- Application.ScreenUpdating = True
- End Function
Also note that this does not need to be an array formula.
Constants, by definition, can't lose their values, so I assume you are talking about public variables. Anyway, there is no way to do what you want that I know of. You'd need a commandbar listener, and that would be reset in the state loss, just like your other variables. If you have a routine to reset them, it's not really a hardship to run one routine yourself, is it?
Do you also have SORTBY?
If not, clear Q2:Q9, then enter this in Q2:
and it will spill down automatically. If you do have it, use:
Do you have the SORT function available to you?
What version of Excel do you have?
What do you need it for?