Need help with convert VBA code to another file

%3CLINGO-SUB%20id%3D%22lingo-sub-3006576%22%20slang%3D%22en-US%22%3ENeed%20help%20with%20convert%20VBA%20code%20to%20another%20file%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3006576%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20using%20office%20365%20with%20%C4%B0ntel%20Mac.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20was%20seeking%20an%20answer%20for%20my%20problem.%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1174419%22%20target%3D%22_blank%22%3E%40Quadruple_Pawn%3C%2FA%3E%3CSPAN%3E%26nbsp%3Bgave%20me%20this%20useful%20solution%20below%20which%20is%20works%20very%20well.%20Attached%20image%20'solution'.%26nbsp%3BThis%20code's%20purpose%20is%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3Bto%20print%26nbsp%3Bif%20there%20is%20a%20'True'%20in%20A%20column%2C%20it%20should%20print%20the%20corresponding%20part%20in%20D%20column%20in%20order%20to%20column%20C.%20The%20problem%20is%20old%20file%20was%20an%20example%20file%20and%20now%20I%20got%20the%20original%20data%20DataLO.xlsx%20and%20I%20couldn't%20succeed%20to%20convert%20this%20code%20to%20make%20it%20work%20with%20this%20original%20file.%20Because%20at%20some%20point%20the%20rows%20format%20is%20changing%20in%20file(see%20attached%20other%20image)%20and%20I%20don't%20know%20how%20to%20convert%20this%20code%20to%20complete%20this%20task.%20Also%20in%20original%20data%20we%20have%20additionally%20id%20in%20column%20A%2C%20true-false%20values%20are%20separated%20with%20rows%20and%20I%20need%20to%20add%20new%20column%20to%20print%20opt-in%20as%20the%20previous%20file(attached%20file%20LO%20data%20testing(1).xlsm).%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EAny%20idea%3F%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22%22%3EOption%20Explicit%3C%2FP%3E%3CP%20class%3D%22%22%3E%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22%22%3EFunction%20SelItems(rng1%2C%20rng2)%20As%20String%3C%2FP%3E%3CP%20class%3D%22%22%3EConst%20sep%20%3D%20vbLf%3C%2FP%3E%3CP%20class%3D%22%22%3EDim%20a1()%20As%20String%3C%2FP%3E%3CP%20class%3D%22%22%3EDim%20a2()%20As%20String%3C%2FP%3E%3CP%20class%3D%22%22%3EDim%20i%20As%20Long%3C%2FP%3E%3CP%20class%3D%22%22%3EDim%20j%20As%20Long%3C%2FP%3E%3CP%20class%3D%22%22%3EDim%20s%20As%20String%3C%2FP%3E%3CP%20class%3D%22%22%3Ea1%20%3D%20Split(rng1%2C%20vbLf)%3C%2FP%3E%3CP%20class%3D%22%22%3Ea2%20%3D%20Split(rng2%2C%20%22%2C%22)%3C%2FP%3E%3CP%20class%3D%22%22%3EFor%20i%20%3D%20UBound(a1)%20-%205%20To%20UBound(a1)%3C%2FP%3E%3CP%20class%3D%22%22%3EIf%20a1(i)%20%3D%20%22True%22%20Then%3C%2FP%3E%3CP%20class%3D%22%22%3Es%20%3D%20s%20%26amp%3B%20%22%2C%22%20%26amp%3B%20a2(j)%3C%2FP%3E%3CP%20class%3D%22%22%3Ej%20%3D%20j%20%2B%201%3C%2FP%3E%3CP%20class%3D%22%22%3EElse%3C%2FP%3E%3CP%20class%3D%22%22%3Ej%20%3D%20j%20%2B%201%3C%2FP%3E%3CP%20class%3D%22%22%3EEnd%20If%3C%2FP%3E%3CP%20class%3D%22%22%3ENext%20i%3C%2FP%3E%3CP%20class%3D%22%22%3EIf%20s%20%26lt%3B%26gt%3B%20%22%22%20Then%3C%2FP%3E%3CP%20class%3D%22%22%3ESelItems%20%3D%20Mid(s%2C%20Len(sep)%20%2B%201)%3C%2FP%3E%3CP%20class%3D%22%22%3EEnd%20If%3C%2FP%3E%3CP%20class%3D%22%22%3EEnd%20Function%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3006576%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ECharting%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Occasional Contributor

I'm using office 365 with İntel Mac.

 

I was seeking an answer for my problem. @Quadruple_Pawn gave me this useful solution below which is works very well. Attached image 'solution'. This code's purpose is to print if there is a 'True' in A column, it should print the corresponding part in D column in order to column C. The problem is old file was an example file and now I got the original data DataLO.xlsx and I couldn't succeed to convert this code to make it work with this original file. Because at some point the rows format is changing in file(see attached other image) and I don't know how to convert this code to complete this task. Also in original data we have additionally id in column A, true-false values are separated with rows and I need to add new column to print opt-in as the previous file(attached file LO data testing(1).xlsm). 

 

Any idea?

 

Option Explicit

 

Function SelItems(rng1, rng2) As String

Const sep = vbLf

Dim a1() As String

Dim a2() As String

Dim i As Long

Dim j As Long

Dim s As String

a1 = Split(rng1, vbLf)

a2 = Split(rng2, ",")

For i = UBound(a1) - 5 To UBound(a1)

If a1(i) = "True" Then

s = s & "," & a2(j)

j = j + 1

Else

j = j + 1

End If

Next i

If s <> "" Then

SelItems = Mid(s, Len(sep) + 1)

End If

End Function

0 Replies