Excel宏搜索具体安排的值可能吗？ [英] Excel macro search for values in a specific arrangement, possible?
问题描述
https://docs.google.com/file/d/0B_tgNfDq0kXAakR5eHZ3bzJQVkk/edit?usp=sharing
广告牌只是改变他们的网站，所以现在Excel的webquery返回一个非常漂亮干净的表。我在我的工作表的A和B列中创建了两个公式，A有相关日期的列表（具体是从8/18/2001到本周的每个星期六），B根据日期对图表进行超链接。图表的其余部分是为了我的顾问的利益而进行了颜色编码，他们也将在审查表。
我还手动绘制了日期2001-08-18，进入自己的工作表。这个工作表没有被触及 - 它正是webquery返回的。
正如你所看到的，该表跨越G列，每个条目占用3行。我的焦点是列C和D.列D的每个条目的行是（从上到下）标题，艺术家和印记|标签。每个条目的列C中的第一行包含该周的位置从1到200.出现的模式是以4（所以7，10，13 ...）开头的每3行包含一个位置和专辑标题（col C & D，分别），并且以5开头的每个3单元格包含一个艺术家。
我将尝试用简单的英文解释我想象的，但是要预先警告，这可能会惨败。
所以宏会把两个单元格作为输入（甚至可以输入？），专辑标题和相应的艺术家。该输入应该告诉宏宏，存储在每个单元格中的字符串和所述单元格的位置 - 例如E1，C2 - 。宏应循环遍历A列中的每个URL，从第3行到第608行，将URL查询到新的表单。这张新表格应该被激活，然后按顺序搜索从4开始的第3行，以获得专辑标题串。找到匹配后，匹配查询单元下方一行的单元格应与艺术家名称字符串进行比较。如果两个字符串与其对应的查询单元格的内容相匹配，则列C中的数字（从1到200）和与匹配的相册标题单元格相同的行应该被复制到对应于URL的bb200表中的单元格并搜索相册标题。该循环现在应该在序列中的下一个URL上重复出现。如果没有找到匹配项（相册本周没有列出，或者BB返回了一张桌面），相应的单元格应该留空。网址列表用尽后，宏应该退出。
我的问题有两个：首先，我的思考过程是关于宏观的根本原因吗？第二，但最重要的是，我没有最暗淡的线索甚至开始在VBA写这个。我已经学习过Java，C和最近的C ++（OpenGL，具体来说）。我完全不熟悉VBA语法和API，坦白说，我的时间太短了，不能坐下来正式学习语言。之后，我打算这样做很简单，但是这个任务是星期一到了，我不知道最终会有多大的努力。
对于记录，宏不是作业，而是要收集的数据是完成的。对于那些好奇的人来说，这个任务是在星期一之前完成我的高级论文的草稿。这些数据将用于创建几个图表，我的顾问已经指示我在我的写作中加入。该纸本身已经是基于简单阅读每张专辑在网站上的销售成绩编写的。
您将帮助我超越，超越其他毕业生的老年人正在转向一些严重半熟的图形表示。唯一的其他学生，远远发明了一个仪器，并提供了原理图和AutoCAD图纸。但是，在那我离题。
提前感谢帮助!!
我认为这应该让你大概90％。唯一不能做的就是网络查询。
对于那一部分，我建议您使用宏录像机进行网络查询，然后发布修订版中的代码，我们会添加它并根据您的需要进行调整。你必须做一些工作：）
Option Explicit
Sub TestMacro（）
Dim inputVal As String
Dim artistCell As Range
Dim artistName As String
Dim albumCell As Range
Dim albumName As String
Dim ws As Worksheet：set ws = Sheets（thesisData）
Dim r As Long'这将是我们的行迭代器变量
Dim hLink As String'迭代中每个超链接的字符串
Dim wsNew As Worksheet'这将在我们创建新的工作表时使用
Dim foundRange As Range'这是我们将如何找到相册
Dim weekRank As Long'从列C
错误GoTo InvalidRange'这个错误处理是为输入框捕获无效参数'
'使用一个输入框来捕获单元格地址'
inputVal = InputBox（ 请输入包含ARTIST名称的单元格位置，输入范围）
设置artistCell = Range（inputVal）'设置Range变量艺术家'
artistName = artistCell.Value'艺术家名称的字符串变量'
inputVal = vbNullString'清除inputVal'
'再次使用输入框...'
inputVal = InputBox（请输入包含ALBUM名称的单元格位置，输入范围）
设置albumCell = Range（inputVal）'为歌曲单元格设置一个Range变量
albumName = albumCell.Value'歌曲名称的字符串'
On Error GoTo 0
对于r = 3至608'遍历第3行至第608行
hLink = ws.Cells（1 ，r）.Value
'在此文件中的最后一个表格后面添加一个新的工作表'
设置wsNew = Sheets.Add（After：= Sheets（ThisWorkbook.Sheets。计数））
wsNew.Name =格式（ws.Cells（r，2）.Value，YYYY-MM-DD）
''''为网络查询添加VBA， '
''''
''''尝试使用宏录音机，我们可以根据您的需要进行调整。'
''''
'''''
''''
'而不是遍历网络查询中的所有单元格'
Do
'使用FIND方法在列D中查找匹配的相册标题'
'这使用精确的文本匹配，非区分大小写。
Dim fnd
设置foundRange = wsNew.Columns（4）.Find（What：= albumName，After：= ActiveCell，LookIn：= _
xlFormulas，LookAt：= xlWhole ，SearchOrder：= xlByRows，SearchDirection：= _
xlNext，MatchCase：= True，SearchFormat：= False）
如果没有找到Range是没有然后
'如果我们发现一个匹配，然后被偏移1行，并检查艺术家名称'
如果foundRange.Offset（1，0）= artistName然后
'同样，只是偏移foundRange单元格-1列，以获得每周排名'
weekRank = foundRange.Offset（0，-1）
'此时我不知道你想把这个值放在哪个单元格中，'
'但是我想你想要由r指定的行和'
'专辑名称的列，所以我们可以这样做：
ws.Cells（r，albumCell。列）.Value = weekRank
结束如果
结束如果
循环但未找到Range是没有
下一个
退出子'之前错误处理
InvalidRange：'错误处理
MsgBox inputVal& 不是有效范围，vbCritical，Error！
End Sub
祝你好运！
编辑，这也假设每个网络查询中只会有一个匹配项。如果有不止一个，那只会返回最后一场比赛。考虑到数据的性质，这似乎是一个安全的假设，但如果不是这样，让我知道，我可以调整它。
Here's my dilemma, I need to parse a series of 606 weeks worth of Billboard 200 charts for the position of 36 different albums. Here's what I've got so far...
https://docs.google.com/file/d/0B_tgNfDq0kXAakR5eHZ3bzJQVkk/edit?usp=sharing
Billboard just redid their website, so now Excel's webquery returns a very pretty and clean table. I created two formulas in columns A and B of my worksheet, A has the list of relevant dates (specifically every Saturday from 8/18/2001 until this week), and B makes hyperlinks to the charts based on the dates. The rest of the chart is color-coded for the benefit of my advisors, who will also be reviewing the sheet.
I've also manually webqueried the first chart, dated 2001-08-18, into its own worksheet. This worksheet hasn't been touched - its exactly what the webquery returned.
As you can see, the table spans to column G, and each entry takes up 3 rows. My focus is on columns C and D. Column D's rows for each entry are (top-to-bottom) Title, Artist, and Imprint|Label. The first row in Column C for each entry contains that week's position from 1 to 200. The pattern that emerges is that every 3rd row starting with 4 (so 7, 10, 13...) contains a position and album title (col C & D, resp.), and every 3rd cell starting with 5 contains an artist.
I'm going to try to explain what I'm imagining in plain English, but be forewarned that this may fail miserably.
So the macro would take two cells as input (can they even take input?), album title and corresponding artist. This input should tell the macro both the string stored in each cell and the location - e.g, E1, C2 - of said cell. The macro should loop through each URL in column A from row 3 to 608, querying the URL into a new sheet. This new sheet should be made active, and then every 3rd row starting with 4 should be searched sequentially for the album title string. Upon finding a match, the cell one row beneath the matching query cell should be compared to the artist name string. Should both strings match the content of their corresponding query cells, the number (from 1 to 200) in column C and the same row as the matching album title cell should be copied to the cell in the 'bb200' sheet corresponding to the URL queried and the album title searched. The loop should now recur on the next URL in the sequence. In the event no match is found (the album didn't chart that week, or BB returned a wonky table), the corresponding cell should be left blank. The macro should exit once the list of URLs is exhausted.
My problem is twofold: first off, is my thought process regarding the macro fundamentally sound? And second-but-most-importantly, I haven't the faintest clue where to even start writing this in VBA. I have studied Java, C, and most recently C++ (OpenGL, specifically). I'm totally unfamiliar with the VBA syntax and API, and frankly, my time on this is far too short to sit down and formally learn the language. After this, I plan on doing so in short order, but this assignment is due Monday and I had no idea how massive an undertaking it was going to end up being.
FOR THE RECORD, the macro is not the assignment, but the data to be collected is integral to completing it. To those curious, the assignment is to produce a complete rough draft of my senior thesis by Monday. This data will be used to create several graphs that my advisors have instructed me to include with my writing. The paper itself is already written based on simply reading each album's sales performance off the site.
You'd be helping me go above-and-beyond, as most of the other graduating seniors are turning in some seriously half-baked graphical representation. The only other student that went this far invented an instrument and provided schematics and autoCAD drawings. However, on that I digress.
Thanks in advance for the help!!
I think this should get you about 90%. The only thing this won't do is the web query.
For that part, I propose that you use the macro recorder to do a web query, and then post that code in a revision and we'll add it in and tailor it to your needs. You've gotta do some work on this :)
Option Explicit
Sub TestMacro()
Dim inputVal As String
Dim artistCell As Range
Dim artistName As String
Dim albumCell As Range
Dim albumName As String
Dim ws As Worksheet: Set ws = Sheets("thesisData")
Dim r As Long 'this will be our row iterator variable
Dim hLink As String 'string for each hyperlink in the iteration
Dim wsNew As Worksheet 'this will be used when we create new worksheets
Dim foundRange As Range 'this is how we will locate the album
Dim weekRank As Long 'weekly rank from column C
On Error GoTo InvalidRange 'This error handling is for the input box, to trap invalid arguments.'
'Use an input box to capture the cell address'
inputVal = InputBox("Please enter the cell location containing the ARTIST name", "Input Range")
Set artistCell = Range(inputVal) 'set a Range variable for the artist'
artistName = artistCell.Value 'string variable for artist name'
inputVal = vbNullString 'clear out the inputVal'
'Use an input box again...'
inputVal = InputBox("Please enter the cell location containing the ALBUM name", "Input Range")
Set albumCell = Range(inputVal) 'set a Range variable for the song cell'
albumName = albumCell.Value 'string for song name'
On Error GoTo 0
For r = 3 To 608 'iterate over rows 3 to 608
hLink = ws.Cells(1, r).Value
'Add a new sheet after the last sheet in this file'
Set wsNew = Sheets.Add(After:=Sheets(ThisWorkbook.Sheets.Count))
wsNew.Name = Format(ws.Cells(r, 2).Value, "YYYY-MM-DD")
'''' add VBA for web query, here.'
''''
'''' try using the macro recorder and we can tweak it to your needs.'
''''
''''
''''
'Rather than looping over all the cells in web query...'
Do
'Use the FIND method to look for matching album title in column D.'
' this uses exact text match, non-case-sensitive.
Dim fnd
Set foundRange = wsNew.Columns(4).Find(What:=albumName, After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=True, SearchFormat:=False)
If Not foundRange Is Nothing Then
'if we've found a match, then just offset by 1 row and check against artist name'
If foundRange.Offset(1, 0) = artistName Then
'likewise, just offset the foundRange cell by -1 columns to get the weekly rank'
weekRank = foundRange.Offset(0, -1)
'At this point I'm not sure what cell you want to put this value in, '
' but I think you want row designated by "r" and the column of the '
' album name, so we can do that like this:
ws.Cells(r, albumCell.Column).Value = weekRank
End If
End If
Loop While Not foundRange Is Nothing
Next
Exit Sub 'before error handling
InvalidRange: 'error handling
MsgBox inputVal & " is not a valid range", vbCritical, "Error!"
End Sub
Good luck!
Edit this also assumes that there is only going to be one match in each web query. In the event there is more than one, it would only return the last match. That seems like a safe assumption given the nature of the data, but if that's not the case, let me know and I can tweak it.
这篇关于Excel宏搜索具体安排的值可能吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
