Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
ebook!
Our Top 15 Excel Tutorials
Instant Access!
E-mail:
Subscribe for Free Excel tips & more!
E-mail:


Advertisements


Free Excel Forum

Evaluate Method Fails Using Variables

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

The code below is what I'm having trouble with. Count runs fine and returns what I would expect. Count2 however does not work. If I run it in break mode and hover over the variables in the count if statement they appear to be correct however it will not return a value. I need to us the Count2 as I need to pass variables into the COUNTIFS statement. Any help would be great.

VB:

Sub Count() 
    Dim Agent As String 
    Agent = "ROSSY" 
    Dim Count As Integer 
    Dim Count2 As Integer 
    Dim Week As Integer 
    Week = 25 
    Count = Evaluate("=COUNTIFS(PEX!$B:$B,""ROSSY"",PEX!$M:$M,""25"")") 
    Count2 = Evaluate("=COUNTIFS(PEX!$B:$B," & Agent & ",PEX!$M:$M," & Week & ")") 
End Sub 


If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines



View Answers     

Similar Excel Video Tutorials

Helpful Excel Macros

Count The Number of Words in a Cell or Range of Cells in Excel - UDF
- Count words in cells with this user defined function (UDF). This UDF allows you to count the number of words that are w
Vlookup Macro to Return All Matching Results and Stack them with Previous Results
- This is very similar to the other Vlookup type Macro in that it returns all of the results that match a particular se
Return the ISO Week Number from a Date in Excel - UDF
- Return the ISO Week Number in Excel with this UDF. This is a simple to use UDF (user defined function) that returns the
Vlookup Macro to Return All Matching Results from a Sheet in Excel
- This Excel Macro works like a better Vlookup function because it returns ALL of the matching results. Run the
Excel Macro that Searches Entire Workbook and Returns All Matches
- This is the ultimate Lookup Macro for Excel. It will search every worksheet in the workbook and return all of the mat

Similar Topics







Hello,
I have this script that I'm trying to modify. There are two modifications I want to make, but can't seem to figure it out. Script is below

First:
On the two lines that contain the cell ranges
Code:

Count = Int(Evaluate("COUNTIF(A1:A50,""=""&BB1)") / 2)
For Each cell In Range("A1:A50")


I want to be able to put in a variable instead of a cell number. This variable will be set throughout the other parts of the script.
For example, "A1:A50" would be along the lines of "A & variablename1:A & variablename2"

Second:
This script is going to be executed from another worksheet.
I tried adding With Sheets("sheet2") arguement to the start of the script and adding the End WIth at the end, but it would run on sheet1, where it's being executed from. I want it to run it's script on sheet2.

thanks for taking a look!

Code:

 Count = Int(Evaluate("COUNTIF(A1:A50,""=""&BB1)") / 2) 
 If Count Mod 2 = 1 Then Count = Count + 1 
 Count2 = 1 
 For Each cell In Range("A1:A50") 
 If IsEmpty(cell) Then 
 cell = Count2 
 If Not Switch Then 
 Count2 = Count2 + 1 
 If Count2 = Count Then Switch = True 
 Else 
 Count2 = Count2 - 1 
 End If 
 End If 
 Next cell 





First of all, thanks to everyone on this forum as it has been a great source of help for me.

On to my question. Basically I have a worksheet that retrieves external data from a .csv file exported from an onsite reporting tool. This data consists of simple list of names, it represents a 'count' of everytime an agent performs a specific action. I convert this list of names into an associated log of how many times each agent performed this function. So every agent name has a number alongside generated using the =COUNTIF(B:B,B2) function. This report is run daily.

Column A is purely numbers generated by the =COUNTIF function
Column B is pure text imported from external data made up of agent names.

The next worksheet contains a list of all agent names, and using the following, I refer to the sheet that contains the external data import and retrieves each agents 'count'.

=INDEX('external data'!A:A,MATCH(agent list!A4,'external data'!B:B,0))

So for each agent listed, it matches the agent name in the external data sheet and returns the 'count' for that agent according to the data for that day.

I would like the worksheet containing a list of agents to show their 'count' for each day the report is run. So the main worksheet looks like

Agent name, Date1, Date2, Date3, Date4, etc.
name1, count1, count2, count3
name2, count1, count2, count3
etc.

So as per the post title, I am looking for the date that occurs in the external data sheet to be validated before populating the agent counts.

So,

