Little problem

[bojan0810]

I have little problem. Again.

https://dl.dropboxusercontent.com/u/57916703/Origianl_file_Analytics NEW3..xlsm

This is the file.

When you hit button on Dataset1 it looks words on J column and if column B has that words it moves to Black. If it doesnt have it moves to white.
So that works even it takes a while...

Now. My problem is on "Action" Sheet. Column A compares 2 columns, it compares column A on black and column B on white. If it finds on both sheet thenleaves blank if it is only on white, then it write white value.

But, it doesnt working like that, for first file it worked like a charm, I changed data and now it doesnt work like that.

For example on white sheet in column a there is code(A3) 2453218, that codeisnt in black sheet and it should write 2453218 in A3 on Action sheet. Instead of that it writes N/A. I tried to "convert" cells to numbers because of error, but that isnt a problem.

I hope someone can help me with this.

 

[Claus Busch]

Hi Bojan,

Am Fri, 16 May 2014 11:12:16 -0700 (PDT) schrieb (e-mail address removed):

Now. My problem is on "Action" Sheet. Column A compares 2 columns, it compares column A on black and column B on white. If it finds on both sheet then leaves blank if it is only on white, then it write white value.

change the formula in "Action" A2 to:
=IF(White!A2="","",IF(ISNUMBER(MATCH(White!A2,Black!A:A,0)),"",White!A2))


Regards
Claus B.

 

[bojan0810]

change the formula in "Action" A2 to:

=IF(White!A2="","",IF(ISNUMBER(MATCH(White!A2,Black!A:A,0)),"",White!A2))

huh... That worked like an charm. Do you know why mine formula didnt worked?

And thanks for formula. I thought I tried all formulas, but I guess I didnt lol

 

[GS]

Wizard!<g>

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[Claus Busch]

Hi Bojan,

Am Fri, 16 May 2014 12:03:21 -0700 (PDT) schrieb (e-mail address removed):

And thanks for formula. I thought I tried all formulas, but I guess I didnt lol

your formula gives a #NA if the number from White is not in Black.

To fasten your code try:

Sub Rectangle1_Click()
Columns("C:F").Clear

Dim pvt As PivotTable
Dim sh As Worksheet

Application.ScreenUpdating = False
Dim ListOfWords As Variant, EndRw As Long
'''Amend the following range to the real range containing your list of
words
ListOfWords = Range("J1:J" & Cells(Rows.Count, "J").End(xlUp).Row)
EndRw = Cells(Rows.Count, "B").End(xlUp).Row
For i = LBound(ListOfWords) To UBound(ListOfWords)
If WorksheetFunction.CountIf(Range("B2:B" & EndRw), "=*" &
ListOfWords(i, 1) & "*") > 0 Then
Range("A1:B" & EndRw).AutoFilter field:=2, Criteria1:="=*" &
ListOfWords(i, 1) & "*"
Range("A2:B" & EndRw).SpecialCells(xlCellTypeVisible).Copy _
Sheets("Black").Cells(Rows.Count, "A").End(xlUp)(2)
End If
Next i
ActiveSheet.AutoFilterMode = False

Dim sourceColumn As Range, targetColumn As Range

Set sourceColumn = Worksheets("Dataset1").Columns("A:B")
Set targetColumn = Worksheets("White").Columns("A:B")

sourceColumn.Copy Destination:=targetColumn

For Each sh In Worksheets
For Each pvt In sh.PivotTables
pvt.RefreshTable
Next pvt
Next sh
'Columns("a:b").Clear
Application.ScreenUpdating = True
End Sub


Regards
Claus B.

 

[bojan0810]

Wow. Now its like formula got new processor installed. Much much better and faster.

I tested it with few different datas that I had, one is shorter one is much longer then that I showed you. And it worked great. With last code it would take much longer to finish.

Thanks for this.

 

[Claus Busch]

Hi Garry,

Am Fri, 16 May 2014 15:14:59 -0400 schrieb GS:

Wizard!<g>

no, only fx. Please press button ;-)



Regards
Claus B.

 

[GS]

Hi Garry,

Am Fri, 16 May 2014 15:14:59 -0400 schrieb GS:


no, only fx. Please press button ;-)

Yes, I saw that! Nice resolve...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[bojan0810]

I kinda got into problem.

I have 3007 rows of data(3006 if you dont count headers)

After "coding" black gets 3609 rows of data and white 3007 rows. Not sure what is problem

 

[Claus Busch]

Hi again,

Am Sun, 18 May 2014 00:47:54 -0700 (PDT) schrieb (e-mail address removed):

I have 3007 rows of data(3006 if you dont count headers)

After "coding" black gets 3609 rows of data and white 3007 rows. Not sure what is problem

try it with following code:

Sub Rectangle1_Click()
Columns("C:F").Clear

Dim pvt As PivotTable
Dim sh As Worksheet

Application.ScreenUpdating = False
Sheets("Black").Range("A2:B10000").ClearContents

Dim ListOfWords As Variant, EndRw As Long
'''Amend the following range to the real range containing your list of
words
ListOfWords = Range("J1:J" & Cells(Rows.Count, "J").End(xlUp).Row)
EndRw = Cells(Rows.Count, "B").End(xlUp).Row
For i = LBound(ListOfWords) To UBound(ListOfWords)
If WorksheetFunction.CountIf(Range("B2:B" & EndRw), "=*" &
ListOfWords(i, 1) & "*") > 0 Then
Range("A1:B" & EndRw).AutoFilter field:=2, Criteria1:="=*" &
ListOfWords(i, 1) & "*"
Range("A2:B" & EndRw).SpecialCells(xlCellTypeVisible).Copy _
Sheets("Black").Cells(Rows.Count, "A").End(xlUp)(2)
End If
Next i
ActiveSheet.AutoFilterMode = False

