Posts by turtle44

Re: Copy To Destination,only Value

rngtocopy.Copy
Cells(5, 14 + cc + (t - 1) * 3 + (i - 1) * 17).PasteSpecial (xlValues)

Re: Conditional Importing Of External Cells

Try inserting this code into your existing code.

Dim rFound As Range
    On Error Resume Next
    Set rFound = Nothing
    Set rFound = wbResults.Worksheets(1).Range("B:B").Find _
    (What:="Application ID", LookIn:=xlValues, _
    LookAt:=xlWhole, SearchDirection:=xlNext).Offset(0, 1)
    On Error GoTo 0
    If Not rFound Is Nothing Then
        'paste value to destination workbook
        wbCodeBook.Worksheets(1).Range("A65536").End(xlUp).Offset(1, 0) = rFound
    End If

Re: Extract Numbers And Text From Listbox

This should work, assuming your data is always delimited by a hyphen.

x = Trim(Left(ListBox1.Text, InStr(1, ListBox1.Text, "-", vbTextCompare) - 1))
y = Trim(Mid(ListBox1.Text, InStr(1, ListBox1.Text, "-", vbTextCompare) + 1))

Re: Copying A Hyperlink

There may be a way, but I don't know it. As a workaround, just filter your list in place then copy to another location; that will keep links. Something like this:

Sub CopyLink()
Dim rng As Range
Set rng = Range("A1:H33")
rng.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("J4:O6"), _
        Unique:=False
rng.Cells.SpecialCells(xlCellTypeVisible).Copy Destination:=Range("Overdue!A1")
End Sub

Re: Conditional Importing Of External Cells

Quote from Sweater Fish Delux

Let's say the search term is 'Application ID,' how do I look through the source worksheet for that string in column B and if I find it bring the string from column C on the same row into my destination sheet?

This will find first match. If there is more than one, search the help for FindNext.

Sub CustomFind()
    Dim rFound As Range
    On Error Resume Next
    Set rFound = Nothing
    Set rFound = Workbooks("SourceWB").Worksheets("SourceWS").Range("B:B").Find _
            (What:="Application ID", LookIn:=xlValues, _
            LookAt:=xlWhole, SearchDirection:=xlNext).Offset(0, 1)
    On Error GoTo 0
    If rFound Is Nothing Then
        MsgBox "not found"
    Else
        'paste value to destination workbook
        Workbooks("DestWB").Worksheets("DestWS").Range("DestRng") = rFound
    End If
End Sub

Re: Count where individual cell results product of formula

How were your results using "Count" inaccurate?

Re: Replace Function - Remove X Number

Why are you searching for a decimal? It seems that all you need is everything to the left of the ampersand. If that is the case, this should work:
[bfn]=LEFT(E2,FIND("&",E2,1)-1)[/bfn]

Re: Find Then Unhide Help

I have removed the .Select statements (as a general rule, there is rarely a need to select a worksheet or range), and added a test to make sure the value exists (if the value you are searching for doesn't exist, you will be trying to unhide a column which doesn't exist and that would generate error). The following code is based on the assumption that you are only searching in your range "P10:AV254". I have also added the code in case you want to search all cells.

Private Sub CommandButton1_Click() 'This is for the "OK" button
   'Declare variables to hold the value of caption
   Dim WS As Worksheet
   Dim rng As Range
   Dim rFound As Range
   'Assign variables, Declare active sheet and range
   Set WS = ThisWorkbook.Worksheets("General")
   With WS
      Set rng = .Range("P10:AV254")
      ' Hide all data within range
      rng.EntireColumn.Hidden = True
      ' Begin your Selection
      If CheckBox1.Value = True Then
         Set rFound = rng.Find(What:=CheckBox1.Caption, LookIn:=xlFormulas, LookAt:=xlPart, _
               SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False)
