CODEWITHSUNDEEP
vbaexcel
Robinsper
Robinsper

Reputation: 79

Excel VBA find address and assign to variable then reuse value

I'm trying to find if there's a given title on a cell, pass the address of that cell to a variable and use such location to adjust the size of the column. The reason I'm doing this is because I'm writing several functions which will shift the position of the columns. I'd appreciate it if someone could take a look and tell me what I'm doing wrong.

Option Explicit

Sub adjustColumns()

Dim PONumberCell As String
Dim PONumberAddress As Range
Dim TopLabelinColumn As Range

For Each TopLabelinColumn In Range("A1:Z1").Cells
    If TopLabelinColumn Like "PO_NUMBER" Then TopLabelinColumn.Value = "PO"
    PONumberCell = TopLabelinColumn.Address

    Set PONumberAddress = PONumberCell
    PONumberAddress.ColumnWidth = 70

Next TopLabelinColumn

End Sub

Upvotes: 3

Views: 1689

Answers (2)

user3598756
user3598756

Reputation: 29421

edited after OP's further request:

you are confusing a Range object (such as PONumberCell is meant to be) with a String variable one (like PONumberAddress), so 

Set PONumberAddress = PONumberCell

doesn't work because you are trying to assign an object variable to a String one

but you can be more effective avoiding the loop and using the Find() method

Option Explicit

Sub adjustColumns()
    Dim PONumberAddress As String
    Dim PONumberCell As Range

    Set PONumberCell = Range("A1:Z1").Find(what:="PO_NUMBER", LookIn:=xlValues, lookat:=xlPart, MatchCase:=False)
    If Not PONumberCell Is Nothing Then
        With PONumberCell
            .value = "PO"
            PONumberAddress = .Address
            .EntireColumn.ColumnWidth = 70
        End With
    Else
        Set PONumberCell = Range("A1:Z1").Find(what:="PO", LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False) '<--| if it didn't find "PO_NUMBER" then it seaches for a complete match of "PO"
        If Not PONumberCell Is Nothing Then PONumberCell.EntireColumn.ColumnWidth = 70
    End If
End Sub

Upvotes: 3

Shai Rado
Shai Rado

Reputation: 33682

Following the comments above, there are a few erros in your code:

  1. Setting the PONumberAddress Range, you need to use the syntax : Set PONumberAddress = Range(PONumberCell) using the address string found in brackets.

  2. To set the column width, use : PONumberAddress.Columns.ColumnWidth = 70.

  3. According to your post, I think you want to do this only for columns where the header text is "PO_NUMBER", therefore you need all the code below inisde your If : If TopLabelinColumn.Value Like "PO_NUMBER" Then.

Code

Option Explicit

Sub adjustColumns()

Dim PONumberCell As String
Dim PONumberAddress As Range
Dim TopLabelinColumn As Range

For Each TopLabelinColumn In Range("A1:Z1").Cells
    If TopLabelinColumn.Value Like "PO_NUMBER" Then
        TopLabelinColumn.Value = "PO"
        PONumberCell = TopLabelinColumn.Address

        Set PONumberAddress = Range(PONumberCell)
        PONumberAddress.Columns.ColumnWidth = 70
    End If
Next TopLabelinColumn

End Sub

Upvotes: 1

Related Questions