How to use a macro to run through sheet in excel and put double quotes around values
Category: [Excel] Demo Available
How to use a macro to run through sheet in excel and put double quotes around values
Requirement:
My excel has been culled down from 140000 lines to a few thousand through filters. I cut out the few thousand and pasted to new sheet. I have values such as the below ... I want to go line by line through the sheet and add quotations around any value after an equal sign. Many of the values (especially the more difficult values that have multiple words) already have quotes so that headache is out. I do have concrete variables that equals those values, such as macharea as a variable =71477.9572, etc. If it could loop through and just find an equal sign and quote around the value following up to the first space, that would be ideal. I believe I could also name the variable and code in to quote it's value possibly. I would appreciate any tips on how I can accomplish the final goal of quotes around all values. The reason for this objective is to then convert this into legitimate xml. xml requires quotes around any value.
Sample data:
| <assycount=3778 drillcount=24 macharea=22623086.57295438 machcount=4907 name="21-00325.003" taskcount=351 taskhours=1571.45481423 usetasksforassembly=false> | |||||||||||||||||
StephenGunn, the user requires the following: "I don't know how to go about this and am not sure it can be done with excel. I've done quite a lot with functions and macros in excel in the past. To me this one is a challenge and I've found not solid examples of anything at all similar out on the net.
Process is this. Export xml from a Die Build Process Planner COTS application called SMIRT. The xml is not true xml and cannot be read in an xml reader. I could not import it as xml into excel. In my case I spent about 30 hours writing regular expressions in notepad ++ and doing replace and finds. I did write this regular expression \b(\w+)=((?:\s*[^=>]+\b(?!=))+)?(\s+|\/?>) $1="$2"$3 which got me mostly there but was double writing quotes on some of the values (not all) that already had quotes. I rewrote that regular expression a few times. In mean time a coworker showed me he could past into excel the structure and excel logically indented (followed the indenting) of the structured xml … we have 10 columns and created filters on each and were able to filter out unncessary content. I had ten macros in regex that accomplished this in notepad++ but this was a much quicker process. I wanted it in excel because I've been able to write macros in the past for many needs. I felt confident I’d be able to write something for this but nothing I found fit the bill to do what I needed here.
Very specifically, I was looking a way, and I'm not at sure if even possible which is the challenge, to go through the cells in the excel and find every location of an equal sign. If the equal value to the equal sign already has quotes to ignore that value. If the value of the equal sign does not have quotes to put quotes around that value. The value end would be identified by the first space found. Thus myvariable=myvalue mysecondvariable=myvalue2, etc. Many of these variables and values would be found in the same cell."
Solution:
Code:
Sub AddQuotes()
Dim x, y, i As Long, ii As Long, iii As Integer, s As String
x = Sheet1.Cells(1).CurrentRegion.Offset(1, 1)
For i = 1 To UBound(x, 1)
For ii = 1 To UBound(x, 2)
If x(i, ii) <> vbNullString Then
y = Split(x(i, ii))
x(i, ii) = vbNullString
If Not IsArray(y) Then
x(i, ii) = y
Else
For iii = LBound(y) To UBound(y)
If InStr(1, y(iii), Chr(61)) Then
s = Split(y(iii), Chr(61))(1)
If Left(s, 1) <> Chr(34) Then
If Right(s, 1) = Chr(62) Then
s = Chr(34) & Left(s, Len(s) - 1) & Chr(34) & Chr(62)
Else
s = Chr(34) & s & Chr(34)
End If
x(i, ii) = x(i, ii) & " " & Split(y(iii), "=")(0) & Chr(61) & s
Else
x(i, ii) = x(i, ii) & " " & y(iii)
End If
Else
x(i, ii) = x(i, ii) & " " & y(iii)
End If
Next
x(i, ii) = Trim(x(i, ii))
End If
End If
Next
Next
With Sheet2
.Cells.Clear
.[a1].Resize(UBound(x, 1), UBound(x, 2)) = x
.Activate
End With
End Sub
Obtained from the OzGrid Help Forum.
Solution provided by KjBox.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to use a macro for grouping rows based on cells with same names |
| How to use a macro to pull every Nth row of data |
| How to use a macro to select value criteria from a table rather than manually inputting |
| How to create a macro button to put date in selected cell within specific column |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.