Posts by Colin1234

Re: Looping to fill textboxes based on others. ("txtbox" & i).Value?

Works perfectly, Bob! Thanks !

Hi all,

I have a userform with 2 columns and 12 rows of textboxes. The first column is txtCode1, txtCode2 and so on. The second column is txtDescription1, txtDescription2, etc. I want to fill the second column based on entry in the first.

I'm trying to use the code below to make it do things if the value of one of the textboxes in the first column is "A", and it doesn't want to recognize the "Select Case CtrlStuff" line. I wish I could use something like

Select Case ("txtCode" & i).Value

In other words, I'm trying to match a txtDescription with its txtCode based on their numbers. ExtractNumber is a function from ozGrid's free function page (which is awesome!) that "will extract the numeric portion from a Text String." Thanks!

Dim i As Integer
Dim CtrlStuff As String


    For Each Control In UserForm.Controls
        Select Case TypeName(Control)
            Case "TextBox"
                For i = 1 To 12
                    If ExtractNumber(Control.Name) = i Then
                        Select Case Left(Control.Name, 7)
                            Case "txtCode"
                                CtrlStuff = Control.Value
                            Case "txtDesc"
                                Select Case CtrlStuff
                                    Case "A"
                                        'Do a lot of stuff
                                End Select
                            Case Else
                        End Select
                    End If
                Next i
            Case Else
         End Select
Nextone:
    Next Control

Re: Delete non-numeric characters (like symbols) from cells

Jack,

This is really awesome. Thanks !

Re: Delete non-numeric characters (like symbols) from cells

Awesome, works perfectly! Thanks guys

Re: Delete non-numeric characters (like symbols) from cells

About half are

###-###-####

Others are

