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



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 Tutorials

Variables in Macros VBA for Excel - Practical Guide
This is a practical guide to using Variables in Macros and VBA for Excel. I will tell you what they are and how to ...
Excel VBA - Create an Array - 3 ways
Ill show you three different ways to create an array in Excel VBA and Macros and how to get the data out of those a ...
How to Find and Fix Errors in Complex Formulas in Excel
Here, I'll show you a quick, simple, and effective way to fix formulas and functions in Excel. It can be hard to f ...
Performing Calculations in Excel - Order of Operations Explained
In this tutorial I am going to cover how to perform calculations in Excel. Calculations in Excel follow the same ba ...

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







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

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.


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???


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

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

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 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 want to take a string representing a formula and evaluate it.

When the string-formula refers to the content of Ranges, e.g. named ranges and cell references
I can do this using the Application.Evaluate method.
This method doesn't work when the string-formula refers to variables defined in code.

Is there an equivalent method or technique that I can use?
(For reasons of speed and efficiency I don't want to create Range references)

My code example demonstrates what I'm trying (and failing) to achieve. As it stands the
code returns error 2029.

Grateful for any help.

VB:

Sub myTest() 
    Dim strFormula As String 
    Dim myVar1 As Double 
    Dim myVar2 As Double 
    myVar1 = 2 
    myVar2 = 4 
    strFormula = "myVar1 + myVar2" ' doh, doesn't work
     '        strFormula = "myName1 + myName2"   ' works, uses values in the named ranges
     '        strFormula = "b4 + b5"             ' works, uses values in cells b4 and b5
     '        strFormula = "1 + sin(45)"         ' works too
    myOp = Application.Evaluate(strFormula) 
    Debug.Print myOp 
End Sub 


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




Dear experts,

A portion of my code checks if the value of the cell equals to zero (inside a For Loop). Within the macro the test gives me zero variable equal to the amount of blank AND zero-containing cells, while I would expect it to count only zero-containing cells.
In the immediate window everything works fine: ?Cells(x,y).value returns 1,0 or nothing exactly as it is in the cells.

VB:

If Cells(range.Row + lrowno - 1, inumberscolno).Value = 0 Then 
    zero = zero + 1 
End If 


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



This is the entire code (if required). This is an updated version of AVERAGEOF macro by SMC (thank you) that has the functionality of AVERAGEIF ignoring zeros (but conditions and numbers are in the same range), if the range is empty - returns "" as a result, if cells of the range contain zeros and blanks or only zeros - returns "0" (ZERO).
VB:

Public Function AVERAGEIFNB(ByVal range As range, _ 
    ByVal sCriteria As String) As Variant 
     'Calculates AVERAGEIFNB, for empty range returns "", if
    Dim inumberscolno As Integer 
    Dim blank_zero As Integer 
    Dim zero As Integer 
    Dim lrowno As Long 
    Dim lmatch As Long 
    Dim sngaverage As Single 
    Dim vcellvalue As Variant 
     
    zero = 0 
    Call Application.Volatile(True) 
    If IsNumeric(sCriteria) Then sCriteria = "=" & sCriteria 
    If sCriteria = vbNullString Then sCriteria = "=""""" 
    inumberscolno = range.Column 
     
    For lrowno = 1 To range.Rows.Count 
         
        If Cells(range.Row + lrowno - 1, inumberscolno).Value = 0 Then 
            zero = zero + 1 
        End If 
         
        If IsEmpty(range.Parent.Cells(range.Row + lrowno - 1, inumberscolno)) Then 
            blank_zero = blank_zero + 1 
        End If 
         
        If Not IsEmpty(range.Parent.Cells(range.Row + lrowno - 1, inumberscolno)) Then 
            vcellvalue = range.Parent.Cells(range.Row + lrowno - 1, inumberscolno).Value 
            If IsNumeric(vcellvalue) Then 
                If Evaluate("=" & IIf(range.Parent.Cells(range.Row + lrowno - 1, inumberscolno).Text = "", """""", range.Parent.Cells(range.Row + lrowno - 1, inumberscolno).Text) & sCriteria) Then 
                    lmatch = lmatch + 1 
                    sngaverage = sngaverage + range.Parent.Cells(range.Row + lrowno - 1, inumberscolno).Value 
                End If 
            End If 
        End If 
        If sngaverage <> 0 And IsEmpty(vcellvalue) = True Then Exit For 
    Next lrowno 
     
    If zero > 0 And blank_zero < range.Rows.Count Then 
        AVERAGEIFNB = 0 
        Exit Function 
    End If 
     
    If Len(range.FormulaArray) = 0 Or blank_zero = range.Rows.Count Then 
         
        AVERAGEIFNB = "" 
    Else 
        AVERAGEIFNB = sngaverage / lmatch 
    End If 
     
End Function 


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




Hello

I'm trying to write a UDF that will get round a problem with SUMPRODUCT. Basically, I have a large DataTable with about 180 columns of data in it. I need to be able to work out the number of entries in the table that match certain criteria (stored in a range called Criteria). In principle, the number of criteria could equal the number of columns, and I'm working on that basis in what follows.

In other words, I could use COUNTIFS, but I can't guarantee that all users will have Excel 2007 or newer, and I've read that the max number of criteria for COUNTIFS is 157 (<180).

So I'm therefore having to go for a SUMPRODUCT formula along the lines of

VB:

=SUMPRODUCT(--(D4:D11=G4),--(E4:E11=H4),--(F4:F11=I4)) 


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



Where in this test example, the DataTable range is D4:F11 and the Criteria range is G4:I4.

The problem is that I can't use wildcards in the above. So if I want to set I4 = "*" to mean all of column F4:F11, then I can do this using COUNTIFS, but not SUMPRODUCT.

I'm therefore trying to write a UDF to produce the SUMPRODUCT formula automatically, so that if an entry in the criteria column is "*" or "", then it skips that column in the DataSheet/Criteria ranges and moves on to the next. I've come up with the code below and it comes up with a big juicy #VALUE! error when I try.

The UDF has been declared in the VBA module (not the sheet), so that's not the problem. Any suggestions for what I'm doing wrong?

Many thanks in advance,
Darren

VB:

Function DSRFUNCTION(DataTable As Range, Criteria As Range) 
     
    Dim i As Integer 
     
    Dim FStr As String, TempStr As String 
     
    Dim DataTableTL As Range 
    Dim CriteriaTL As Range 
     
    If DataTable.Columns.Count <> Criteria.Columns.Count Or Criteria.Rows.Count > 1 Then 
        MsgBox "Incompatible Data - Please Check Formula" 
        FStr = "" 
    Else 
         
        With DataTable 
            DataTableTL = Cells(.Row, .Column).Address(0, 0) 
        End With 
         
        With Criteria 
            CriteriaTL = Cells(.Row, .Column).Address(0, 0) 
        End With 
         
         ' Rem aiming for formula along the lines of =SUMPRODUCT(--(D4:D11=G4),--(E4:E11=H4),--(F4:F11=I4))
         
        FStr = "=SUMPRODUCT(" 
         
        For i = 0 To Criteria.Columns.Count - 1 
             
            If CriteriaTL.Offset(0, i).Value <> "" Or CriteriaTL.Offset(0, i).Value <> "*" Then 
                 'Add additional entry in formula
                TempStr = "--(" & DataTableTL.Offset(0, i).Address & ":" & DataTableTL.Offset(DataTable.Rows.Count - 1, i).Address _ 
                & "=" & CriteriaTL.Offset(0, i).Address & ")," 
                FStr = FStr & TempStr 
            End If 
             'Otherwise leave formula alone
        Next i 
         
         'Delete last comma in formula then close brackets
        FStr = Left(FStr, Len(FStr) - 1) 
        FStr = FStr & ")" 
         
    End If 
     
    DSRFUNCTION = FStr 
     
     
End Function 


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




Hi!
I would like Excel to look at column A, and if there is a number0, count it and go to the next row. If it's =0, then go to column B and see if THAT number is 0. If it is, count it; if not, go to C. So my data looks like:

A B C
0 2 3
4 0 0
0 0 0

I only want it to count one time per row, so right now I am using =IF(COUNTIFS(A2:A30,"0",B2:B30,"0")0,COUNTIFS(A2:A30,"0",B2:B30,"0"),IF(COUNTIFS(B2:B30,"0",C2:C30,"0")0,COUNTIFS(B2:B30,"0",C2:C30,"0"),0)))

I also tried it with a SUMPRODUCT to simplify but both formulas are getting me the wrong answer. I'm using Excel 2007. Thank you!


Hi,

I have this code which works fine but its kinda slowing down my macro. The macro combines all worksheets in a workbook into a single sheet called "combined". And the sheet named combined will copy a column called test area and a column called Test run.

Currently it takes quite a long time to run compared to other modules.

VB:

Private Function GetCol(s As String, sno As Integer) As Integer 
    Dim i As Integer 
    i = 1 
    With ActiveWorkbook.Sheets(sno) 
        Do While (Trim(.Cells(1, i)) <> "") 
            If (InStr(.Cells(1, i), s)) Then Exit Do 
             'MsgBox .Cells(1, 2)
            i = i + 1 
        Loop 
    End With 
    GetCol = i 
End Function 
 
Sub CombineWorksheets() 
    Dim k As Integer 
    Dim j As Integer 
    Dim i1 As Integer 
    Dim i2 As Integer 
    Dim count As Integer 
    count = Worksheets.count 
    Worksheets.Add After:=Worksheets(Worksheets.count), count:=1 
    ActiveWorkbook.Sheets(Worksheets.count).Name = "Combined" 
    ActiveWorkbook.Sheets(Worksheets.count).Cells(1, 1) = ActiveWorkbook.Sheets(2).Cells(1, 2) 
    ActiveWorkbook.Sheets(Worksheets.count).Cells(1, 2) = ActiveWorkbook.Sheets(2).Cells(1, GetCol(ActiveWorkbook.Sheets("Get Count").Cells(6, 11), 2)) 
     
    i1 = 2 
    For j = 2 To count 
        i2 = 2 
        k = GetCol(ActiveWorkbook.Sheets(1).Cells(6, 11), j) 
        While (Trim(ActiveWorkbook.Sheets(j).Cells(i2, 2)) <> "") 
            ActiveWorkbook.Sheets(Worksheets.count).Cells(i1, 1) = ActiveWorkbook.Sheets(j).Cells(i2, 2) 
            ActiveWorkbook.Sheets(Worksheets.count).Cells(i1, 2) = ActiveWorkbook.Sheets(j).Cells(i2, k) 
            i1 = i1 + 1 
            i2 = i2 + 1 
        Wend 
    Next 
     
    count1 = "Get Count" 
    Merge = "Combined" 
    Pivot = "Status" 
     
    Application.DisplayAlerts = False 
    For Each sht In Sheets 'Remove all sheets except "DATA"
        If sht.Name <> count1 And sht.Name <> Merge And sht.Name <> Pivot Then 
            Sheets(sht.Name).Delete 
             
             
             
        End If 
    Next 
    Application.DisplayAlerts = True 
     
     
End Sub 


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




Hello All,

I have an issue I have been trying to work on all week. Here is my issue
I need to randomly select work for 12 different agent based on a list of 4 jobs.

The work has to be spread out (i.e. 4 agent doing Task 1, 4 doing Task 2...etc)
this also includes the weekend when staffing is only 3 on Saturday and sunday. also the list has to look at the agents schedule to assign a task. Example we can have all of the 9am-6pm agents doing task on b/c after 6pm there will not be anyone left to do that work. the list changes when a new month is selected. task 1-4 will be on a hidden tab with the dates.

I hope i did not confuse anyone. example is below Thank you in advance for any assistance you are able to provide. Please feel free to email me if you have any question. What I am looking for is some suggestion and formulas on which way to go. I have tried the randbetween however it does not spread the work/list out even. Is there any way i can fine tune that function?

Nate
QIBusinessGroup


Agent Name Saturday Sunday Monday Tuesday Wednesday Thursday Friday
Agent 1 Off 2:00 - 6:00 9:00 - 7:00 9:00 - 7:00 9:00 - 7:00 9:00 - 7:00 Off
Agent 2 Off 2:00 - 6:00 9:00 - 7:00 9:00 - 7:00 9:00 - 7:00 9:00 - 7:00 Off
Agent 3 Off Off 9:00 - 6:00 9:00 - 6:00 9:00 - 6:00 9:00 - 6:00 9:00 - 6:00
Agent 4 Off Off 9:00 - 6:00 9:00 - 6:00 9:00 - 6:00 9:00 - 6:00 9:00 - 6:00
Agent 5 10:00 - 7:00 Off 9:00 - 6:00 9:00 - 6:00 9:00 - 6:00 9:00 - 6:00 9:00 - 6:00
Agent 6 Off 2:00 - 6:00 9:00 - 7:00 9:00 - 7:00 9:00 - 7:00 9:00 - 7:00 Off
Agent 7 Off Off 12:30 - 9:30 12:30 - 9:30 12:30 - 9:30 12:30 - 9:30 12:30 - 9:30
Agent 8 Off Off 11:30 - 8:30 11:30 - 8:30 11:30 - 8:30 11:30 - 8:30 11:30 - 8:30
Agent 9 Off Off 11:00 - 8:00 11:00 - 8:00 11:00 - 8:00 11:00 - 8:00 11:00 - 8:00
Agent 10 10:00 - 7:00 Off 10:00 - 9:00 10:00 - 9:00 Off Off 10:00 - 9:00
Agent 11 10:00 - 7:00 Off Off 10:00 - 7:00 10:00 - 7:00 10:00 - 7:00 10:00 - 7:00
Agent 12 Off Off 1:15 - 10:15 1:15 - 10:15 1:15 - 10:15 1:15 - 10:15 1:15 - 10:15


Select A Month
May 5


Agent Name 5/7 - 5/13 5/14 - 5/20 5/21 - 5/27 5/28 - 6/3
Agent 1 Task1 1 4
Agent 2 2 3
Agent 3 3 1
Agent 4 Task 3 4 2
Agent 5 1 3
Agent 6 2 2
Agent 7 3 1
Agent 8 Task 2 4 4
Agent 9 1 2
Agent 10 2 1
Agent 11 3 3
Agent 12 4 4


I've been trying for a few days to figure this out myself but I've either misunderstood the process or the lifetime of Public variables.

I have the following code in a userform module:

VB:

Public LenChk As String 
 
Private Sub textbox2_Enter() 
    LenChk = textbox1.value 
    Call LengthCheck 
End Sub 


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



In a standard module, I have the following code.

VB:

Sub LengthCheck() 
    Dim Length As Integer 
    MsgBox LenChk 
    Length = Len(LenChk) 
    Msgbox Length 
    If Length = 9 Then 
        Msgbox "Lenth is appropriate." 
    End Sub 


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



The first MsgBox gives me a blank (Nothing) and the second gives me 0.

If the value in textbox1 is "backspace", then I would expect the first msgbox to give me the word "backspace" and for the second to give me a value of 9.

I'm guessing there's a problem with the public variable.

Thank you.

Hi.

I'm trying to set up a validation tool for a multi-dimensional account sequence.

My approach has been to set up the validation as text strings containing IF's and using a UDF to convert the strings to excel formulas.

VB:

Function Evalu(ByVal S As String) As String 
     
    Evalu = Evaluate(S) 
     
End Function 


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



My problem boils down to Evaluate() not being able to process the IF or OFFSET functions that I am using, and returns a #VALUE! error.

Am I misunderstanding something about the use of this UDF, or is the problem elsewhere?

Thank you for you time

Hi,

this isnt really an excel problem but I've had great help from here in the past and I'm hoping someone can help with this as well!

I'm trying to predict the failure of a machine, with the probability of failure at time t [P(t)], being just a normal distribution. When the machine breaks down it is immediately replaced by a new machine which has the same probability function.

I've wrote up some code in VBA that models the probability of the second and subsequent failures. As an example, the probability of failure of the second machine in exactly 10 years is:

P2(10)=P(10)*P(0) + P(9)*P(1) + P(8)*P(2) + ... P(0)*P(10)

(i.e, within 10 years, the first machine can break down after 1 year AND the second machine breaks down after 9 years, OR the 1st machine fails after 2 years AND the 2nd machine fails after 8...)

below is the code used to evaluate the failure distribution of each machine (I've evaluated 10 in this example):

VB:

Sub conv_demo() 
     
    Dim T As Double 
    Dim convTime As Double 
    Dim n As Integer, i As Integer, k As Integer, l As Integer 
    Dim X As Double 
    Dim failureNum As Integer 'number of failures to evaluate
    Dim sv() As Variant 
     
    T=100 'time domain to evaluate up to
    n=100 'resolution
     
    failureNum=10 
     
    Redim sv(0 To n, 0 To failureNum) 
     
    For i=0 To n 
        X=i*T/n 
        sv(i,0)=X 
        sv(i,1)=WorksheetFunction.NormalDist(X,10,2,False) 
        For l=2 To failureNum 
            For k=i To 0 Step -1 
                convTime=sv(k,0) 'time of previous failure
                sv(i,l)=sv(i,l)+sv(k,l-1)*WorksheetFunction.NormalDist(X-convTime,10,2,False) 
            Next k 
        Next l 
    Next i 
     
    Sheets("Sheet1").Activate 
    Dim TheRange As Range 
    Set TheRange=Range(Cells(1,1),Cells(UBound(sv,1), UBound(sv,2))) 
    TheRange.Value=sv 
     
End Sub 


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



(For reference, I believe that I am performing a numerical version of the convolution integral to evaluate the probability distribution).

My code works fine when the resolution, n, is the same as the time, t. When plotting a graph of the distributions, the curves become more spread out but the area underneath still stays as 1. If increasing the resolution however, each subsequent curve starts to grow in amplitude.

This makes me think that I need to include 'n' somewhere within the convolution but I am not sure where. If anyone can give any help on this then it'd be much appreciated!

Many Thanks,
Dan

I have two spread sheets (Master and Records). The Identifying factor is the agent number. Example (Agent 1234).

In Record Sheet agent 1234 appears speradically, within each row for agent 1234 there is a month assiged mm/yy (10/09).

I need to record in my Master sheet, how many times agent 1234 appears with month 10/09.

Assuming that agent 1234 is in cloumn A and month is in column D.

I have tried inserting this formula in my Master Sheet
=Countif(RKFSHEETA:A,A2) but this only returns the count of agent 1234 found in RKF sheet. I need the count of 10/09 in column D for agent 1234 in column A.

Please help.


Hi there,

Hopefully this is a simple issue!

Basically I have applied a rather complex conditional formatting formula (manually in Excel) to a range of cells using relative addressing (i.e. no $). The problem is that when I attempt to evaluate the condition in VBA for any of the cells within the range, VBA only evaluates the original formula as if I had used absolute addressing. Is there any easy way to avoid this short of applying a different formula to each cell?

My original attempt is as follows
VB:

overallResult = 0 ' PASS
For Each currentCell In Selection.Cells 
    If currentCell.FormatConditions.Count > 0 Then 
        For i = 1 To currentCell.FormatConditions.Count 
            If (Application.Evaluate(currentCell.FormatConditions(i).Formula1)) Then 
                overallResult = 1 ' FAIL
                Exit Function 
            End If 
        Next J 
    End If 
Next c 


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



Thanks,

--
Joshua

I am using a diamond shape that is copied into the active sheet when the user runs a macro. The user then selects the shape and enters some text. I want to limit the characters to a certain number or at least be able to give the user some advice when they enter too many characters.

I have found that, as long as I have manually named the shape "Diamond1", I can get the count in my immediate window with the following:

VB:

 
?ActiveSheet.Shapes("Diamond1").TextFrame.Characters.Count 


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



but I want the count to relate to the active shape. So I tried:

VB:

?selection.shapeRange.TextFrame.Characters.Count 


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



and I get a "runtime error 438 Object doesn't support this property or method".

How should I count the characters in the selected shape, and then if it is over say 12, require the user to shorten it or at least present yield a message Box?

Thanks for your help,
Sheila