searching for header

[ian0886]

Hi,

I'm trying to find the column from the header and using the result, to format the entire column. below is what i've writen.

Dim lnRow As Long

lnRow = Worksheets("ADSS").Cells("1:1").Find(What:="Party", LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
               
Range("inrow:inrow").NumberFormat = "0"

When i'm running it i face an error 'invalid procedure or argument', i can't seem to figure out why.

Thanks in advance

 

[Leith Ross]

Hello ian0886,

The Find method returns a Range object. If Find is successful then a range object is returned. If not then the special Object variable Nothing is returned.

The problem is you are trying to set a variable that has been typed as Long to an Object that is a Range. A better practice is to type the variable as a Range.

Columns are vertical and Rows are horizontal across the worksheet.

    Dim Cell    As Range
    Dim Headers As Range

        Set Headers = Worksheets("ADSS").Range("A1", Cells(1, Columns.Count).End(xlToLeft))
        
        Set Cell = Headers.Find(What:="Party", LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, MatchCase:=False, SearchFormat:=False)

        If Cell Is Nothing Then
            MsgBox "Header was Not Found."
            Exit Sub
        End If

        Cell.EntireColumn.NumberFormat = "0"

 

[ian0886]

thanks @leith ross, it works!

 

[ian0886]

Sorry, but i'm trying to do the same but instead now i wish to find the blank spaces and subsequently replace all with comma.

Set Cell2 = Headers.Find(What:="Amount", LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, MatchCase:=False, SearchFormat:=False)

Cell2.EntireColumn.Select
Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False


selction.Style = "Comma"

Why won't it work the same way. sorry i've just starting learning VBA

 

[Leith Ross]

Hello ian0886,

In VBA, it is rarely necessary to either Select or Activate an Object. This code should work...

Set Cell2 = Headers.Find(What:="Amount", LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, MatchCase:=False, SearchFormat:=False)


Cell2.EntireColumn.Replace What:=" ", Replacement:=",", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

 

[ian0886]

I've got an error msg still ' object variable or with block variable not set'