###/###-####
###.###.####
(###)###-####
### ### ####

And some other strange combinations of these symbols.

Hi all, I have a huge list of phone numbers, and I want them to be listed as numbers without dashes or parenthises (e.g. 1234567890) so that I can format them all how I want. How can I go about doing this? The code below is all that I could come up with, but I know almost nothing about the Characters property.

Public Sub fff()
    For Each character In cell.Characters
        If Not IsNumeric(character) Then
            character.Delete
        End If
    Next character
End Sub

Thanks!

Re: Sort Listbox by clicking on column header?

Norie,

That's how it is now

They type the criteria in the textbox and then press search. I should have been more clear about that. Some of the entries in this sheet are very similar, so I was looking to simplify it a bit more. I will look into the labels option. Thanks very much for your help

I have a userform with a listbox. The listbox is populated through a textbox and command button at the top. You type what you want in the top, and then the command button ("Search") populates the listbox.

The listbox has 8 columns. I'm wondering if there's a way to sort by columns when the user can't find the thing he or she is looking for. It populates from a range, and I'm assuming the only way to sort the listbox is to sort the range, but I don't know all that much about listboxes, as this is my first time to work with them. The ideal situation would be something like Windows Explorer, where you can click on the column headings (Name, Type, Modified, etc) and have it sort Ascending or Descending like that.

I'm just wondering if there's a built-in way to do this that I'm overlooking. If there is not, then I probably don't want to put in this feature, as it would delay the completion of this project.

Thanks!!

Re: Dynamic named range in VBA - row # changed when run

I switched to RC, and it works now:

Sub Macro1()


On Error Resume Next
ActiveWorkbook.Names("lateaccounts").Delete


ActiveWorkbook.Names.Add Name:="lateaccounts", RefersToR1C1:= _
    "=OFFSET('Late Accounts'!R1C1,1,0,COUNTA('Late Accounts'!R1C1:R10000C1)-1,COUNTA('Late Accounts'!R1:R1))"
        
End Sub

I'm still curious as to why it was changing the numbers in the first example.

I have a dynamic named range in my sheet called "lateaccounts" (the sheet is also called "Late Accounts."

Sub Macro1()
On Error Resume Next
ActiveWorkbook.Names("lateaccounts").Delete


ActiveWorkbook.Names.Add Name:="lateaccounts", RefersTo:= _
    "=OFFSET('Late Accounts'!$A$1,1,0,COUNTA('Late Accounts'!$A$1:$A$10000)-1,COUNTA('Late Accounts'!1:1)-1)"
End Sub

I want to re-define this named range. When I use the code above, the last part of the code,

('Late Accounts'!1:1)

becomes

('Late Accounts'!17:17)

Any idea why?

Re: Macro should delete 20 consecutive columns, only does every other column

Aaron,

This is perfect!! Works like a dream

Thanks!

I have 25 columns. Column 1 is an account name, and the rest of the columns follow this format:

Jan-A
Jan-B
Feb-A
Feb-B

etc.

I want only the correct month's two columns to show, and I want to delete the other 22 columns. Cell A1000 houses the correct month.

When I run my macro, it deletes every other column. If I run it again, it does every other column again. I have to run it about 5 times to get them all gone, but it does eventually leave me with the correct columns. Here is the code I have to delete the non-applicable months:

Sub Macro7()


ActiveWorkbook.Sheets("late accounts").Activate
For Each Cell In Range("B1:Y1")    'the column headings
    If Left(Cell, 3) <> Left(Range("a1000"), 3) Then
        Cell.EntireColumn.Delete
    End If
Next Cell


End Sub

Hi everyone,

I have a spreadsheet with 1,000 businesses. The sheet shows their states, cities and addresses, but I need the zip codes. Here's the layout:

Column A: State
Column B: City
Column C: Business Name
Column D: Street Address

I'm wondering if MapQuest can search the 1,000 addresses and store the zip code for each account. This may be kind of "out there," but I've seen some cool MapQuest stuff on here.

Thanks!

Hi all,

I have a userform that houses many textboxes. Sometimes, when I type something in one of the textboxes and press Tab to go to the next one, the Tab key just makes the cursor go to the right in the same text box.

This is a very rare occurence, but it's annoying when it happens. I haven't been able to make it happen consistently enough to figure out what's wrong. Have y'all experienced this error? Do you know how to fix it?

Thanks!!

Re: Return control name as string and hide corresponding named range

Ohhhhh! Now it makes sense... thanks

Re: Return control name as string and hide corresponding named range

Thanks for the response! I fixed that part of the code, but I'm still getting Run-time error '438' - Object doesn't support this property or method for this line:

ActiveWorkbook.Range(CtrlName).EntireRow.Hidden = True

Is it possible to select a named range like this? I rechecked the named ranges, and they are exactly equal to the names of the controls.

Thanks!

I have a userform that includes 13 checkboxes. Those checkboxes correspond to a list on the worksheet. Each cell is a named range that corresponds to the name of a checkbox. When the user presses okay, I want this to hide the row of any checkbox that is unchecked. I know this is in the initialize section -- it's just because I think it's easier to put it there until I get it right.

I'm getting errors for this line: Set CtrlName = Ctrl.Name -- not sure what I'm doing wrong.

Thanks!!

Private Sub UserForm_Initialize()
Dim Ctrl As Control
Dim CtrlName as String


With Userform
    For Each Ctrl In Userform.Controls
        If TypeName(Ctrl) = "CheckBox" Then
            Set CtrlName = Ctrl.Name
                ActiveWorkbook.Range(CtrlName).EntireRow.Hidden = True
        End If
    Next Ctrl
End With


End Sub

Re: Change vertical data to horizontal (columns to rows) for validation

Thanks! I'll play around with this.

Re: Change vertical data to horizontal (columns to rows) for validation

Hi guys,

I finally found where the error was, and it was my fault (duh!). The first time I ran it, I forgot to set the output range to the next sheet, so it pasted the data underneath the original on sheet1. Then every time I ran the code it was sorting already duplicated data.

The code works very well. Jindon, I have not yet tried your code because I really don't understand it I still plan to test it out because I want to understand it, but I knew that I would have no clue how to alter it to my workbooks. Thanks for your work, and it won't go to waste -- I just need to do some learning first! Your post is helpful, and hopefully I'll learn it soon. This dictionary thing looks awesome. I just didn't know if I'd have time to learn it for this particular project.

The code now works exactly as intended, and everything is perfect. I have a new problem matching data validation lists now. I'm not sure if I should start a new post for this, so I will if you tell me to. Here's the problem:

I use the following code to name the ranges after this code transposes the data into rows:

Private Sub Names


'this sub resets named ranges for the validation listboxes


Dim rCell As Range
    Application.DisplayAlerts = False
'    On Error Resume Next
        With Sheet3
            For Each rCell In .Range("a2").CurrentRegion.Columns(1).Cells
                .Range(rCell.Cells(1, 2), rCell.End(xlToRight)).Name _
                            = Replace(rCell.Cells(1, 1), " ", "_")
            Next rCell
            .Range("A1").CurrentRegion.Columns(1).Name = "UniqueGroups"
        End With


'    On Error GoTo 0


End Sub

I have several names that are apparently invalid entries for named ranges, so my list only populates about half the total number of entries. Some of these entries have ampersands (&), slashes (/), quotes ("") and/or percent signs (%). Is there a way to work around this for naming the ranges so that they can show up in the validation lists?

Thanks again!!

Re: Change vertical data to horizontal (columns to rows) for validation

Thanks for the replies! This is awesome, but I'm having a problem with it. It pastes all the data exactly like I want it, but then it pastes a duplicate list underneath where it relists the data, but it only shows the first carrier for that company.

Original Data:
Company (Col a)....Carrier (col b)
Colin, Inc...................1
Colin, Inc...................2
Colin, Inc...................3
Colin, Inc...................4
Steven, Inc................1
Steven, Inc................5

Data produced on sheet 2:
(col a)........(col b)...(col c)...(col d)....(col e)
Colin, Inc........1...........2...........3...........4
Steven, Inc.....1...........5
Colin, Inc........1
Steven, Inc.....1

How can I get rid of this duplicate section? Here's the code I'm using:

Sub TransposeRawData()


     'Local Variables
    Dim cell As Range
    Dim rngOut As Range
    Dim dblOffset As Double
     
    With Sheet2
        If ActiveSheet.Name = .Name Then Range("Company").Select
    End With


     'Loop
    For Each cell In Range("Company")
        If cell <> cell.Offset(-1, 0) Then
            dblOffset = 0
            Set rngOut = Sheet3.Range("A65536").End(xlUp).Offset(1, 0)
            rngOut = cell
            Do While cell.Offset(dblOffset, 0) = cell
                rngOut.Offset(0, 1 + dblOffset) = cell.Offset(dblOffset, 1)
                dblOffset = dblOffset + 1
            Loop
        End If
    Next cell
     
End Sub

Thanks!!