PDA

View Full Version : Help with the Loop



ElGatito
06-03-2010, 02:06 PM
Hi Guys,

Sorry in advance, but on that one, I really can't figure it out. Yes, I am quite new to basic, and the fix will probably be simple to do. but here's the thing.

I just need to have a count different links in the different folders of a webpage. So I have two lists, one with the URLs, and one with the subfolders.

i crafted a small code to compare and count the number of time the two list have a similar element (Folders are surrounded by *) Each time the two list are the same Count goes plus one.

Here's the thing, it works great when done individually, but when I try to Loop it, or use For, then it take the first result and spreads it on all subfolders

So what's wrong with my Code?

Sub counturlsComplex()
Dim Target As String
Dim Compared As String
Dim Count As Integer
Dim Row As Single
Dim StartRow As Single
Dim EndRow As Single
Row = 1
StartRow = 1

For i = 1 To 215


Do Until IsEmpty(Cells(StartRow, 1))

Target = Cells(Row, 6)
Compared = Cells(StartRow, 1)
If Compared Like Target Then
Count = Count + 1
End If
StartRow = StartRow + 1

Loop
Cells(Row, 5) = Count
Row = Row + 1
Next i
End Sub

And yes If you see some elements which aren't used, its because I tried to tweak it like crazy all day.

Thanks a lot in advance, you wouldn't believe how much this would help me. (Even a tip or two to go in the good direction)

Tinbendr
06-03-2010, 02:29 PM
Some sample data would help a lot.

ElGatito
06-04-2010, 08:35 AM
Sir Yes Sir !! :bow:

Here's a test doc. What I am trying to accomplish is simply Count the number of data of different categories. So here's a small list which is similar to what I will have to work. (The data I'm handling could be considered as sensitive, sorry. :blush )

But the code and the data placement are the same.

Here's my issue. When I am giving the attribute Target a value, I think it should take the value from the number of the Row and the column E. Butfor some reason it doesn't. it seems that when it analyzes, it always takes the value of apple to compare it to the Column A list.

But, when it outputs the data, it will output it in the right Row, meaning, continue onto E2, but it will have compared the data from F1 to the Column A again.

Could it be that my formula to get the Cells value is wrong? Or is the double loop screwing my code ( Already tried to sperate it in a different sub and function though, and it would still not work)?

ElGatito
06-04-2010, 08:37 AM
Here's what the file looks like

mdmackillop
06-04-2010, 09:10 AM
It looks like CountIf would do the job for you.

For a looping solution though, something like this. Named ranges used for clarity
Sub Looping()
Dim c As Range, d As Range, cnt As Long
For Each c In Range("Cats")
cnt = 0
For Each d In Range("Data")
If d Like c Then cnt = cnt + 1
Next d
c.Offset(, -1) = cnt
Next c
End Sub

mdmackillop
06-04-2010, 09:23 AM
Here's a correction of your own code. You need to reset starting values inside the outer loop

Sub counturlsComplex()
Dim Target As String
Dim Compared As String
Dim Count As Integer
Dim Row As Single
Dim StartRow As Single
Dim EndRow As Single
Row = 1
For i = 1 To 215
StartRow = 1
Count = 0
Do Until IsEmpty(Cells(StartRow, 1))
Target = Cells(i, 6)
Compared = Cells(StartRow, 1)
If Compared Like Target Then
Count = Count + 1
End If
StartRow = StartRow + 1
Loop

Cells(Row, 5) = Count
Row = Row + 1
Next i
End Sub

ElGatito
06-04-2010, 09:56 AM
(A lot of swearing and total disbelief) It works perfectly :bow:

Thank you so much for your help. You cannot believe how much i appreciate :bow:

I will definitely take a beer in honor of the scots tonight!!