Traverse content of cell using VBA

[pukiamak]

Hello Guys,

I wish to traverse content of each cell in my sheet. Let's say on

Sheet 1, in cell (1,1) I have the entry (Good,Morning,Sir).

On sheet 2, in cell (1,1) I have the entry (Good,Afternoon,Sir).

I want to traverse inside the cell so that if there is a similar data like above(Good & Sir), I want to remove both of them from the cell. So, the updated Sheet 1,cell (1,1) will have "Morning" only and On sheet 2,cell(1,1) will have "Afternoon" only.

I would like to remove all parts that match anywhere else and all parts that match anywhere else.


Thank you,
Nic

 

[taupirho]

Just do a search and replace. Record the operation as a macro and you have code already that can be used as is or modified as required

 

[abaldwin]

Judging from the previous thread that lead to the creation of this one, I have a few questions that may assist others in helping you.

1. Can you post some sample data and the expected results. One line examples in this case just dont go far enough.

2. Are you wanting to input a string to look for or are you wanting to compare every cell in a wb to every other cell in a workbook?

3. How many passes to make?

i.e.

if in cells 1,1 of 3 sheets you have

A good book

A bad book

no book at all

Then you would want to see what?

If enough passes were made you would wind up with one cell in sheet 3 that said "no at"


BTW what is this project intended to be used for. Curious as to what type of application would want to loop so many times to remove data.

Thanks


Andy Baldwin

"Testing is the most overlooked programming language on the books!

 

[PHV]

Anyway, to parse the comma separated list you may consider the Split function.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[abaldwin]

He is not wanting to parse or separate a string. He is wanting to loop through every cell and compare each word with each word in every other cell in the wb and remove the words that are the same.

Please see

http://www.tek-tips.com/viewthread.cfm?qid=1254504&page=1

Hence my request for more info.


Andy Baldwin

"Testing is the most overlooked programming language on the books!

 

[PHV]

in my case the data inside the cell is separated by comma

So, Andy, what have I misread ?

 

[abaldwin]

Sure split will break apart the contents of a cell. Read further

[q]Sheet 1, in cell (1,1) I have the entry (Good,Morning,Sir).

On sheet 2, in cell (1,1) I have the entry (Good,Afternoon,Sir).

I want to traverse inside the cell so that if there is a similar data like above(Good & Sir), I want to remove both of them from the cell. [/q]

Wants to then remove all the words that are the same. Just asking how far he wants to go.

Andy Baldwin

"Testing is the most overlooked programming language on the books!

 

[pukiamak]

Hi Andy,

Thanks a lot for the reply. For example, on cell (1,1) on 3 wb, I have:

A,good,book

A,bad,book

no,book,at,all

remember that I already have the data in the cell of every workbook. Therefore, after i run the macro, on cell (1,1) i should only have

good
bad
no,at,all

since A and book exit in the same cell on wb.

I want to compare every cell in to every same cell location in a workbook. In other words, I only want to compare cell(1,1) sheet 1 to cell(1,1) sheet 2. next, cell (2,1) sheet 1 to cell (2,1) sheet 2 and goes on.

Well, I juz got hired as accountant and they ask me if I can set up a macro to filter their data. Since I know only VBA a bit, this task coz a headache for me. Once again, I already have the data to be filtered.

Once again, thx a lot for those who reply.

 

[pukiamak]

Hi PHV,

thanks a lot for the reply. I just read ur comments and I am wondering how do i parse the comma separated list using the split function? Btw, I am thinking to split the data in the cell and put it into the array, since each content in the cell is separated comma.

suppose I have on cell (1,1) "good,morning,sir"

im thinking to make array sum()
so that sum(1)=good
sum(2)=morning
sum(3)=sir

n from then on i can juz compare the array content for sheet 1 with array on sheet 2. if they are the same, then delete it. how can do i that with coding? can u help me?

Thx a lot,
Nic

 

[firefytr]

You can set the array like so ...

Dim arrVal() as variant
If Instr(1, Cells(1, 1), ",") <> 0 then
    arrVal = Split(Cells(1, 1), ",")
End if

I like to enclose a test before I set the array, b/c if the delimiter is not there you'll get an error.

If you're only wanting to check cells, you might want to do a Do/Find/FindNext/Loop to find all instances on a sheet. Remember, the more elements you can keep in arrays and not use actual physical objects, the faster your code will run.

You also may want to check out Dave Brett's Duplicate Master here:

http://members.iinet.net.au/~brettdj/

It uses Regular Expressions and Collections like candy. Very slick.

HTH

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[pukiamak]

Hi Zack,

Thanks a lot for the code and response. I am wondering how do I split the data "(my,name),(is,nic),(and,you)" into

(my,name)
(is,nic)
(and,you)

i try to change the code but it doesnt like it.

Thx,
Nic

 

[pukiamak]

Hello Guys,

Sorry to annoy u guys a lot. I can use split function to extract the data and put it into array. Now, I have question in comparing content of array among 2 sheets.

Let's say I have on

cell(2,2): "(My,name),(is,Nic),(and,you)"
cell(2,3): "(My,name),(and,you)"

Therefore, I used two arrays name one() and two() in which

one(0)=(My,name)
one(1)=(is,Nic)
one(2)=(and,you)

two(0)=(My,name)
two(1)=(and,you)

My question, what is the algorithm/code so that when I run the macro, it should only have on

cell(2,2): "(My,name),(is,Nic),(and,you)"
cell(2,3): BLANK

since My,Name & and,you are common.

Thanks a lot for the response,
Nic

 

[pukiamak]

Hello Guys,

Any response will be great.

Thx,
Nic

 

[firefytr]

Maybe use the Split() function to spit for "),(", then trim the left char "(" from the Lbound() array and the right char ")" from the Ubound() array. Then you can do a series of loops that will loop through each element of the array and performing a Find on it checking only for partial matches. Check the address of the Found items and if it's not the same cell address, take that item out of the array (or do whatever you want to do with it).

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[pukiamak]

Hi Zack,

THx for the response. How do I take out items from the array in VBA?

thx,
NIc

 

[firefytr]

You'll need a second array; here is an example ...

Sub CutOutArrayParts()
    Dim arrOne() As String, arrTwo() As String, CellTwo As String
    Dim i As Long, iStop As Long, j As Long
    CellTwo = "(My,name),(is,Nic),(and,you)"
    arrOne = Split(CellTwo, "),(")
    arrOne(LBound(arrOne)) = Right(arrOne(LBound(arrOne)), Len(arrOne(LBound(arrOne))) - 1)
    arrOne(UBound(arrOne)) = Left(arrOne(UBound(arrOne)), Len(arrOne(UBound(arrOne))) - 1)
    Stop 'array is full here
    iStop = 1
    For i = LBound(arrOne) To UBound(arrOne)
        If i <> iStop Then
            ReDim Preserve arrTwo(0 To j)
            arrTwo(j) = arrOne(i)
            j = j + 1
        End If
    Next i
    Stop 'array is full here minus said part(s)
End Sub

HTH

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[pukiamak]

Hi Zack,

Thx a lot for the response & code.

~Nic

 

[firefytr]

No problem. Let me know if you need any of it explained.

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci