Solved: Find column with a value and replace it with another column

[Hamond]

Hello,

I have data from column BC to DA in a sheet called changes. Actual data is from row 5 to row 163. Series names are in row 1.

I want to find any series/column in the range that contains any occurrence of the value #NUM!, and replace the series/entire column with the equivalent series from another sheet (called levels) that is also shares the same name in row 1.

So in the sheet "changes", if row 7 in column BP (call it Series ZK) contains #NUM!, then I want to find the equivalent series in the sheet "Levels" (which will also be called Series ZK in row 1 but in any column), copy over that entire column and replace the column in the changes sheet.

I've attached an example to make things clearer. Column BP - X14 in the sheet levels contains #NUM!, I want to replace this with column O from the levels sheet.

I'm thinking that it would be easy to identify the relevant columns either by using find or using a formula such as sum to sum each columns contents, this will return #num! if it contains it. But I'm not sure about the code.

Thanks,

Hamond

[Bob Phillips]

[vba]

Public Sub ReplaceErrors()
Dim cell As Range
Dim ColNum As Long

Application.ScreenUpdating = False

With Worksheets("Changes")

Set cell = .Columns("BCB").Find(What:="#NUM!", SearchOrder:=xlByColumns)
If Not cell Is Nothing Then

Do

ColNum = Application.Match(.Cells(1, cell.Column), Worksheets("Levels").Rows(1), 0)
If ColNum > 0 Then

Worksheets("Levels").Columns(ColNum).Copy .Cells(1, cell.Column)
End If

Set cell = .Columns("BCA").FindNext
Loop Until cell Is Nothing
End If
End With

Application.ScreenUpdating = True
End Sub
[/vba]

[p45cal]

slightly differently:[vba]Sub blah()
Dim DestRng As Range, SourceRng As Range
For Each colm In Sheets("Changes").Range("BC1A163").Columns
If Evaluate("=COUNTIF(" & colm.Address & ",""#NUM!"")") > 0 Then
Set zzz = Sheets("Levels").Rows(1).Find(colm.Cells(1).Value)
If Not zzz Is Nothing Then
Set DestRng = colm
Set SourceRng = zzz.Resize(DestRng.Rows.Count)
DestRng = SourceRng.Value
Else
MsgBox "No equivalent column header for '" & colm.Cells(1).Value & "' found!"
End If
End If
Next colm
End Sub[/vba]

[Hamond]

XLD/P45Cal,

Thanks for the code. XLD - yours has been tried and tested and works fine. Have not tested the other code yet.

Just one general question, what should be the best practice/criteria in selecting between two or more pieces of code that does the same thing as in the current example? Is there any way to work out which is more efficient?

In the VB Editor is there a way to measure how long each piece of code takes to run?

Thanks,

Hamond

[GTO]

Hi Hammond,

As to your last question, an easy time test is to temporarily insert:

Dim Start as Single: Single = Timer

immedietely above where the timing should begin, and:

Debug.Print Timer - Start

Immedietely below where timing should stop. Afetr running the code, you can find the elapsed time in the Immediete Window.

I would certainly defer to others (who are for more knowledgeable) opinion on judging what code if "the best" between two or more choices, but it would seem to me that producing reliably accurate results trumps speed.

Let's say one snippet runs quicker than another, but the quicker one can foul if the wrong sheet is selected (when the code starts), or produces unexpected results from time-to-time.

Not sure if the analogy will make sense, but... if I suddenly came into a large amount of cash, I would love nothing better than to race a Top Fuel dragster. But... I wouldn't want to depend on one for transportation to and from work, cuz getting there every day is more important that getting there quickly one day, but neve again, cuz the motor blew.

Does that make sense?

Mark

[Bob Phillips]

You cannot/should not just worry about efficiency, you should also consider maintainability. An hour of your time lost in debugging is worth far more than shaving 2ms off of a procedure. Of course there comes a threshold when you might have to improve its efficiency, but there are usually some obvious things in such cases,