Excel VBA - Copy if value equals "X" to another column in same row

[falascot]

IN VBA, I'm trying to copy the value in column MaterialNum (column "P") if it equals the value 390.005-15, the value is copied to (column "C") Part# in the same row. It will overwrite the value in this case of 390.005. The current table is approximately 65,000 rows, but in most cases the value of 390.005-15 in column "P" maybe less than 20 rows.

I need to keep looping through the spread sheet until it's finds all the values.

This information is being pulled from a SQL table for manufacturing and it refreshes every 15 minutes

On the image, I hid some of the columns so I could show the two working columns that I have identified. Column "C" and column "P".

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Excel VBA - Copy if value equals "X" to another column in same row
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[Fluff]

If the data is being pulled from an outside source, won't the values in col C revert back to what they are when the table refreshes?

 

[falascot]

If the data is being pulled from an outside source, won't the values in col C revert back to what they are when the table refreshes?

Before it's updated, the information is copied to another drive per the current macro. The macro is a little or a lot clumsy, but gets the job done. I would like to have the copy function inserted before the file is saved. Here is the macro I'm currently running. Thanks.

Sub SQL_GO()
'
' SQL_GO Macro
'
' Keyboard Shortcut: Ctrl+s

'Refresh SQL Connection and SQL Table

Windows("laser_workorder_brw.xls").Activate
Range("A2").Select
ActiveWorkbook.RefreshAll
ActiveWorkbook.Save


'Save As to Y:\Laser_Down Directory

Application.DisplayAlerts = False

Windows("laser_workorder_brw.xls").Activate


' Change Directory to C:\Laser_Download and save

ChDir "C:\Laser_Download"
ActiveWorkbook.SaveAs Filename:="C:\Laser_Download\laser_workorder_brw.xls", _
FileFormat:=xlExcel8, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

' Change Director to Y:\Laser_Download and save

ChDir "Y:\Laser_Download"

ActiveWorkbook.SaveAs Filename:="Y:\Laser_Download\laser_workorder_brw.xls", _
FileFormat:=xlExcel8, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False


'Go back to SQL Go
Windows("SQL Go.xlsm").Activate


' Set timer to NOW + 15 minutes

Application.OnTime Now + TimeValue("00:15:00"), "SQL_GO"



End Sub

 

[Fluff]

Ok, how about

Sub falascot()
   With Range("C2", Range("C" & Rows.Count).End(xlUp))
      .Value = Evaluate(Replace("if(" & .Offset(, 13).Address & "=""390.005-15"",""390.005-15"",if(@="""","""",@))", "@", .Address))
   End With
End Sub

 

[falascot]

Hi, Thanks for the quick response. I added it to my macro and appears to be copying the value from C2 into all of the cells in the C column instead of the value identified in the VBA (390.005-15) from column P.

 

[Fluff]

Is your data filtered?

 

[falascot]

Is your data filtered?

Yes, I'm using a excel power query to sort my data.

 

[Fluff]

In that case you will need to unfilter the data before using the macro.

 

[falascot]

That I can do, just tried it and it it works. Thanks.