Posts by turtle44

Re: Return Matrix Results Based On Two User-entered Selections

Assuming your dropdowns are in cells G1 and H1, this formula will do the trick:
[bfn]=INDEX($A$1:$D$4,MATCH($G$1,$A$1:$A$4,0),MATCH($H$1,$A$1:$D$1,0))[/bfn]

Re: 2003 Code Stops In 2007

Quote from dec

Well, it still doesn't seem to work.

What have you changed in your attempt to make it work?

Re: 2003 Code Stops In 2007

No, it is my understanding that any code that uses Application.FileSearch will not work in Excel 2007. I don't use Excel 2007, so this doesn't concern me, and I therefore have no solution for you. However, there are plenty of people who do use Excel 2007 and I'm sure that they have figured out ways to get around this. If you search the forum for "FileSearch in Excel 2007", you should be able to find said work-arounds.

Re: 2003 Code Stops In 2007

Yes.

Re: 2003 Code Stops In 2007

Application.FileSearch is not supported in Excel 2007. A search for "FileSearch in Excel 2007" should give you some work-arounds.

Re: Count Unique Entries Within Variable Date Range

Your DCount formula in G10 yields 29 (which is the number of unique entries in your list). Why do you have a note stating that the result should be 11? I don't understand. If you change your criteria to look at 1/7/2008 only, your formula gives 13, which also seems to be correct, i.e. there are 28 entries, but only 13 of them are unique. What am I missing here?
EDIT: Ok, now I see what I'm missing. The formula works, but only when 1/7/08 is the start date. Let me look at this at little more.[hr]*[/hr] Auto Merged Post;[dl]*[/dl]My solution is rather ugly so hopefully someone else will come along with something better. Until then, this method will work. Based on the data in your sheet 1, I made the following assumptions:

• Your dates are in Column A and are sorted in ascending order.
• Your start date is G1
• Your end date is H1
• Your unique count is based on ID.

Create a range named "rng" [bfn]=OFFSET($A$1,MATCH($G$1,$A:$A,0)-1,4,MATCH($H$1,$A:$A,1)-MATCH($G$1,$A:$A,0)+1,1)[/bfn]
Then use this formula to get your unique count:
[bfn]=SUMPRODUCT((rng<>"")/COUNTIF(rng,rng&""))[/bfn]

Re: Iterative Calculation

Quote from amain

Hello, I need to perform an iterative calculation that will continue until the value is below a certain number.

Only way I know is with UDF. Something like:

Function TestFunction(B, M, X, Y)
   Dim A
   Dim arr(1)
   Do
      A = M * (X - Y)
      X = X - 1
   Loop Until A < B
   arr(0) = A
   arr(1) = X
   TestFunction = arr()
End Function

Re: Selecting Multiple Ranges Meeting One Condition

If your data is all in one row, what exactly do you mean by "bottom right"? Do you just want to select the 3 cells to the left of the blank cell?

Re: Formula To Count Number Of Characters In Each Cell

Did you look at any of the Possible Answers above?

Re: Adding Upper Function To Exisiting Code

Quote from ge0rge

But I am greeted with a Compile Error: Ambiguos name detected: Worksheet_Change

You cannot have 2 subs with the same name.

Re: Re-Enable VBA Code Protection

Quote from Techhie_Tim

turtle44: The code does not have a digital signature. Would that affect anything in this case?

It shouldn't, but I have had problems before when someone using Excel 2000 worked with files that I had saved in Excel 2003. The problem only occurred, however, when I had my code digitally signed. Once I removed digital signature, everything was fine. My code is always protected, so I don't understand why you are having a problem with compatibility.

Re: Roundup If Value Great Than X.xx5

You're welcome. For the record, you may want to change your title so that it is more descriptive of your actual question, perhaps something like "VLookup Based on Rounded Values."

Re: Roundup If Value Great Than X.xx5

[bfn]=IF(ISERROR(VLOOKUP(ROUND(E4,2),tier_upsell,3,TRUE)),"",VLOOKUP(ROUND(E4,2),tier_upsell,3,TRUE))[/bfn]

Re: Roundup If Value Great Than X.xx5

Quote from Avis Guy

there is a different cell that refrences that answer by doing a VLOOKUP

We are giving you the formula to use in that cell.

Re: Re-Enable VBA Code Protection

Does your code have a Digital Signature?

Re: Event Macro To Move Cell Pointer To True Cell

Something like this?

Private Sub Worksheet_Change(ByVal Target As Range)
   On Error Resume Next
   Cells.Find(What:=True, LookIn:=xlValues).Select
End Sub

Re: Loop Through Cell Comments

Andy, I think I like SpecialCells better than my method, but you would need to test that comments existed or your code will cause error. Something like this perhaps:

Sub LoopSelectedCellComments()
   Dim rngTemp As Range
   Dim rngComment As Range
   Dim strBuf As String
   On Error Resume Next
   Set rngComment = Range("B2:D10").SpecialCells(xlCellTypeComments)
   On Error GoTo 0
   If rngComment Is Nothing Then
      MsgBox "no comments found"
      Exit Sub
   End If
   For Each rngTemp In rngComment
      strBuf = strBuf & rngTemp.Comment.Text
   Next
   Range("A1") = strBuf
End Sub

Re: Roundup If Value Great Than X.xx5

If your lookup table is in A1:B3 and is sorted in ascending order, and the value you are looking up is in A6, then this formula should work:
[bfn]=VLOOKUP(ROUND(A6,2),$A$1:$B$3,2,TRUE)[/bfn]

Re: Two listboxes item select simultaneously and paste on sheet

Yes. As long as ListBox1 has its MultiSelect property set.

Re: Add Incrementing Letter To The End Of Each Cell In A Highlighted Range

Here is a slightly longer solution that allows for more than 26 cells.

Sub AddLetters()
   Dim i As Long
   Dim x As Long
   Dim y As Long
   Dim sTemp As String
   Dim Cell As Range
   i = 0
   For Each Cell In Selection
      i = i + 1
      x = ((i - 1) Mod 26) + 1
      y = (i - 1) \ 26
      
      If y < 1 Then
         sTemp = Chr(96 + x)
      Else
         sTemp = Chr(96 + y) & Chr(96 + x)
      End If
      Cell.Value = Cell.Value & sTemp
   Next Cell
End Sub