VBA Column Find and change format

[StuartWhi]

Hi Excel experts,

I have been trying to do this for way to long and it’s got to be a simple syntax so hope you can guide me in the right directions…

What I would like to do is, Find text in a Row get the Column number and then change the properties of that column, snippet below.

This code does what I would like it to,

Sub AdjColworking()
Dim ColValue As Long
ColValue = Range(Cells(16, 2), Cells(16, 8)).Find("S. Weight", Range("B16")).Column
Columns(ColValue).NumberFormat = "0.0"
End Sub

But I require it in this format,

Sub AdjCol()
Dim ColValue As Long
Set ColValue = Range(Cells(16, 2), Cells(16, 8)).Find("S. Weight", Range("B16")).Column
If Not ColValue Is Nothing Then
    Columns(ColValue).NumberFormat = "0.0"
End If
End Sub

What am I missing,

• Ideally I would like to use “Find("S. Weight", Range(Cells(16, 2))” not ”Find("S. Weight", Range(Cells(“B16”))” but this isn’t the course of the issue.

I get an “Object” required error on the “Set ColValue” line.

Thanks in advance and look forward to any ideas.

 

[Rick Rothstein]

This code does what I would like it to,

Sub AdjColworking()
Dim ColValue As Long
ColValue = Range(Cells(16, 2), Cells(16, 8)).Find("S. Weight", Range("B16")).Column
Columns(ColValue).NumberFormat = "0.0"
End Sub

But I require it in this format,

Sub AdjCol()
Dim ColValue As Long
Set ColValue = Range(Cells(16, 2), Cells(16, 8)).Find("S. Weight", Range("B16")).Column
If Not ColValue Is Nothing Then
    Columns(ColValue).NumberFormat = "0.0"
End If
End Sub

The problem with your second macro is that you declared ColValue as a Long, but you are trying to set a reference to it like it was a Range and you are trying to do that with a single numeric value (Set is only used with objects, not standard variables). I am completely lost as to what you are trying to do with that second macro. Can you clarify something for us though... if the first macro does what you want, why are you seeking to recast it in a different way? If it works, that should be enough... what is motivating you to try and change it?

 

[Teeroy]

There are a couple of issues. You've Dimensioned ColValue as Long then you try and "set" its value which can only do with an object. Also the result of the expression that you are trying to set is an integer, not a range object.

Try instead something like:

Sub teeroy()
Dim rFound As Range
Set rFound = Range(Cells(16, 2), Cells(16, 8)).Find("S. Weight", Range("B16"))
If Not rFound Is Nothing Then
    rFound.EntireColumn.NumberFormat = "0.0"
End If
End Sub

 

[StuartWhi]

Hi Rick and Teeroy,

Thanks for your help, just what I needed.

Rick to answer your question, it’s possible that the row being searched for the text doesn’t contain the text, in this case it errors out. If you have a better way to do this I would love to learn.

• I have seen many say on this site it’s not ideal to use “On Error Resume Next” so I have been trying to avoid it.

Teeroy this code works great but I do wonder what the value is the Set is returning because when I hover over it, it returns “S. Weight” not the column number (which I was expecting, thus the reason I assume you have changed the NumberFormat line as well).

• Also (if you have a minute) is there any reason why I’m not able to use the Cells referenced within the “.Find(“S. Weight”, Range(????))” section?

Thanks both the insight.

Stuart.

 

[Teeroy]

Hi Stuart,

The "set" expression is returning a Range object. When you hover over it you see the Value of the Range object as this is its default property. For the problem you posed you don't need to know the column number, hence the code essentially finds the cell that contains the text then selects that entire column that includes that cell. If you did need the column number you could get it using rFound.column.

There is no reason why you can't use Cells(16,2) to define the After Range of the .Find. The only restriction is that it must be a single cell Range.

Teeroy

 

[StuartWhi]

Hi Teeroy,

Thanks for the details and help always amazed with this community. Great stuff from great people.

Stuart