Posts by turtle44

Re: Vb Code

Welcome to Oz, but if "VB Code" is your idea of a descriptive thread title, you may want to read the rules again.

Re: Find Text In A String Of Text And Return Whole Found Text

Quote from kamoore

that formula will look in a list of "long text" in column b and return the specific long text that includes the "short text"?

I know. That seems to be what you asked, although I'll admit your question wasn't easy to decipher.

Edit: Since your last edit made more sense, I assume this is what you really wanted:
[bfn]=INDEX(B1:B45,MATCH("*"&A1&"*",B1:B45,0),1)[/bfn]

Re: Find Text In A String Of Text And Return Whole Found Text

Assuming "short text" is in column A and "long text" is in column B, this formula should give your desired results:
[bfn]=IF(ISNUMBER(SEARCH(A1,B1,1)),B1,"not found")[/bfn]

Re: Textbox Password Complication

Quote from ShawnIRQL

I don't want the textbox to appear when hitting the button to hide

I'm not sure what you are asking, but "PasswordBox.Show" is always going to be called, because you are testing if column is hidden imediately after you hide it. Is this what you want:

Sub PasswordBoxCode()
   ActiveSheet.Unprotect ("13792468")
   With Columns("B:B")
      If .EntireColumn.Hidden = False Then
         .EntireColumn.Hidden = True
      Else
         PasswordBox.Show
      End If
   End With
   ActiveSheet.Protect ("13792468")
   Range("A1").Select
End Sub

Re: Find And Replace Old Data With New Data

Try this:

For Each cell In rng2
On Error Resume Next
rng1.Find(cell).Offset(0, 1) = cell.Offset(0, 1)
If Err.Number <> 0 Then MsgBox cell.Value & " was not found in master list"
On Error GoTo 0
Next cell

Re: Commanbutton To Close Active Workbook And Open Different Workbook

Did you look in the help file to find the proper syntax for the Close method? I don't mind offering my help, but it would be nice if you were willing to put forth a little effort to find the solution on your own. Having said that, I assume this is what you need:

Workbooks("test1.xls").Close

Re: Running A Loop And Pasting Over Random Decimal Numbers

Quote from sam000

This only occurs when the source values in the excel spreadsheet are decimal numbers generated by the use of =RAND.

If I had to guess, I'd say it only occurs when the number generated by Rand is less than .5. When the number is greater than .5, I imagine you get 1, not 0. Since your variable is an integer, and the number you are evaluating is always going to be between 0 and 1, you will always get either 0 or 1 when you convert to integer.

Re: Find And Replace Old Data With New Data

Either you are leaving out information that is vital to your question, or you are making this far too complicated. Why not just copy Sheet2 and paste over Sheet1?

Re: Commanbutton To Close Active Workbook And Open Different Workbook

If you use ".Open" to open, I would try using ".Close" to close.
Just a suggestion.

Re: Show/hide Row Based On Column Value

In that case, have your macro apply the advanced filter.

Re: Getting Multiple Items From An Unsorted Table

Quote from aslsw

Thanks. It might come to that, but I would prefer something a bit more 'elegant' (no offence) that removes the blank lines.

a) That's why I would use a filter.
b) What blank lines? The ones you included in your example table?

Re: Show/hide Row Based On Column Value

Quote from VBA Rookie

Simple AutoFiltering won't work here though.

That's why there is an Advanced Filter.

Re: Getting Multiple Items From An Unsorted Table

Personally, I wouldn't use a Lookup function, I would just filter the Itinerary list for Nights > 0 and use the filtered list as my Accomodation list. If you have your heart set on using VLookups, this might be want you want:
Cell C13 copied down: [bfn]=VLOOKUP(B13,$B$4:$F$9,5,FALSE)[/bfn]
Cell D13 copied down: [bfn]=IF(C13=0,"",VLOOKUP(B13,$B$4:$F$9,4,FALSE))[/bfn]

Re: Macro To Delete Entire Row Based On Specific Word In Cell With Multiple Words

I would use advanced filter to copy all records that don't contain the word "hospital".

Re: Keyboard Shortcut Causes Error

I'm stumped. Apparently, you can't use Workbook.Open in a macro called from keyboard shortcut. Don't quote me on that, but in my limited testing, I could never get it to work. If you add a custom button or custom menu, you can call macro from there.

Re: Keyboard Shortcut Causes Error

Quote from otictac1

But it still works perfectly fine when I run it from the Tools screen, but stops halfway through when I try it with the keyboard.

Where does it stop?

Re: Use Macro To Loop Through Data And Copy Rows

First of all, I would change your thread title since there is no need for loop. I would just apply an advanced filter to copy all rows that have "yes" in column G.

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

I was getting ready to sign out for the night, but all this talk of brilliance forced me to stay. Is that what you wanted John?

Sub AddLetters()
   Dim i As Long
   Dim x As Long
   Dim y As Long
   Dim z As Long
   Dim lCells As Long
   Dim sTemp As String
   Dim Cell As Range
   Application.ScreenUpdating = False
   i = 0
   lCells = Selection.Cells.Count
If lCells  > 26 * 26 * 26 Then
      MsgBox "This many cells would require 4 letters." & vbCrLf & _
            "This macro is only equipped to handle 3 letters, i.e. 17576 cells."
      Exit Sub
   End If


   For Each Cell In Selection
      i = i + 1
      x = ((i - 1) Mod 26) + 1
      y = (((i - 1) Mod 676) \ 26) + Application.WorksheetFunction.Min(1, (lCells - 1) \ 26)
      z = ((i - 1) \ 676) + Application.WorksheetFunction.Min(1, (lCells - 1) \ 676)


      If z < 1 Then z = -64
      If y < 1 Then y = -64
      sTemp = Chr(96 + z) & Chr(96 + y) & Chr(96 + x)
      Cell.Value = Trim(Cell.Value & sTemp)
   Next Cell
   Application.ScreenUpdating = True
End Sub

Re: Count Unique Entries Within Variable Date Range

Quote from Dave Hawley

Dom, your formula returns 29, same as the DCOUNT?

I thought DCount worked when I first looked at it too. However, it only works when your start date in the first date listed, i.e. 1/7/08. DCount fails for any other start date, whereas Dom's formula seems to work for any date range.

Re: Count Unique Entries Within Variable Date Range

Yep, that's better.
Domenic, what does the tilde in the match formula do?