With Sheets("Black")
EndRw = Cells(Rows.Count, 1).End(xlUp).Row
.Range("A1:B" & EndRw).RemoveDuplicates Columns:=Array(1, 2), _
Header:=xlYes
End With

Dim sourceColumn As Range, targetColumn As Range

Set sourceColumn = Worksheets("Dataset1").Columns("A:B")
Set targetColumn = Worksheets("White").Columns("A:B")

sourceColumn.Copy Destination:=targetColumn

For Each sh In Worksheets
For Each pvt In sh.PivotTables
pvt.RefreshTable
Next pvt
Next sh
'Columns("a:b").Clear
Application.ScreenUpdating = True
End Sub


Regards
Claus B.

 

[bojan0810]

Hi Claus. Thanks for answering...

I am getting error on
..Range("A1:B" & EndRw).RemoveDuplicates Columns:=Array(1, 2), _
Header:=xlYes

Maybe I just copied wrong

 

[Claus Busch]

Hi,

Am Sun, 18 May 2014 02:25:47 -0700 (PDT) schrieb (e-mail address removed):

.Range("A1:B" & EndRw).RemoveDuplicates Columns:=Array(1, 2), _
Header:=xlYes

look here:
https://onedrive.live.com/?cid=9378...#cid=9378AAB6121822A3&id=9378AAB6121822A3!326
for "Analytics".
Rightclick and download the file because macros are disabled in
OneDrive.


Regards
Claus B.

 

[bojan0810]

I think I copied wrong then.

I have slighty problem. Well at end on action what I need codes only they are ok. Because white ones are all rest that arent on black list...

But can I move on white sheet just remaining one that arent on black list?

Even as I said that isnt important much because I need "action" sheet at end only...

Thanks for helping me.

 

[Claus Busch]

Hi,

Am Sun, 18 May 2014 03:22:48 -0700 (PDT) schrieb (e-mail address removed):

But can I move on white sheet just remaining one that arent on black list?

have another look for "Analytics".


Regards
Claus B.

 

[bojan0810]

Hi there Claus. Thanks again for helping me lol. Maybe you are getting annoyed with me already lol...

Anyway, after adding new data with new words etc, black is doing all, white only gets codes and then I am getting error "Type mismatch" at

Sheets("White").Range("B2").Resize(rowsize:=UBound(arrOutB) + 1) = Application.Transpose(arrOutB)

Thanks

 

[Claus Busch]

Hi,

Am Sun, 18 May 2014 07:40:58 -0700 (PDT) schrieb (e-mail address removed):

Anyway, after adding new data with new words etc, black is doing all, white only gets codes and then I am getting error "Type mismatch" at

Sheets("White").Range("B2").Resize(rowsize:=UBound(arrOutB) + 1) = Application.Transpose(arrOutB)

try the new "Analytics"


Regards
Claus B.

 

[bojan0810]

unfortunately still same.

Try to add data from this https://www.dropbox.com/s/ub8by53ut8tzeqz/trythis.xlsx

With this data I am testing and error is type missmatch.

P.S. Sorry for bothering you and thanks for helping me

 

[Claus Busch]

Hi Bojan,

Am Sun, 18 May 2014 10:40:36 -0700 (PDT) schrieb (e-mail address removed):

unfortunately still same.

I forgot a limitation in arrays. If you want to transpose an array the
count of characters can be max. 255. But in your data you have some
hyperlinks with much more characters. Now I have cut the count of
characters in "White".
Please look again for "Analytics"


Regards
Claus B.

 

[Claus Busch]

Hi Bojan,

Am Sun, 18 May 2014 21:18:01 +0200 schrieb Claus Busch:

Please look again for "Analytics"

if you need the complete length of the hyperlinks then look for
"Analytics2"


Regards
Claus B.

 

[Claus Busch]

Hi Bojan,

Am Sun, 18 May 2014 21:45:59 +0200 schrieb Claus Busch:

if you need the complete length of the hyperlinks then look for
"Analytics2"

I changed again the code because you had some words twice in "Dataset1"
column J
Now such errors will be solved with the new code.
The changing is not made in "Analytics" because I would prefer to use
"Analytics2"


Regards
Claus B.