Please review this macro? (it seems to work) and an additional question

Windy Skies

New Member
Joined
Dec 27, 2012
Messages
47
Based on: How to Automatically Autofilter Based Upon 'Contains Cell Value'

I have no clue what I am doing, so I am actually extremely surprised that it seems to work.

This is supposed to look for a value in cell E3, and filter the range $A$1:$A$46 to only display the rows that contain the value in that cell.

Code:
Sub Macro2()
'
' Macro2 Macro

Dim sCriteria As String

    Range("E3").Select
    Selection.Copy
sCriteria = "*" & Range("E3").Value & "*"
'

'

    ActiveSheet.Range("$A$1:$A$46").AutoFilter Field:=1, Criteria1:=sCriteria, _
        Operator:=xlAnd
End Sub


My previous post: http://www.mrexcel.com/forum/excel-...lter-based-contains-value-reference-cell.html

I have a LONG list of words I would like to associate with the phrases in the book they are used in. I could run this macro a thousand times and copy the result. It would be easier for me, for now and for future projects, if Excel could automatically generate a table like so:

Ideally, but I have no idea how to do this, Excel could do this automatically: based on a list of a 1000 words, generate a multiple-column table (word, associated phrase #1, associated phrase #2, ...., associated phrase #6 )
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
...I've messed around with that a bit... First, I can't seem to input a reference cell as the criteria range? Second, if that were possible, I don't know if I could edit the filter to display the rows that contain the value in the reference cell.

And I don't know how that would result in less steps, as I have to do this thousands of times. If it does, I'm happy to learn how.
 
Upvote 0
Can you upload anything so i know what you're working with.
It can be done with the advanced filter in vba.
 
Upvote 0
im on the run now but if you upload and havent got any replies on it later today i will have a look at it.
 
Upvote 0
Thanks for your replies.


I knew nothing of the advanced filter, now I've read up on it a bit. I think it can do the same as the macro.

What I am working with: a book in Chinese in which I've automatically counted the words. I will select, say, a thousand words to study. I would like to find the phrases in which those words are used.

As an approximation:

You may write me down in history
With your bitter, twisted lies,
You may tread me in the very dirt
But still, like dust, I'll rise.

Does my sassiness upset you?
Why are you beset with gloom?
'Cause I walk like I've got oil wells
Pumping in my living room.

Just like moons and like suns,
With the certainty of tides,
Just like hopes springing high,
Still I'll rise.

Did you want to see me broken?
Bowed head and lowered eyes?
Shoulders falling down like teardrops.
Weakened by my soulful cries.

Does my haughtiness offend you?
Don't you take it awful hard
'Cause I laugh like I've got gold mines
Diggin' in my own back yard.

You may shoot me with your words,
You may cut me with your eyes,
You may kill me with your hatefulness,
But still, like air, I'll rise.

Does my sexiness upset you?
Does it come as a surprise
That I dance like I've got diamonds
At the meeting of my thighs?

Out of the huts of history's shame
I rise
Up from a past that's rooted in pain
I rise
I'm a black ocean, leaping and wide,
Welling and swelling I bear in the tide.
Leaving behind nights of terror and fear
I rise
Into a daybreak that's wondrously clear
I rise
Bringing the gifts that my ancestors gave,
I am the dream and the hope of the slave.
I rise
I rise
I rise.

Example words to find: rise, dream, hope.



Ideal format of result:

RiseBut still, like dust, I'll rise.Still I'll rise.But still, like air, I'll rise.Does it come as a surprise
DreamI am the dream and the hope of the slave.
Hope Just like hopes springing high,I am the dream and the hope of the slave.

<tbody>
</tbody>
 
Upvote 0
I don't see an option to include an attachment? I only planned to make the file after I knew how to do this. Anyway, here is a bit from the actual book. The lines are still longer than they need to be:

Sample of words to look up:

知道
毛巾
东西

<tbody>
</tbody>


Sample of text:

 
Code:
[SIZE=1]就在这个星期四,有个东西无声无息地穿行在距离地球表面很多英里之上的电离层中。
实际上,应该说是—些东西,好几十个巨大而粗矮的黄色板状物,像办公楼一样大,像鸟一样无声无息。
它们轻快地滑翔着,沐浴在这颗叫做太阳的恒星的电磁射线中,花费时间集结、编队、准备着。
  它们下面这颗行星完全没有意识到它们的到来,惟一探测到它们的是一个叫做亚以太感应器的黑色微型仪器,它开始无声地闪烁起来。
它被放在一个皮质小背包里,福特·普里弗克特总是习惯把这个小背包挂在脖子上。
福特·普里弗克特的小背包里面其实相当有趣,足以使地球上的任何物理学家瞠目结舌,所以他总是把两本已经卷角的剧本(他假装自己正准备去这两个剧组试镜)放在面上遮住。
除了亚以太感应器和剧本之外,包里还有一个电子拇指——一根粗短的黑色棒子,光滑但没有光泽,一头有两个扁平的开关和刻度盘。
还有一个仪器,看起来很像是个巨大的电子计算器,上面有上百个扁平的小按键,以及一个大约4英寸见方的屏幕,上百万个“页面”中的任意一个转瞬之间就能被调出来显示在上面。
这玩意儿看上去复杂得简直快要让人疯掉了,也许这就是尺寸刚好盖着它的那个塑料壳子上以大而友善的字体写着“不要恐慌”的原因之一吧。
另一个原因则是,这个仪器实际上就是小熊星座那些伟大的出版公司所出版过的所有书中最非凡一本——《银河系漫游指南》。
至于为什么它要以这种基于微亚介子的电子化形式出版,那是由于如果采用普通的纸媒形式印刷的话,一个星际间的漫游者将不得不随身准备好几座大楼才能装得下它。
  在福特·普里弗克特的小背包里,这几件东西下面是几支圆珠笔、一个笔记本,以及一条在M&S超市买的大洗澡毛巾。
  《银河系漫游指南》中关于毛巾这个词条也有一些解释。
  一条毛巾,它解释说,大概是对一个星际漫游者来说最有用的东西了。
从一个方面看,毛巾有着巨大的实用价值:但更重要的是,毛巾有着巨大的心理学上的价值。
也不知道出于什么原因,如果一个“正常人”(正常人:非漫游者)发现一个漫游者随身带着毛巾,那么他会很自然地认为此人同样也有牙刷、浴衣、肥皂、装饼干的罐子、保温瓶、指南针、地图、绳捆、灭蚊喷剂、雨衣、太空服……等等。
于是乎,他会很乐意借给这个漫游者所有这些东西,甚至还有其他的许多东西——而这些东西通通是这个漫游者碰巧“丢失”了的。
这个正常人的心理就是,一个人,在广阔的银河系中漫游,在面对了许多可怕的困难并且成功地战而胜之以后,他如果仍然还弄得清楚自己的毛巾在哪里,那么这显然是一个值得认真对待的人。
  因此,在搭便车漫游的行话中有这么一句,就是:“嘿,你碰过那个同行的福特·普里弗克特吗?那可是个真正知道自己的毛巾在哪里的好搭档。
”(碰:知道,认识,遇见,发生过性关系;同行:确实在一起的家伙;好搭档:在一起时让人惊叹的家伙)
  “你带毛巾了吗?”福特突然对阿瑟说。
  阿瑟——这可怜的人正在对付他的第三品脱啤酒——上下打量了他几眼。
  “为什么?什么,不,没有……我应该带吗?”喝到这个时候,他对这种突兀的问题已经不再感到惊讶了。
  福特恼火地弹了一下舌头。
  “干。
”他劝道。
  就在这时,外面传来轰隆一声闷响,阿瑟被啤酒噎了一下,直跺脚。
  “出什么事了?”他喊道。
  “别担心。
”福特说,“他们还没有开始呢。
”
  “喔,谢天谢地。
”阿瑟这才放松下来。
  “可能是你的房子刚刚被推倒了。
”福特说,喝下了他的最后一品脱。
  “什么?”阿瑟叫了起来。
就在这一瞬间,福特方才咒语被打破了。
阿瑟发疯般看了看他,然后跑到窗户边上。
  “噢,上帝,他们真的那么干了!他们正在铲平我的房子。
我他妈在这该死的酒馆里干吗,福特?”
  “眼下看来这并没有什么不同嘛。
”福特说,“就让他们乐一乐吧。
”
  “乐一乐?”阿瑟咆哮起来,“乐一乐!”他又迅速地瞟了一眼窗外。
  “去他们该死的乐一乐吧!”他愤怒地嚷着,猛然冲出酒馆,差点儿带倒一个几乎已经空了的啤酒杯。
  “住手,你们这些野蛮人!你们这些破坏狂!”阿瑟大声喊道,“你们这些半疯狂的蛮子,住手,听见没有?!”
  福特看见这架势,知道自己必须跟在他后面。
所以他迅速转向酒吧服务员,他刚向他要了4袋花生米。
  “你要的,先生。
”服务员说着把花生米扔在吧台上,“28便士,谢谢。
”
  沿着乡间小道跑了一阵,阿瑟几乎快到自己的房子了。
他没有注意到天气突然之间变得很冷,他没有注意到刺骨的风,也没有注意到突然之间毫无理由砸下来的暴风雨。
他没有注意到任何事,除了那些缓缓碾过一片瓦砾的履带推土机。
这片瓦砾刚才还是他的房子。
  “你们这些野蛮人!”他高喊着,“我要起诉委员会,讨回每一个子儿!我要绞死你们,把你们五马分尸!狠狠地鞭打你们!下油锅……直到……直到……直到把你们收拾个够。[/SIZE]
 
Last edited:
Upvote 0
ok, have no idea of what im looking at but,
is each phrase in a cell? (at least it looks like it when i translate with google )
 
Upvote 0
Thanks for your reply.

Yes. It is formatted the same as the poem I posted earlier, so one could just as easily use that as a starting point.

I've tried applying an advanced filter, but that still escapes me a bit. I'll go offline now.
 
Upvote 0

Forum statistics

Threads
1,213,530
Messages
6,114,162
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top