Excel formula range changed/reference error after query refresh: Indirect Function

Suppose we have added a sql query (using ODBC connection) and from the results, we are referring cells or range in excel formula, so when the results are refreshed, the range gets changed or we simply loose reference and get refer error.

Solution:

Instead of referring a cell directly like =A1, we can use Indirect function. Like =Indirect("A1"). Please note the quotes around cell reference, the INDIRECT function is making it a string output which is then interpreted as a cell reference and that trick helps in not loosing cell or range reference after query results refresh.

If we want to refer a cell in a different sheet, then we can do that as well with INDIRECT function, in below example we are referring Sheet2's F1 cell.

=INDIRECT("Sheet2!"&"f1")

Refer a Sheet with space(s) in Name using INDIRECT Function

And if the sheet name have spaces in it, use the single quotes along, like below.

=INDIRECT("'Summary Sheet'"&"!"&"A1")

Matching and Displaying a RegEx Pattern in a String and storing in Excel Rows

Suppose in Column A we have certain text and we want to search a pattern/certain words in the row and store the searched/found words, separated by comma in very next column B's respective row.

For that we can use the below Excel VBA code. Put the text data in Column A and select them and then run the below code, the results would be stored in column B. And Column C will have the label as per the words in Column 2. Change the Regex pattern as per your need.

Please note In order to use Regex in VBA, you first have to set the reference in the VBE editor. In the VBE editor, go to Tools>References>Microsoft VBScript Regular Expressions.

Below is the VBA code for use.

Sub amiq()

Dim strPattern As String: strPattern = "oil|gas|coal|sport|physical|gym|fitness"

Dim regEx As New RegExp

Dim strInput As String

Dim r As Integer

For r = 2 To Selection.Rows.Count

If strPattern <> "" Then

strInput = Cells(r, 1).Value

Values = ""

With regEx

.Global = True

'.MultiLine = True

.IgnoreCase = True

.Pattern = strPattern

End With

Set theMatches = regEx.Execute(strInput)

For Each Match In theMatches

Values = Values & ", " & Match.Value

If Values <> "" Then

Cells(r, 2).Value = Values

Else

Cells(r, 2).Value = "Not FOUND"

End If

If InStr(1, Cells(r, 2).Value, "oil", 1) > 0 or InStr(1, Cells(r, 2).Value, "coal", 1) > 0 or InStr(1, Cells(r, 2).Value, "gas", 1) > 0 then

Cells(r, 3).Value = "Energy

elseif InStr(1, Cells(r, 2).Value, "sport", 1) > 0 or InStr(1, Cells(r, 2).Value, "physical", 1) > 0 then

Cells(r, 3).Value = "Sports"

elseif InStr(1, Cells(r, 2).Value, "Gym", 1) > 0 or InStr(1, Cells(r, 2).Value, "fitness", 1) > 0 then

Cells(r, 3).Value = "Gym"

end if

Next

End If

Next r

End Sub

Gantt Chart in MS Excel; Urdu/Hindi, just two minutes

MS Excel Pivot & Power Pivot Table in Urdu/Hindi; Complete in 7 minutes