首页 \ 问答 \ 在VBA中使用列名作为范围(Using column names as range in VBA)

在VBA中使用列名作为范围(Using column names as range in VBA)

 我有下面的代码，使用col范围作为值在工作表中搜索列。 相反，我需要使用col名（在该表中）作为搜索条件并执行相同的功能。 我如何实现这一点？  
Sub WBR()
Dim Count1Criteria As Variant
Dim Count3Criteria As Variant
Dim test As Variant
Dim wf As WorksheetFunction
Set wf = Application.WorksheetFunction

Filter1InSummary = Array(Array("AE4", "Latency", "O:O", "Pass"), _
                     Array("AE51", "TT", "G:G", "Yes"), _
                     Array("AE52", "TT", "G:G", "No"), _
                     Array("AE61", "Reactive", "R:R", "Item"))

Filter3InSummary = Array(Array("AE43", "TT", "I:I", "<>Duplicate TT", _
                                         "G:G", "<>Not Tested", _
                                         "U:U", "Item"))
For Each test In Filter3InSummary
With Worksheets(test(1))
    Range(test(0)) = wf.CountIfs(.Range(test(2)), test(3), _
                                 .Range(test(4)), test(5), _
                                 .Range(test(6)), test(7))
End With
Next

I have the below code that searches a column in a sheet using the col range as value. Instead of that, I need to use the col name (in that sheet) as he search criteria and perform the same function. How do I implement this? 
Sub WBR()
Dim Count1Criteria As Variant
Dim Count3Criteria As Variant
Dim test As Variant
Dim wf As WorksheetFunction
Set wf = Application.WorksheetFunction

Filter1InSummary = Array(Array("AE4", "Latency", "O:O", "Pass"), _
                     Array("AE51", "TT", "G:G", "Yes"), _
                     Array("AE52", "TT", "G:G", "No"), _
                     Array("AE61", "Reactive", "R:R", "Item"))

Filter3InSummary = Array(Array("AE43", "TT", "I:I", "<>Duplicate TT", _
                                         "G:G", "<>Not Tested", _
                                         "U:U", "Item"))
For Each test In Filter3InSummary
With Worksheets(test(1))
    Range(test(0)) = wf.CountIfs(.Range(test(2)), test(3), _
                                 .Range(test(4)), test(5), _
                                 .Range(test(6)), test(7))
End With
Next

原文：https://stackoverflow.com/questions/41581155

更新时间：2024-05-02 07:05

最满意答案

 你能用COALESCE吗？  
 例如：  
SELECT COALESCE(COUNT(*),0) AS orders
FROM `orders`
GROUP BY DATE(`order_datetime`)
ORDER BY DATE(`order_time`) ASC
 
 它返回列表中的第一个非空值 - 如果它不为空则计数，如果不是则为0。  
 http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#function_coalesce  
 挂起..是没有这些日期的行的问题，所以你只是得到那些日期的结果？  
 如果是这样，您也可以返回订单日期，并且对于如何显示结果更加聪明：  
SELECT  order_datetime,COUNT(*) AS orders
FROM `orders`
GROUP BY DATE(`order_datetime`)
ORDER BY DATE(`order_time`) ASC
 
 然后在输出结果时可能会创建一个循环，遍历您感兴趣的日期，如果该日期没有行，则打印0：  
for (datetime  d = startdate; d <= enddate; datetime.adddays(1)){
    if (recordset[order_datetime] == d){
        output (d,recordset[orders]);
        recordset.movenext();
    }else
    {
        output (d,0);
    }
}
 
 （注意我不知道会是什么编程语言;-)  
 如果你想在数据库中完成所有操作，那么请查看这个SO问题，了解如何动态创建日期表，然后你就可以将它的外部联接留给你查询日期了。  
 获取两个日期之间的日期列表 

can you use COALESCE? 
eg: 
SELECT COALESCE(COUNT(*),0) AS orders
FROM `orders`
GROUP BY DATE(`order_datetime`)
ORDER BY DATE(`order_time`) ASC
 
it returns first non null value in the list - count if its not null and 0 if it is. 
http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#function_coalesce 
hang on.. is the issue that there are no rows with those dates so you just get results for those dates? 
if so you could return the order date too and be a bit more clever about how you display your result: 
SELECT  order_datetime,COUNT(*) AS orders
FROM `orders`
GROUP BY DATE(`order_datetime`)
ORDER BY DATE(`order_time`) ASC
 
then when outputting your results maybe create a loop that iterates through the dates you are interested in and prints a 0 if there is no row for that date: 
for (datetime  d = startdate; d <= enddate; datetime.adddays(1)){
    if (recordset[order_datetime] == d){
        output (d,recordset[orders]);
        recordset.movenext();
    }else
    {
        output (d,0);
    }
}
 
(note i have no idea what programming language that would be ;-) 
if you want to do it all in the DB then look at this SO question for how to create a table of the dates on the fly and then you can left outer join from it to you query on date.. 
Get a list of dates between two dates

在VBA中使用列名作为范围(Using column names as range in VBA)

最满意答案

相关问答

有什么数据库专业书籍介绍？[2022-05-19]

MySQL组由null(MySQL group by with null)[2023-09-08]

MySQL按日期分组，如果不存在，强制返回空值(MySQL group by date, force to return null value if does not exists)[2022-08-24]

MySQL Group By Date为NULL值(MySQL Group By Date for NULL values)[2021-08-13]

MySQL Group By和Skip Grouping on Null Values(MySQL Group By And Skip Grouping On Null Values)[2023-01-13]

MySQL GROUP_CONCAT与COALESCE有关的NULL值(MySQL GROUP_CONCAT vs. COALESCE concerning NULL values)[2022-08-28]

MySQL“GROUP BY NULL”有什么作用？(What does MySQL “GROUP BY NULL” do?)[2023-12-09]

mysql时间转换获取null值(mysql time to date conversion fetches null value)[2022-06-21]

UNION / GROUP BY - 防止NULL值(UNION / GROUP BY - Prevent NULL values)[2023-12-29]

MySQL GROUP除非值等于0(MySQL GROUP unless value equals 0)[2022-03-09]

相关文章

最新问答