Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using variable in WorksheetFunction
Can you see what I am doing wrong here.
Sub CntIf() Dim myrng As Range Sheets("Node Data").Activate Cells(1, counter + 2).Value = "Total Leaks per " & Ldb Set myrng = Sheets(Ldb).Range("B2:B" & rwct) Range(Cells(2, counter + 2), Cells(85, counter + 2)) = Application.WorksheetFunction.CountIf(myrng, "$A$2:$A$85") End Sub This does put values in the range (Cells(2, counter + 2), Cells(85, counter + 2)) , but they are all 0's, not the correct count. TIA Mike F |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using variable in WorksheetFunction
If you used =countif() in the cell, your formula would be:
=countif(sheetx!b2:b###,"$a$2:$a85") This doesn't look like it's what you really want. Mike Fogleman wrote: Can you see what I am doing wrong here. Sub CntIf() Dim myrng As Range Sheets("Node Data").Activate Cells(1, counter + 2).Value = "Total Leaks per " & Ldb Set myrng = Sheets(Ldb).Range("B2:B" & rwct) Range(Cells(2, counter + 2), Cells(85, counter + 2)) = Application.WorksheetFunction.CountIf(myrng, "$A$2:$A$85") End Sub This does put values in the range (Cells(2, counter + 2), Cells(85, counter + 2)) , but they are all 0's, not the correct count. TIA Mike F -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using variable in WorksheetFunction
This formula pasted in the activesheet from B2:B85 works. There is a list of
unique items in this sheet from A2:A85 that I want counted from the sheet LAFQ403 B2:B568. =COUNTIF(LAFQ403!B2:B568,$A$2:$A$85) The variable Ldb="LAFQ403" rwct=568, so myrng = Sheets(Ldb).Range("B2:B" & rwct) counter=0 so Range(Cells(2, counter + 2), Cells(85, counter + 2)) is Range("B2:B85") of the activesheet. How, in VB, do I get the results of the COUNTIF into the range B2:B85 of the activesheet? I have never figured out how to put variables into a cell formula = "=CountIf(myrng), so I am trying the Application.WorksheetFunction method, but still coming up zip. As it turns out "0" is the correct count for the first item in A2, so it is giving me one answer throughout the entire range. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using variable in WorksheetFunction
I _think_ that this is what you're shooting for:
Option Explicit Sub CntIf() Dim myrng As Range Dim ldb As String Dim rwct As Long Dim counter As Long ldb = "lafq403" rwct = 568 counter = 0 Set myrng = Sheets(ldb).Range("B2:B" & rwct) With Sheets("Node Data") .Cells(1, counter + 2).Value = "Total Leaks per " & ldb .Range(.Cells(2, counter + 2), .Cells(85, counter + 2)).Formula _ = "=CountIf(" & myrng.Address(external:=True) & ",A2)" End With End Sub The stuff that's related to a variable in VBA doesn't have "" surrounding it. Mike Fogleman wrote: This formula pasted in the activesheet from B2:B85 works. There is a list of unique items in this sheet from A2:A85 that I want counted from the sheet LAFQ403 B2:B568. =COUNTIF(LAFQ403!B2:B568,$A$2:$A$85) The variable Ldb="LAFQ403" rwct=568, so myrng = Sheets(Ldb).Range("B2:B" & rwct) counter=0 so Range(Cells(2, counter + 2), Cells(85, counter + 2)) is Range("B2:B85") of the activesheet. How, in VB, do I get the results of the COUNTIF into the range B2:B85 of the activesheet? I have never figured out how to put variables into a cell formula = "=CountIf(myrng), so I am trying the Application.WorksheetFunction method, but still coming up zip. As it turns out "0" is the correct count for the first item in A2, so it is giving me one answer throughout the entire range. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
WorksheetFunction with VBA | Excel Discussion (Misc queries) | |||
Max WorksheetFunction | Excel Programming | |||
WorkSheetFunction.CountIf & WorkSheetFunction.SumIf with 2 conditions? | Excel Programming | |||
WorksheetFunction | Excel Programming | |||
WorksheetFunction | Excel Programming |