day1 the report is run, date1 = today and so the first column (date1) is populated with each agents count.

day2 the report is run, date2 = today and so the second column is populated.

and so on without overwriting the previous days 'count' and eventually creating a weekly and monthly stat for each agents 'count'. Perhaps this is only achievable with the use of a VB macro and not in the cell formula. Either way, if anyone could point me in the right direction it would be much appreciated.

I hope that is clear and someone can assist. Thanks in advance.


I'm having a proble with the following code with I change one of the variables:
VB:

 
Private Sub count() 
    Dim Wk As String 
    Wk = 16 
     
    Dim ANDERKD As Integer 
    Sheets("TPSS").Select 
    ANDERKD = CountTA(Wk, "BOBAYNP") 
End Sub 
--------------------------------------------------------- 
Function CountTA(ByVal Wk As String, TA As String) 
    CountTA = Evaluate("=SUMPRODUCT((TPSS!M3:M1500=" & Wk & ")*(TPSS!B3:B1500=" & TA & "))") 
End Function 


If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines



When I change the BOBAY to BERRYCA the code does not return anything. There are results on the page that is being looked at that meet the criteria. I have checked over it a million times and every instance is spelled correctly. The function will work if I enter it on the page as the sumproduct formula. This formula works for some of the variables and not others which is driving me crazy. Any suggestions I can not see what I'm doing wrong.

Also is there something besides the sumproduct formula that I have in the code that I shoudl be using? I need it to look at a specific sheet and count the number of rows that meet multiple criteria.

Thanks
Dan



Hello people:

I have been using this formula to count agents based on time frames which includes OR function: Example:

=IF(COUNTIFS(AR:AR,"<="&TIME(8,0,0),AO:AO,$AO$2:$AO$10) + COUNTIFS(AQ:AQ,"<="&TIME(8,0,0),AR:AR,"online",AO:AO,$AO$2:AO$10),"1","0")

(AO Column) includes the agents that have Logged Out at specific Times (Column AR) or if Agents are "Online" (not logged out) (Column AR) as well .

AO2 is where first agent is listed , although it is not necessary to have each agent's name on time frame count. The most important result is to have a general count of agents for each time frame.

It works for each row, it gives "1" value if countifs operations results positive for at least 1 of the variables, either logged in or logged at specific time frame.

The only issue I have not been able to figure out is how to tell Excel not to duplicate agents names for each time frame, in example formula all agents are listed on column AO:AO, and for example if Agent X is listed 10 times on such time frame, then excel counts Agent "Paul" 10 times on rows with a 1, that would mean agent was working on such time frame.

That is fine but I would like to know if Excel can interpret that there is only 1 Agent named Paul for each time frame.

Is there a way to avoid multiple counting for each agent?

Thanks very much!!

Hi,

I am having trouble getting the correct syntax for this VBA statement used to Countif based on named ranges and variables. Does anyone know what the correct syntax should be for this statement?

VB:

CountPay = Evaluate("=SUMPRODUCT((PayA=" & CLng(DateValue(TestForm.PayWeek.Value)) & ")*(PayB=" & TestForm.EmpCode.Value & "))") 


If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines



Countpay is dimmed as Long
PayA and PayB are 2 dynamic named ranges
TestForm.PayWeek.Value and TestForm.EmpCode.Value are 2 user form fields.

I know these named ranges and variables are correct and contain data as the MATCH statement below works fine:

VB:

