Solved

Find value in one column and replace value in another

Posted on 2016-08-31

Microsoft Excel

1 solution

62 Views

Last Modified: 2016-08-31

Hi Experts

I am having trouble finding a solution to my task so I am once again hoping that I might get some assistance here.
I require a VBA script to find the values listed in Column D and replace whatever is currently in Column F with the value "30-03-AM".
The sheet name is constant "COMBINATION-DLY" and the columns that the data will appear in is always the same. What changes
from day to day is the row that the data appears. It varies from day to day based on the total number of records. Also there is a chance that a value may NOT be there every day.

Any help would be greatly appreciated.

Andy

Column D      Column F
252881        30-03-AM
252889        30-03-AM
252890        30-03-AM
207554        30-03-AM
233230        30-03-AM
252844        30-03-AM
253279        30-03-AM
257168        30-03-AM
261858        30-03-AM

Comment

Question by:spudmcc

[X]

Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

Help others & share knowledge
Earn cash & points
Learn & ask questions

Join The Community

6 Comments

LVL 31

Expert Comment

by:Subodh Tiwari (Neeraj)

ID: 417784382016-08-31

Please try this....

Sub ReplaceValues()
Dim lr As Long
Dim rng As Range
lr = Cells(Rows.Count, 4).End(xlUp).Row
ActiveSheet.AutoFilterMode = 0
With Range("D1:D" & lr)
   .AutoFilter field:=1, Criteria1:="<>"
   On Error Resume Next
   Set rng = Range("F2:F" & lr).SpecialCells(xlCellTypeVisible)
   If Not rng Is Nothing Then rng.Value = "30-03-AM"
End With
ActiveSheet.AutoFilterMode = 0
End Sub

Open in new window

Author Comment

by:spudmcc

ID: 417784642016-08-31

Not sure if I it was clear that there are other values in both columns. I can't do a global type find and replace. It must be where the specific value in column D (one of those listed) is found and then the replacement can be done. Column D might contains thousands of other values. I am sorry I wasn't more clear.

A

LVL 31

Expert Comment

by:Subodh Tiwari (Neeraj)

ID: 417784702016-08-31

Where are those criteria for col. D placed in the workbook, on another Sheet?
Or you want to use them as hard coded values in the code?

Can you upload a sample workbook with some dummy data to work with?

LVL 31

Accepted Solution

by:Subodh Tiwari (Neeraj)

Subodh Tiwari (Neeraj) earned 500 total points

ID: 417785482016-08-31

If you list your criteria for column D on a separate sheet in column A (e.g. in column A on Sheet2 in the attached), then you may use the following code to get the desired output.
Please change the sheet names used in the code as per your requirement.

Sub ReplaceValues()
Dim lr As Long, dlr As Long, i As Long
Dim rng As Range
Dim arrFilters() As String
Application.ScreenUpdating = False
lr = Cells(Rows.Count, 4).End(xlUp).Row
With Worksheets("Sheet2")
   dlr = .Cells(Rows.Count, 1).End(xlUp).Row
   ReDim arrFilters(1 To dlr)
   For i = 1 To dlr
      arrFilters(i) = CStr(.Range("A" & i).Value)
   Next i
End With
ActiveSheet.AutoFilterMode = 0
With Worksheets("Sheet1").Range("A1").CurrentRegion
   .AutoFilter field:=4, Criteria1:=arrFilters, Operator:=7
   On Error Resume Next
   Set rng = Range("F2:F" & lr).SpecialCells(xlCellTypeVisible)
   If Not rng Is Nothing Then rng.Value = "30-03-AM"
End With
ActiveSheet.AutoFilterMode = 0
Application.ScreenUpdating = True
End Sub

Open in new window

Click the button "Replace Values" on Sheet1 to run the code.
Replace-Values.xlsm

Author Closing Comment

by:spudmcc

ID: 417786292016-08-31

You are fantastic. This is exactly what I needed to complete this project. Thank you for your prompt response and sharing your knowledge.

Andy

LVL 31

Expert Comment

by:Subodh Tiwari (Neeraj)

ID: 417786332016-08-31

You're welcome Andy! Glad to help.

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An overview of using NPV and IRR in Excel for evaluating investment decisions

Article by: Dave

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…

Microsoft Excel

How to quickly and accurately populate Word documents with Excel data, charts, and images (including Automated Bookmark generation)

Article by: dlmille

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …

Microsoft Excel

Creating Interactive Pivot Charts in Excel

Video by: AnnieCushing

This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

Microsoft Excel

Enumerating Prime Numbers in Excel / VBA

Video by: Ed

Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210 (2 * 3 * 5 * 7) or 2310 (2 * 3 * 5 * 7 * 11). The larger templa…