'In the above line of code, change "rng" to ".cells" if you wish to search entire sheet
         If rFound Is Nothing Then
            MsgBox "Value not found"
         Else
            'Want to unhide a particular column base on
            .Columns(rFound.Column).EntireColumn.Hidden = False
         End If
      End If
   End With
End Sub

Re: Color Coding Cells With Vba

I'm not sure if this is the best way, and I guess it could be slow if you have a lot of cells with formulae, but this seems to work:

Private Sub Worksheet_Calculate()
   Dim Cell As Range
   For Each Cell In Sheet1.UsedRange.SpecialCells(xlCellTypeFormulas)
      Select Case UCase(Left(Cell, 3))
         Case "VOL"
         Cell.Interior.ColorIndex = 5
         Case "MOM"
         Cell.Interior.ColorIndex = 6
         Case Else
         Cell.Interior.ColorIndex = xlNone
      End Select
   Next Cell
End Sub

Re: Automatically Insert Row When Row Value In Column Changes

You're welcome.

Re: Check & Add Sequential Numbers In Column

I don't won't to turn this thread in a war or words, so I will make the following points once again then consider this matter closed.

Quote from jerrychem

I thought when I said I had thousands of data sets within a spreadsheet, and where A and B can only consist of 2 columns, then I figured it was assumed that the data did not only exist in columns A and B.

I wouldn't assume that for the following reasons:
1) A data set could easily consist of only 2 columns, so having thousands of them could easily mean that you have 2 columns and thousands of rows
2) Since your version of Excel has only 256 columns, "having thousands of data sets" obviously doesn't mean that you have thousands of columns
3) You explicitly stated that your data was in Columns A and B.

Quote from jerrychem

Lighten up turtle44. I'm okay with moving the columns.

You called moving the columns a "burden" (your word, not mine), so I was simply trying to help you by stating that if you had given an actual description of your problem, then the solution probably would have been tailored to fit your needs without the burden of moving data. And for the record, there is no need for me to lighten up as I am perfectly calm and was simply trying to help.

Re: Cell Value As Sheet Name In Function

I'm on Excel 2003 and it works fine for me. Your sheet isn't in a different workbook, is it?

Re: Check & Add Sequential Numbers In Column

My point is this:
If you had told us where your data actually was, instead of stating that it was in Columns A and B when this is clearly not the case, then the solution would have worked without your having to move the data to Columns A and B first.

Re: Place Variable Data Count In New Sheet

Quote from goatdan

I do not always know which numbers will be in the original spreadsheet.

As I said, Advanced Filter will give a unique list.

Quote from goatdan

Finally, what I am hoping to do with this eventually is that there is additional data that I want to sort by -- for instance, each of these can also be sorted by "Morning, Afternoon, Night, Overnight".

SumProduct would work for this, but a Pivot Table is probably your best bet.

Re: Place Variable Data Count In New Sheet

Quote from goatdan

I cannot seem to correctly formulate a looping statement which correctly counts the above.

Why loop? Advanced Filter will give you a unique list, Countif will give you the count.
Pivot table should work as well.

Re: Use Cell Value As Sheet Name In Index Function

Does you sheet name contain a space? If so, you will need to enclose sheet name between single quotes.

Re: Find And Set Cmbo Value Based On String

Quote from xclerant

How can i Loop through a combobox's values and compare to a string value and then set the listindex of the combobox to that value?

What???

Re: Sum Column K For A Worksheet Macro

If you want to find the subtotal of a filtered list, you should probably use the Subtotal function...it's not just a clever name
If you need it to be in a macro, then record a macro as you are entering the formula.

Re: Check & Add Sequential Numbers In Column

The solution given was based on your original statement that

Quote from jerrychem

I have two columns A and B filled with numbers

Then you stated

Quote from jerrychem

now i just transfer data in column A and B(a burden...

If your data wasn't in Columns A and B, you shouldn't have stated that it was.

Re: Copy Cells In Loop Based On Loop Increment Being Multiplied

You're welcome.