Evaluate("MATCH(""" & CLng(DateValue(TestForm.PayWeek.Value)) & TestForm.EmpCode.Value & """,PayA&PayB,0)") 


If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines



Thanks.
It's driving me crazy and googling has not revealed an answer.
janie

Hey, hope someone can give me some useful advice...basically what i am trying to do is look at each cell in a column (or range), then increase the appropriate counter depending on which range the value falls within.here is my code thus far...

any help appreciated!!

tan :-)

Sub test()

Dim count1 As Integer
Dim count2 As Integer
Dim count3 As Integer
Dim i As Integer
Dim x As Integer

x = 0
count1 = 0
count2 = 0
count3 = 0

For Counter = 1 To 6 ' this is how many samples in 8 hrs
'Set curCell = Worksheets("rad_10secs_5days").Cells(x, 9).Value
'Worksheets("rad_10secs_5days")
If Cells(x, 9).Value < 86 Then 'if less than 86%
count1 = count1 + 1

ElseIf Cells(x, 9).Value >= 86 And Cells(x, 9).Value < 95 Then
count2 = count2 + 1 'if between 86-94% inclusive

Else ' if greater than 94%
count3 = count3 + 1

End If

x = x + 1

Next Counter

MsgBox "Saturation < 86% is " & count1 & vbCrLf & "Saturation 86-94 is " & count2 & vbCrLf & "Saturation > 94% is " & count3

'Worksheets("Sheet2").Range ("A1:B5")-how to define range in worksheet

End Sub


Hello All,

Seems the first time around I did get some help, but I am still stuck. I am using Hummingbird Visual Basic 8.0 for programming, and I am trying to create a pivot table in Excel.

I have tried doing the Excel Macro (recording my steps), but that gives me a ton of junk that I am not sure what to do with.

Below is the type of code that I am using, it is very rudamentary, but it gets the job done for a basic lookup and compare routine.

order_pnumber = mid(xls.sheets("order").range("A" & count2).value,1,20)
order_pquantity = mid(xls.sheets("order").range("F" & count2).value,1,8)
us10_pnumber = mid(xls.sheets("US10").range("B" & count).value,1,20)
us10_pquantity = mid(xls.sheets("US10").range("E" & count).value,1,8)
us10_pdelv = mid(xls.sheets("us10").range("F" & count).value,1,3)
if order_pnumber = us10_pnumber then
xls.sheets("us10").range("E" & count).value = (order_pquantity + us10_pquantity)
xls.sheets("us10").range("F" & count).value = (us10_pdelv +1)
count2 = count2+1
count = 2
elseif order_pnumber <> us10_pnumber then
count = count+1
end if
if us10_pnumber = "" then
count2 = count2+1
count = 2
goto end_proc
end if
if order_pnumber = "" then
call close_file(xls)
end if

end_proc:
loop
end sub

Now for my Pivot I have:

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Sheet1!R1C1:R6C6").CreatePivotTable.TableDestination.range("H2"). _
TableName:="PivotTable1"
ActiveSheet.PivotTables("PivotTable1").SmallGrid = False
ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="Code"
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Value")
.Orientation = xlDataField
.Caption = "Average of Value"
.Function = xlAverage
End With

But I get a syntax error and error in statement. If anyone can help with the basics of the pivot table code I would really appreciate it. Or if there is a website that is good about stepping someone through the process that would work as well. Thank you.

Regards,

Portree


Date validation before INDEX AND MATCH?
-posted this a few days back in the miscellaneous forum to no avail,

--------------------------------------------------------------------------------

I have a worksheet that retrieves external data from a .csv file exported from an onsite reporting tool. This data consists of simple list of names, it represents a 'count' of everytime an agent performs a specific action. I convert this list of names into an associated log of how many times each agent performed this function. So every agent name has a number alongside generated using the =COUNTIF(B:B,B2) function. This report is run daily.

Column A is purely numbers generated by the =COUNTIF function
Column B is pure text imported from external data made up of agent names.

The next worksheet contains a list of all agent names, and using the following, I refer to the sheet that contains the external data import and retrieves each agents 'count'.

=INDEX('external data'!A:A,MATCH(agent list!A4,'external data'!B:B,0))

So for each agent listed, it matches the agent name in the external data sheet and returns the 'count' for that agent according to the data for that day.

I would like the worksheet containing a list of agents to show their 'count' for each day the report is run. So the main worksheet looks like

Agent name, Date1, Date2, Date3, Date4, etc.
name1, count1, count2, count3
name2, count1, count2, count3
etc.

So as per the post title, I am looking for the date that occurs in the external data sheet to be validated before populating the agent counts.

So,

day1 the report is run, date1 = today and so the first column (date1) is populated with each agents count.

day2 the report is run, date2 = today and so the second column is populated.

and so on without overwriting the previous days 'count' and eventually creating a weekly and monthly stat for each agents 'count'. Perhaps this is only achievable with the use of a VB macro and not in the cell formula. Either way, if anyone could point me in the right direction it would be much appreciated.

I hope that is clear and someone can assist. Thanks in advance.


Hi All,

I need to make the below code faster, any ideas?

VB:

Dim myWorksheet As Worksheet 
Dim myWorksheetName As String 
Dim count As Long 
Dim temp As String 
Dim count2 As Long 
 
If ComboBox1.value = "" Or ComboBox2.value = "" Then 
    MsgBox ("Please select Month or Branch") 
    Exit Sub 
End If 
 
temp = ComboBox2.value 
If temp = "All" Then 
    temp = "*" 
End If 
myWorksheetName = ComboBox1.value 
 
 'check if exsists
 'call SheetExists
If SheetExists(ComboBox1.value) = True Then 
     
    count = Sheets(myWorksheetName).Range("A1").CurrentRegion.Rows.count 
     
    Worksheets(myWorksheetName).Range("A1:P" & count).AutoFilter _ 
    field:=4, Criteria1:=temp 
     
     
     
    Worksheets("Sheet1").Range("A1:P2").Copy _ 
    Destination:=Worksheets("Sheet3").Range("A1") 
     
    Worksheets(myWorksheetName).Range("A3:M" & count).Copy _ 
    Destination:=Worksheets("Sheet3").Range("A3") 
     
    count2 = Sheets("Sheet3").Range("A1").CurrentRegion.Rows.count 
    count23 = count2 + 1 
    Sheets("Sheet3").Range("K" & count23).value = "Total" 
    Sheets("Sheet3").Range("L" & count23).value = "=SUM(L3:L" & count2 & ")" 
    Sheets("Sheet3").Range("M" & count23).value = "=SUM(M3:M" & count2 & ")" 
     
    With Sheets("Sheet3").PageSetup 
        .PrintTitleRows = "" 
        .PrintTitleColumns = "" 
        .PrintArea = "" 
        .LeftHeader = "" 
        .CenterHeader = "" 
        .RightHeader = "" 
        .LeftFooter = "" 
        .CenterFooter = "" 
        .RightFooter = "" 
        .LeftMargin = Application.InchesToPoints(0) 
        .RightMargin = Application.InchesToPoints(0) 
        .TopMargin = Application.InchesToPoints(0.92) 
        .BottomMargin = Application.InchesToPoints(0) 
        .HeaderMargin = Application.InchesToPoints(0) 
        .FooterMargin = Application.InchesToPoints(0) 
        .PrintHeadings = False 
        .PrintGridlines = False 
        .PrintComments = xlPrintNoComments 
        .CenterHorizontally = False 
        .CenterVertically = False 
        .Orientation = xlLandscape 
        .Draft = False 
        .PaperSize = xlPaperLetter 
        .FirstPageNumber = xlAutomatic 
        .Order = xlDownThenOver 
        .BlackAndWhite = True 
        .Zoom = False 
        .FitToPagesWide = 1 
        .FitToPagesTall = 1 
    End With 
     
     
    Me.Hide 
    Application.WindowState = xlMaximized 
    Sheets("Sheet3").Range("A1:M" & count23).PrintPreview 
    Application.WindowState = xlMinimized 
    Me.Show 
    Worksheets(myWorksheetName).AutoFilterMode = False 
    Sheets("Sheet3").Range("A1:P" & count23).value = "" 
    Sheets("Sheet3").Range("A1:P2").UnMerge 
    ThisWorkbook.Save 
Else 
    MsgBox ("No Data For That Month Please Change") 
    Exit Sub 
End If 


If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines




Hello again, Wizards of OZ.

Back with what I believe to be a major brain fart, hope you can help.

I have a 2248 row spreadsheet with more than a few columns, which would take me a long time to scrub to make it safe for posting(we're talking days, not hours); in this spreadsheet is a fairly simple countifs that refuses to operate properly.
VB:

=COUNTIFS(UIC,G2,Table13[Year],"<AB1") 


If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines



Following the formula, this should look at the UIC column and count all the matches to the data in cell G2, then look at the results and match all the ones in the Year column that are less than the value in cell AB1. The problem is, it won't do a "<" in the nested argument. I have tried "<AB1" "<(AB1)" and all sorts of variations along those lines, it will not allow me to insert them(formula errors), or it tries to count them as literally <AB1 (anything that has a value less than the letters ab1 is counted, making all dates count).

I initially wanted to make it self-contained with a VB:

=COUNTIFS(UIC,G2,Table13[Year],"<=TODAY(YEAR)-1") 


If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines



type of thing...but I'm sure you are all aware that that did not work.

The essence of it is that it needs to compare the values in the "year" column against the current year-1.

Help me, Oz-e won kenobi, you're my only hope!

Edit : silly thought... something along these lines, maybe? VB:

=COUNTIFS(UIC,G3,Year,"=(TODAY())(=TEXT"YYYY")-1") 


If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines



I'm going to keep poking around until something sticks!

I'm trying to count text in columns C & D and change the font color if it is 40 characters long or longer. I was playing around with the code below but it's not working.

I was just using A column to try to get it to work. It would be nice if I could highlight the cell instead of change the font but I can work on that later.

Code I've tried (doesn't work):

Code:

Sub leeee()

Dim Count2 As Integer
Dim MyCell As String

For a = 1 To Count2
     MyCell = Cells(a, 2)
     Cells(a, 2) = ActiveCell
    If Len(ActiveCell) > 39 Then Selection.Font.ColorIndex = 5
   Next a

End Sub


Any thoughts on what's wrong???


Hi, I was just needed sopme help with my syntax for defining time ranges.

I am doing something like this

Sub test_time()


For Counter = 1 To 4

If Cells(Counter, 9).Value >= 08:00:00 And Cells(Counter, 9).Value < 20:00:00 Then

count1 = count1 + 1

Else
count2 = count2 + 1

End If

Next Counter

MsgBox "Count between 08:00:00 and 20:00:00 is " & count1 & vbCrLf & vbCrLf _
& "Count between 20:00:00 and 08:00:00 is " & count2

End Sub

Any help would be appreciated!!!

Thanks

tania


Essentially what is happening is that we have two data sets. Data Set 1(ArrayL) has about 5k records, data set 2(ArrayC) has approximately 22k records. For every record in data set 2 there is a corresponding record in data set 1. The macro loops through data set 2 with a nested loop finding the corresponding value in data set 1, and then copying that value to data set 2.

I'm sure there is a faster way to do this whole thing so if you have any suggestions let me know.

Code:

Private Sub domdivisor()
Dim i As Integer
Dim c As Integer
Dim ArrayC()
Dim ArrayL()
Dim R As Range
Dim count As Integer
Dim count2 As Integer


Application.ScreenUpdating = False

ThisWorkbook.Sheets("dom divisor").Activate
count = ActiveSheet.UsedRange.rows.count - 2
ReDim ArrayC(count, 1)

c = 3
i = 0

'load array values
Do While i < count
ArrayC(i, 0) = Range("A" & c).Value
c = c + 1
i = i + 1
Loop

'remove #N/A Values
ThisWorkbook.Sheets("xref dom").Activate

c = 3
For Each R In Range("f3", Range("f3").End(xlDown))
If WorksheetFunction.IsNA(Range("f" & c)) Then
Range("f" & c) = 0
End If
c = c + 1
Next R

'load cross Ref Values
count2 = ActiveSheet.UsedRange.rows.count - 2
ReDim ArrayL(count2, 1)
i = 0
c = 3

For Each R In Range("A3", Range("a3").End(xlDown))
ArrayL(i, 0) = Range("a" & c).Value
ArrayL(i, 1) = Range("f" & c).Value
i = i + 1
c = c + 1
Next R

'find values for Dom Divisor
i = 0
c = 0
Do While i < count
c = 0

    Do While ArrayC(i, 0) <> ArrayL(c, 0)
    c = c + 1
    Loop
    
ArrayC(i, 1) = ArrayL(c, 1)
i = i + 1
Loop

'Load values into Dom Divisor

ThisWorkbook.Sheets("dom divisor").Activate
i = 0
c = 3

Do While i < count
Range("e" & c) = ArrayC(i, 1)
c = c + 1
i = i + 1
Loop

'insert formula for divisor creation


Application.ScreenUpdating = True
End Sub





I need some help figuring out why the following code will not work. I'm using it to look at a large list and give me a count of rows meeting specific criteria.

This is the small scetion that I wrote for testing and it goes into a much larger peice of code. the Wk variable would normally come for a user form where it is entered prior to this code running. The code will work if I put a 21 in place of the Wk in the sumproduct formula but it gives me the Type missmatch error

Code:
VB:

Sub testsum() 
    Dim Wk As Integer 
    Dim dcANDERKD As Integer 
    Wk = 21 
    dcANDERKD = Evaluate("=SUMPRODUCT((A3:A1500=Wk)*(A3:A1500=""ANDERKD"")*(B3:B1500=""BOBAYNP""))") 
End Sub 


If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines



I have tried putting the Wk in () also and did not work. Have also tried to take the variable type which did not help. I'm sure that I'm missing something simple.

Thank you,

Dan

count1, count2, count3 - integer

Can you please correct error he

Range("B" & count1).Formula = "=max((A & count2) : (A & count3))"

Thanks!
Miche1e


Hi all,

I have the following VBA code that works:

VB:

 
EditPromo.ProdNo2.Value = Evaluate("INDEX(Promotions8,MATCH(""2011-001""&""2"",Promotions1&Promotions7,0),1)") 


If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines



Promotions1, 7 & 8 are all named ranges.

As soon as I try to add a variable as the match criteria like this:

VB:

 
EditPromo.ProdNo2.Value = Evaluate("INDEX(Promotions8,MATCH(PromoNumber&""2"",Promotions1&Promotions7,0),1)") 


If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines



I get this error:
"Run-time error '-2147352571 (80020005)': Could not set the Value property. Type mismatch"

PromoNumber is a String & I have confirmed it contains the value "2011-001" just as in the first formula.
EditPromo.ProdNo2.Value is a textbox on a form.

Does anyone know how to make this statement work with the variable?

Thanks,
janie

Hi Guys

Hopefully very quick - I cant see the wood for the trees! I have this formulae which works just how I want

VB:

ACO2 = Evaluate("=SumProduct((K2:K3=""Closed"") * (EG2:EG3=""06-15 Days""))") 


If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines



However when I change this to a dynamic range I keep getting a syntax error. Can anyone see where I have gone wrong? I know its probably only a " or an ) out of place but its driving me mad!!!

VB:

ACO2 = Evaluate("=SumProduct(((Range("K2:K" & Lrow)=""Closed"") * ((Range("EG2:EG" & Lrow)=""60 Days+""))") 


If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines



AC02 is an integer, as is Lrow (its a simple loop which works out how many rows there are). Any help is really appreciated!

Mike

I have a file that works perfectly fine on my computer but doesn't on my friends.

The start of the code is as follows:

Sub multiinsert()
Sheets("Sheet1").Select

For Count = 1 To 100
ActiveSheet.Range("A2").Select
For count2 = 1 To 100

When my friend runs it it says compile error and with a grey background highlights the word "count", and then "Sub Multiinsert()" is highlighted in yellow.

I have a feeling this has something to do with declaring variables. However, it works on my computer without declaring them, and there are many many variables in the code do put declarations at the top or all of them.

If anyone can help it would be greatly appreciated.
Many thanks
Jonathan


Hi. I've read many many forums but did not find what I'm looking for.
I have an excel file. When you open it, it shows a form, and in the back is a blank sheet ("blank") because I don't want the users to see the data (which is in a Worksheet named "db").

In the form I need to count cells in worksheet "db" meeting two criteria, so I'm using Evaluate/sumproduct:
VB:

sAAR = Evaluate("=sumproduct(--(" & a1 & "=" & Var1 & "),--(" & a2 & "=" & Var2 & "))") 


If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines



where a1 and a2 are ranges and Var1 and Var2 variables.

This is working properly if behind the form is whorksheet "db", but fails when in the background is the desired "blank" worksheet.
Is there a way to set focus to "db", but actually displaying "blank" worksheet?

Many thanks in advance.
Carlos.

Hi Everyone,
I had lots of help over years, so thanks to all contributers, I am trying to feed the forum back in these days;
Below small piece of code does not work, any idea?
thanks

VB:

MsgBox Evaluate("=CountA(SourceWB.Worksheets(1).range(""B:B""))") 


If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines



neither this;
VB:

MsgBox Evaluate("=CountA(SourceWB.Worksheets(1).range("B:B"))") 


If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines




Hi All,

I'm trying to write an IRR function that will allow me to change the number of iterations that the code runs through. My inputs are the cash flow (as range), an estimate of IRR (as double), and a number of desired iterations (as integer). As written, it will allow the net present value to be within 0.5 to return a result.

I have looked at other custom IRR functions, but I do not want to bother with the need to enter dates, etc. I understand that there may be better ways to do this, but I would appreciate it if you would look at my attempt first, as this is partially a learning exercise for me.

Thank you for your help!

VB:

 
Function MyIrr(CF As Range, guess As Double, iter As Integer) 
    Dim rows As Integer 
    rows = CF.rows.count 
    Dim count As Integer 
    count = 2 
    Dim iterations As Integer 
    iterations = 0 
    Dim NPV As Double 
    NPV = CF(1, 1).Value 
doagain: 
    If iterations > iter Then End 
    Do While count <= rows 
        NPV = NPV + (CF(count, 1).Value / ((1 + guess) ^ (count - 1))) 
        count = count + 1 
    Loop 
    If Abs(WorksheetFunction.Round(NPV, 2)) < 0.5 Then 
        MyIrr = guess 
    Else 
        If NPV > 0 Then 
            guess = guess + 0.01 
            iterations = iterations + 1 
            Goto doagain 
        Else 
            guess = guess - 0.01 
            iterations = iterations + 1 
            Goto doagain 
        End If 
    End If 
     
End Function 


If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines




I would like to get the return value for an evaluate statement which is a variant data type, convert it to an integer and do some computation. I have 4 test programs, the first 3 all work, the 4ths does not.

The first sd_test(returns 1) and st_test1(returns 1) demonstrate that my evaluate statement is correct and works,

sd_test2(returns 3) shows that I can convert a variant to an integer

in sd_test3(returns #VALUE!) I try to put both together and it doesn't work?
any help? thanks paul



Code:

Function sd_test()
Dim X As Variant
X = Evaluate("=row(myTable[#Headers])")
sd_test = X
End Function

Function sd_test1() As Variant
Dim X As Variant
X = Evaluate("=row(myTable[#Headers])")
sd_test1 = X
End Function


Function sd_test2() As Integer
Dim X As Variant
Dim Y As Integer
X = 3
Y = CInt(X)
sd_test2 = Y
End Function


Function sd_test3() As Integer
Dim X As Variant
Dim Y As Integer
X = Evaluate("=row(myTable[#Headers])")
Y = CInt(X)
sd_test3 = Y
End Function





I have a Pivot of monthly data. For a subset of my team, I need to use weekly data. I have the Week data as a row label under Month:

Sum of Sales
Agent's Name
... 1 (Month)
....... 2 (Week)
....... 3 (Week)
....... 4 (Week)
....... 5 (Week)
....... 6 (Week)
... 2 (Month)
....... 6 (Week)
....... 7 (Week)
....... 8 (Week)
....... 9 (Week)
....... 10 (Week)

The problem comes in at Week 6. Because it is split between Month 1 and 2, I cannot get it to return the total value.

I of course cannot simply order the data by Week number, because I'd encounter the same problem with my Monthly data.

I'd like to avoid having to create a second pivot, due to the size.

Below is the stripped down formula. Can anyone make it work? Or help me in a different direction to the same result? Thanks!

Code:

 
=GETPIVOTDATA("Sum of Sales Count",$A$5,"Agent",BM5,"Week Num","6")





I am programming a database organizer that utilizes excel to print out some data. As part of this I am using a function that accepts two variables in order to select a range in a workbook and manipulate the data. I am having trouble figuring out how to access a range using two variables.

my code is something like this

VB:

rowcount As Integer 
rowcount2 As Integer 
 
excelbook.Range("A" &rowcount ":G" &rowcount2).Select() 


If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines



I keep getting an error that it is expecting a ,) or valid expression

Any help would be greatly appreciated.

Thank You

I am running a report that will collect information on monitor scores similiar to the example below. What I would like to do is use code to insert two rows between each "new" agent. There can be anyhere between 20 to 60 agents that are in this report so this would save me a lot of time. After running the code, all of agent 1's information should be in a group, then two empty rows and then all of agent 2's information in a group and so on.

Any assistance with pointing me in the right direction would be great.

Thank you,

Eric

A B C D E 1 Agent 1 45 534 54 345 2 Agent 1 5 5 5 435 3 Agent 1 45 34 54 345 4 Agent 1 4 5 545 345 5 Agent 1 45 54 54 45 6 Agent 1 5 3 5 444 7 Agent 1 5 3 5 444 8 Agent 1 45 534 54 345 9 Agent 1 5 5 5 435 10 Agent 1 45 34 54 345 11 Agent 1 4 5 545 345 12 Agent 1 45 54 54 45 13 Agent 2 5 3 5 444 14 Agent 2 5 3 5 444 15 Agent 2 45 534 54 345 16 Agent 2 5 5 5 435 17 Agent 3 45 34 54 345 18 Agent 3 4 5 545 345 19 Agent 3 45 54 54 45 20 Agent 3 5 3 5 444 21 Agent 3 5 3 5 444 22 Agent 4 45 534 54 345 23 Agent 4 5 5 5 435 24 Agent 4 45 34 54 345 25 Agent 4 4 5 545 345 26 Agent 4 45 54 54 45 27 Agent 4 5 3 5 444