|
|
Thread Tools | Display Modes |
#16
|
||||
|
||||
Try the following update. Note that I've retained the pressure conversions in the code, but they're commented-out so they won't do anything. That means you can reinstate them later on if need be.
Code:
Sub EQLConverter() Application.ScreenUpdating = False Dim lRow As Long, i As Long, j As Long, k As Long Dim StrTmp As String, StrBit As String, StrConv As String, StrOut As String With ActiveSheet.UsedRange 'Loop through each cell in column C For i = 3 To .Cells.SpecialCells(xlCellTypeLastCell).Row With .Cells(i, 3) StrOut = "" 'Split the cell results by spaces for parsing k = UBound(Split(.Text, " ")) 'Process each 'word' For j = 0 To k StrTmp = Split(.Text, " ")(j) If Len(StrTmp) > 0 Then 'See what the 'word' ends with Select Case UCase(Right(StrTmp, 1)) 'If it ends with ', try a ft:m conversion Case "'" StrConv = Replace(Split(StrTmp, "'")(0), "(", "") If IsNumeric(StrConv) Then StrOut = StrOut & " " & Format(StrConv * 0.3048, "0.0") & "m" & " (" & StrTmp & ")" Else StrOut = StrOut & " " & StrTmp End If 'If it ends with ", try an in:mm conversion Case """" StrConv = Replace(Split(StrTmp, """")(0), "(", "") StrConv = Evaluate(Replace(Replace(StrConv, "-", "+"), "'", "*12+0")) If IsNumeric(StrConv) Then StrOut = StrOut & " " & GetDiameter(CSng(StrConv)) & "mm" & " (" & StrTmp & ")" Else StrOut = StrOut & " " & StrTmp End If Case Else Select Case Right(StrTmp, 2) 'If it ends with FT, try an ft:m conversion Case "FT" StrConv = Replace(Split(StrTmp, "FT")(0), "(", "") If IsNumeric(StrConv) Then StrOut = StrOut & " " & Format(StrConv * 0.3048, "0.0") & "m" & " (" & StrConv & "')" Else StrOut = StrOut & " " & StrTmp End If 'If it ends with IN, try an in:mm conversion Case "IN" StrConv = Replace(Split(StrTmp, "IN")(0), "(", "") If IsNumeric(Left(StrConv, 1)) Then StrConv = Evaluate(Replace(Replace(StrConv, "-", "+"), "'", "*12+0")) End If If IsNumeric(StrConv) Then StrOut = StrOut & " " & GetDiameter(CSng(StrConv)) & "mm" & " (" & StrConv & """)" Else StrOut = StrOut & " " & StrTmp End If Case Else 'If it's not ft/in and we haven't reached the end, look for other possibilities If j < k Then StrConv = Replace(Split(StrTmp, """")(0), "(", "") If IsNumeric(StrConv) Then 'If the next 'word' is IN/INCH, do an in:mm conversion If UCase(Left(Split(.Text, " ")(j + 1), 2)) = "IN" Then StrConv = Evaluate(Replace(Replace(StrConv, "-", "+"), "'", "*12+0")) StrOut = StrOut & " " & GetDiameter(CSng(StrConv)) & " mm" & " (" & StrConv & """)" j = j + 1 'If the next 'word' is GPM, do a GPM:CMH conversion ElseIf UCase(Left(Split(.Text, " ")(j + 1), 3)) = "GPM" Then StrOut = StrOut & " " & Format(StrConv * 3.785412, "0.0") & " L/Min" & " (" & StrConv & " GPM)" j = j + 1 'If the next 'word' is GAL, do a Gal:Ltr conversion ElseIf UCase(Left(Split(.Text, " ")(j + 1), 3)) = "GAL" Then Select Case StrConv * 3.785412 Case Is >= 100 StrOut = StrOut & " " & Format(StrConv * 0.3785412, "0") * 10 & " Ltr" & " (" & StrConv & " GAL)" Case Is >= 10, Is < 100 StrOut = StrOut & " " & Format(StrConv * 3.785412, "0") & " Ltr" & " (" & StrConv & " GAL)" Case Else StrOut = StrOut & " " & Format(StrConv * 3.785412, "0.0") & " Ltr" & " (" & StrConv & " GAL)" End Select j = j + 1 'If the next 'word' is GPD, do a GPD:LPD conversion ElseIf UCase(Left(Split(.Text, " ")(j + 1), 3)) = "GPD" Then Select Case StrConv * 3.785412 Case Is >= 100 StrOut = StrOut & " " & Format(StrConv * 0.3785412, "0") * 10 & " LPD" & " (" & StrConv & " GPD)" Case Is >= 10, Is < 100 StrOut = StrOut & " " & Format(StrConv * 3.785412, "0") & " LPD" & " (" & StrConv & " GPD)" Case Else StrOut = StrOut & " " & Format(StrConv * 3.785412, "0.0") & " LPD" & " (" & StrConv & " GPD)" End Select j = j + 1 'If the next 'word' is G/HR, do a G/HR:L/HR conversion ElseIf UCase(Left(Split(.Text, " ")(j + 1), 4)) = "G/HR" Then Select Case StrConv * 3.785412 Case Is >= 100 StrOut = StrOut & " " & Format(StrConv * 0.3785412, "0") * 10 & " LPH" & " (" & StrConv & " GPH)" Case Is >= 10, Is < 100 StrOut = StrOut & " " & Format(StrConv * 3.785412, "0") & " LPH" & " (" & StrConv & " GPH)" Case Else StrOut = StrOut & " " & Format(StrConv * 3.785412, "0.0") & " LPH" & " (" & StrConv & " GPH)" End Select j = j + 1 'If the next 'word' is LB, do a LB:KG conversion ElseIf UCase(Left(Split(.Text, " ")(j + 1), 2)) = "LB" Then Select Case StrConv / 2.20462 Case Is >= 100 StrOut = StrOut & " " & Format(StrConv / 2.20462, "0") * 10 & " KG" & " (" & StrConv & " LB)" Case Is >= 10, Is < 100 StrOut = StrOut & " " & Format(StrConv / 2.20462, "0") & " KG" & " (" & StrConv & " LB)" Case Else StrOut = StrOut & " " & Format(StrConv / 2.20462, "0.0") & " KG" & " (" & StrConv & " LB)" End Select j = j + 1 'If the next 'word' is FT^2, do a FT^2:M^2 conversion ElseIf UCase(Replace(Split(.Text, " ")(j + 1), ")", "")) = "FT^2" Then StrOut = StrOut & " " & Format(StrConv / 10.76391, "0.00") & " M^2" & " (" & StrConv & " FT^2)" j = j + 1 'If the next two 'words' are SQ. FT., do a FT^2:M^2 conversion ElseIf UCase(Left(Split(.Text, " ")(j + 1), 2)) = "SQ" And UCase(Left(Split(.Text, " ")(j + 2), 2)) = "FT" Then StrOut = StrOut & " " & Format(StrConv / 10.76391, "0.00") & " M^2" & " (" & StrConv & " FT^2)" j = j + 2 'If the next 'word' is PSI, do a PSI:KPA conversion ElseIf UCase(Left(Split(.Text, " ")(j + 1), 3)) = "PSI" Then StrOut = StrOut & " " & Round(StrConv * 6.894757, 0) & " KPA" & " (" & StrConv & " PSI)" Else StrOut = StrOut & " " & StrTmp End If ' Test for a cell containing a numeric range 'ElseIf InStr(StrConv, "-") > 0 Then 'If IsNumeric(Split(StrConv, "-")(0)) And IsNumeric(Split(StrConv, "-")(1)) Then 'If the next 'word' is PSI, do a PSI:KPA conversion 'If Left(Split(.Text, " ")(j + 1), 3) = "PSI" Then 'StrOut = StrOut & " " & Round(Split(StrConv, "-")(0) * 6.894757, 0) & "-" & Round(Split(StrConv, "-")(1) * 6.894757) & " KPA" & " (" & StrConv & " PSI)" 'j = j + 1 'End If 'If the next 'word' begins with IN or Hg, do an in:mm conversion 'If UCase(Left(Split(.Text, " ")(j + 1), 2)) = "HG" Then 'StrOut = StrOut & " " & Round(Split(StrConv, "-")(0) * 25.4, 0) & "-" & Round(Split(StrConv, "-")(1) * 25.4, 0) & "mm" & " (" & StrConv & """) Hg" 'j = j + 2 'End If 'If UCase(Left(Split(.Text, " ")(j + 1), 2)) = "IN" Then 'StrOut = StrOut & " " & Round(Split(StrConv, "-")(0) * 25.4, 0) & "-" & Round(Split(StrConv, "-")(1) * 25.4, 0) & "mm" & " (" & StrConv & """)" 'j = j + 2 'End If 'End If Else StrOut = StrOut & " " & StrTmp End If Else StrOut = StrOut & " " & StrTmp End If End Select End Select Else StrOut = StrOut & " " End If Next StrOut = Trim(StrOut) If InStr(StrOut, " X ") > 0 Then StrConv = "" For j = 0 To UBound(Split(StrOut, " X ")) Step 2 StrTmp = Split(StrOut, " X ")(j) For k = 0 To UBound(Split(StrTmp, " ")) - 1 StrConv = StrConv & " " & Split(StrTmp, " ")(k) Next StrConv = StrConv & " * " & Split(Split(StrOut, " X ")(j + 1), " ")(0) StrConv = StrConv & " " & Replace(Split(StrTmp, " ")(k), ")", " x ") StrConv = StrConv & Replace(Split(Split(StrOut, " X ")(j + 1), " ")(1), "(", "") Next For j = 1 To UBound(Split(StrOut, " X ")) Step 2 StrTmp = Split(StrOut, " X ")(j) For k = 2 To UBound(Split(StrTmp, " ")) StrConv = StrConv & " " & Split(StrTmp, " ")(k) Next Next StrOut = StrConv End If .Value = Trim(StrOut) End With Next End With Application.ScreenUpdating = True End Sub
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#17
|
|||
|
|||
I'm not getting any errors which is nice and everything that I need converted converts. Paul, thank you so much for your help. I wish I could learn this stuff quicker so I wouldn't have to rely on you so much, it's slow for me but I'm gaining more knowledge each time I do a project like this. Thank you thank you.
|
#18
|
|||
|
|||
Okay Paul, I inserted this code into our overall spreadsheet which contains several different tabs and multiple programs that we've written over the years. Integrating this code into our spreadsheet gives me errors. I spent a day thoroughly going through debugging procedures to pinpoint when and where the error arises. I know this was supposed to be solved but new issues have arisen. Should I create a new thread?
|
#19
|
||||
|
||||
Continue in this thread - I'll remove the 'solved' tag for now.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#20
|
|||
|
|||
I'm getting the "Run-time error '438': Object doesn't support this property or method", with this line highlighted:
Code:
Case "IN" StrConv = Replace(Split(StrTmp, "IN")(0), "(", "") If IsNumeric(Left(StrConv, 1)) Then StrConv = Evaluate(Replace(Replace(StrConv, "-", "+"), "'", "*12+0")) Code:
Case """" StrConv = Replace(Split(StrTmp, """")(0), "(", "") StrConv = Evaluate(Replace(Replace(StrConv, "-", "+"), "'", "*12+0")) Code:
Case Else 'If it's not ft/in and we haven't reached the end, look for other possibilities If j < k Then StrConv = Replace(Split(StrTmp, """")(0), "(", "") If IsNumeric(StrConv) Then 'If the next 'word' is IN/INCH, do an in:mm conversion If UCase(Left(Split(.Text, " ")(j + 1), 2)) = "IN" Then StrConv = Evaluate(Replace(Replace(StrConv, "-", "+"), "'", "*12+0")) I have tried modifying the list and some of the code to see if I can fix it but am unable to. I have researched the error a bit but can't find any info directly correlated with my issue. Thanks again. Last edited by macropod; 10-01-2014 at 11:48 PM. Reason: Replaced large images with code snippets |
#21
|
||||
|
||||
What does the worksheet row on which the error occurs contain?
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#22
|
|||
|
|||
It's the Basket Strainer "MERMADE, 6IN X 3IN ECCENTRIC REDUCING STRAINER" and the other one is the Thermometer "TRERICE AX9, 7 INCH (180mm)ADJUSTABLE ANGLE W/ THERMOWELL".
Also I had a question about the code. What does the "*12+0" do in that line that's getting the error? I know its for the Replace as String part but what exactly does it do? I noticed that if I change it to any number or equation it still works. |
#23
|
||||
|
||||
Quote:
MERMADE, 150mm * 75mm (6" x 3") ECCENTRIC REDUCING STRAINER Quote:
Quote:
Quote:
1' 3" and replace the ' with *12+0, so you end up with: 1*12+03 for a feet_&_inches:inches conversion (in this case, 15in), so the in:mm conversion can be done.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#24
|
|||
|
|||
Ah that explanation for the *12+0 makes perfect sense, thank you.
This is where it's going to get tough because I've now added the code into a whole new environment which you won't be able to simulate. None of these errors occur in my original spreadsheet, like one that you would be using - a stand alone spreadsheet with no other code, but with it now integrated into this new spreadsheet it comes up with these errors. I have deleted all metric units within the list, that is something we can easily alter. I shouldn't get any issues with that. I also added the 7in:180mm conversion to the diameters. Do you know why this error would show up for any reason given the code used and parameters set? This may be difficult because you are unable to recreate the error. I have searched for reasons this error would come up and nothing I have read compares to my situation. |
#25
|
||||
|
||||
Since I have no idea what your other code might be doing, it's rather difficult to give advice on how to address any issues thus created. A workaround might be to copy the data to another workbook containing only the code I've provided, run my macro, then copy the data back again. That way, any interactions with whatever your existing code does could be eliminated.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#26
|
|||
|
|||
I got the error to go away! The error was occurring on the code line:
Code:
StrConv = Evaluate(Replace(Replace(StrConv, "-", "+"), "'", "*12+0")) I decided to run one final run and found another component that I was omitting (we delete equipment that we're not using for the specific job from the list). I will send you the list with no equipment deleted. The Chemical Controller model is being converted into inches. You'll see what I mean when you insert it. The only way I can think of getting around it is by adding a space after the number and before the comma. This works but it doesn't look good and it's not grammatically correct. I would like to leave it as is without it converting. Watching the Locals window it converts it because StrTmp (250,) and (2000,) are greater than 0: Code:
For j = 0 To k StrTmp = Split(.Text, " ")(j) If Len(StrTmp) > 0 Then Also I tried finding a thread about superscripting in vba but couldn't find anything. Not sure if this is the way I want to go but is it possible? Specifically I'm talking about superscripting the '2' in the ft^2 for the filter or others that might show up. |
#27
|
|||
|
|||
Now when I comment out those three lines of code some of the " (inch) dimensions aren't converting.
Code:
Case """" StrConv = Replace(Split(StrTmp, """")(0), "(", "") 'StrConv = Evaluate(Replace(Replace(StrConv, "-", "+"), "'", "*12+0")) If IsNumeric(StrConv) Then StrOut = StrOut & " " & GetDiameter(CSng(StrConv)) & "mm" & " (" & StrTmp & ")" Else StrOut = StrOut & " " & StrTmp End If |
#28
|
||||
|
||||
To handle strings like:
CHEMTROL 250, INCLUDE INDUSTRIAL PROBES insert: 'If a ',', skip it Case ",": StrOut = StrOut & " " & StrTmp after: Select Case UCase(Right(StrTmp, 1)) You might also want to add some order-of-magnitude roundings for the GPM, too. As for the superscripting, the simplest solution would be to use SQ.M, SQ.FT, etc, instead. Otherwise, because text strings have no formatting, what is required is for the code to locate each ^2 sequence in the cell after it's been re-populated, select those characters and superscript them. You can do that with code like: Code:
Sub Superscript(Rng As Range) Dim ArrSupr As Variant, i As Long, j As Long, k As Long ArrSupr = Array("^2", "^3") For i = LBound(ArrSupr) To UBound(ArrSupr) With Rng j = 1 Do While j > 0 k = Len(ArrSupr(i)) j = InStr(j, .Value, ArrSupr(i), vbTextCompare) If j > 0 Then .Characters(Start:=j, Length:=k).Font.Superscript = True j = j + k End If Loop End With Next End Sub Call Superscript(.Cells(i, 3)) before the final: Code:
Next End With
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] Last edited by macropod; 10-02-2014 at 03:44 PM. Reason: Minor code fix |
#29
|
|||
|
|||
The code for skipping Case"," worked perfectly. The Sub Superscript also worked great, that was neat....now to see what we want our standard to be. Thanks again.
Any advise for post #26 and #27 regarding modification to the line that gives me an error? |
#30
|
||||
|
||||
With the mods in post #28, I don't see any errors - and that's with the three lines:
StrConv = Evaluate(Replace(Replace(StrConv, "-", "+"), "'", "*12+0")) still in the code.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Words in my document were converted to numbers???? | MikeD23 | Word | 1 | 08-26-2012 11:09 AM |
can word convert numbers? | koolfire | Word | 2 | 01-07-2012 02:12 PM |
Convert numbers to a specific text string | francis | Excel | 1 | 10-06-2011 01:43 PM |
Numbers in Words | janak | Excel | 2 | 12-19-2010 08:53 PM |
Numbers Convert in word in MS Access | towhid | Office | 0 | 08-19-2010 01:17 AM |