I can look at this for you immediately
Posts by KjBox
-
-
I sent you a PM, please check
-
I can take a look at it for you
-
My code works only for a single column, in this case Column D.
Line 4 of the code determines which column is to be used. .Columns(4) means column D (D is the fourth column, A is 1, B is 2 etc). So, to have the code work with a different column just change the 4 to whatever column number is required.
If the column that needs to have data split is not the last column in your table then you will have to include adding an additional column in the code.
Let me know if that is the case and you are not sure how to add the additional code.
-
Have you tried my code in post #4? That does as you want.
-
I can take a look at this for you, if Roy does not take it on, dependent upon offered price.
-
Try This
Display MoreCodeAssumes that the first cell in column D is a header. If not so, then changeFor i = 2 To UBound(x, 1) to For i = 1 To UBound(x, 1)
-
Thanks, trunted, pleased you got the thread updates now. Go for it!
-
If you are unable to connect with trunten, I can have a look at this for you.
I would need confirmation from trunten that he is not continuing with this before I could take over.
-
-
Payment received, many thanks.
File sent as PM
-
I have a solution for you.
I will PM you my PayPal details and post the file here upon receipt of payment.
-
Your sample Destination sheet shows just "Scenario 1".
Where should "Scenario 2" be placed?
- To the right of Scenario 1? If so then should it begin in G1 or H1 (i.e. should each scenario be separated by an empty column?
- Below Scenario 1? If so then should it begin in A24 or A25 (i.e. should each scenario be separated by an empty row?
-
-
Revised code with error trapping sent via PM
-
Revised code sent via PM
-
Got it, thanks, testing now
-
Don't worry about the Data/Pivot file without the query, I have done that, but I still need the images to conduct a proper test.
-
What error do you get, and on which line of code?
It all works for me, but then I had to hard code for the path to a picture.
I could not update the paths in the data table because then refreshing the pivot caused an error because the power query tried to refresh too, but of course it could not.
Could you send me a file containing all the images you have links to in the data table, and remove the power query from the data/pivot file.
I can then do a full test of the code.
-
Try
Display MoreCode- Sub SNREPORT()
- Dim x, i As Long, ii As Long
- With Sheets(1).Cells(1).CurrentRegion
- With .Offset(1).Resize(.Rows.Count - 1)
- x = .Value2
- For i = 1 To UBound(x, 1)
- x(i, 10) = Replace(Replace(Replace(LCase(Trim(x(i, 10))), " x", vbNullString), ".", vbNullString), "null", vbNullString)
- x(i, 13) = Replace(Replace(x(i, 13), " x", vbNullString), ".", vbNullString)
- x(i, 24) = Trim(x(i, 24))
- x(i, 15) = Right(Trim(x(i, 15)), 4)
- x(i, 24) = IIf(Trim(x(i, 24)) = "", "Verify Title", _
- IIf(LCase(Trim(x(i, 24))) = "n/a - not applicable", "Verify Title", x(i, 24)))
- x(i, 27) = Replace(Trim(x(i, 27)), " 1101 Maine Street", vbNullString)
- If Right(Trim(x(i, 27)), 3) = "HOS" Then
- x(i, 27) = Replace(x(i, 27), "HOS", "HOSPITAL")
- ElseIf Right(Trim(x(i, 27)), 4) = "HOSP" Then
- x(i, 27) = Replace(x(i, 27), "HOSP", "HOSPITAL")
- End If
- x(i, 28) = Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(LCase(x(i, 28)), _
- "street", "ST"), "road", "RD"), "drive", "DR"), "parkway", "PKWY"), ".", ""), "avenue", "AVE"), "suite", "STE"), _
- "north", "N"), "south", "S"), "west", "W"), "east", "E"), "court", "CRT")
- Next
- .Value2 = x
- For ii = 1 To UBound(x, 2)
- .Columns(ii).NumberFormat = IIf(ii = 15, "0000", "General")
- Next
- End With
- Application.Goto .Parent.[a1]
- End With
- End Sub