• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Adding list array to macro to run on multiple columns

T

Tim Hanson

Member
I have a proper case macro that I need to run on multiple columns.

I am tiring to add an array of header names and so run from this list.

When I run the compound macro nothing happens, no errors but no execution of the propercase macro either

Thanks

Code: 
Option Explicit

Sub ProperCaseByList()
  Dim strSearch As String
  Dim aCell As Range
  Dim LastRow As Long

    Dim myRange As Range
    Dim tmpString As String, Convert_This As String
    Dim MyString As Variant
    Dim I As Long
    Dim X As String


'Application.ScreenUpdating = False

'****************************************************************
        strSearch = "user first name, user last name, first name, last name"
'****************************************************************
    With Sheets(1)

        Set aCell = .Rows(1).Find(What:=strSearch, LookIn:=xlValues, _
                                  LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                                  MatchCase:=False, SearchFormat:=False)

        If Not aCell Is Nothing Then

            LastRow = .Cells(.Rows.Count, aCell.Column).End(xlUp).Row
          
        With myRange = .Range(aCell, .Cells(LastRow, aCell.Column))

'PROPERCASE
'The original range
  'Set myRange = .Range("D2", .Range("D" & .Rows.Count).End(xlUp))

        For Each aCell In myRange
            tmpString = vbNullString
            If InStr(1, aCell.Formula, ",") Then
                MyString = Split(aCell.Formula, ",")
                Convert_This = MyString(0)
                For I = 1 To UBound(MyString)
                    tmpString = tmpString & MyString(I) & ","
                Next I
                tmpString = Left(tmpString, Len(tmpString) - 1)
            Else
                Convert_This = aCell.Value
            End If
            aCell.Value = Application.WorksheetFunction.Proper(Convert_This) & IIf(tmpString = vbNullString, tmpString, "," & tmpString)
          
        Next aCell
      End With
        
    End If
  End With
  
    'Application.ScreenUpdating = True
  
End Sub
 
Hi Tim ,

What do you want the macro to do ?

The string which you are searching for is not available in one cell ; it is spread over 4 cells. The FIND statement will not work.

If you can explain what you want to do , then the macro can be written.

Narayan
 
Hello Narayan

I was tiring to execute the prorercase macro from a list so as not to call it 4 times.

I liked the idea of running a macro on multiple columns so I was tiring to use this as a opportunity to try my hand at it
 
Narayan, thanks, it works great in the test file but when I copy and paste into my workbook the macro works but after it runs I get the error "Type mismatch" and "Convert_This = aCell.Value" is highlighted.

Can you think of a reason why this would happen, the macro is in a module by itself.
 
Hi Tim ,

Can you troubleshoot this yourself ?

When you get the error , click on Debug , and in the Immediate window , type in :

?aCell.Address

and see what address is displayed. Go to this address in the worksheet , and see what the cell contains.

Narayan
 
Narayan,

Thanks for the little tutorial in troubleshooting I had not done this before.

The problem was a cell contained "#NAME?"

Thanks again for the code I could follow what you did so I am hopeful I will be able to repeat it for other macros I need to add a list to
 
You must log in or register to reply here.
Back
Top