VBA replace column a based on cells

Euro5
Euro5 used Ask the Experts™
on
I would like to have a code that would look at
sheet "Enter Data" cells D6 & G6 and if they are both populated...

Change all cells in sheet "Reduced_Data" COLUMN A that = D6 to what is in G6.

the sample is attached.
Rerate-v5.0.xlsm
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2015

Commented:
So if i understand this correctly..You want to to change all values of Reduced_data sheet from E2 to GR... Is this what you are looking for??

Saurabh...
Older than dirt
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
Try this.

Sub UpdateReducedData()
Dim lngLastRow As Long
Dim lngRow As Long

With Sheets("Reduced_Data")
    lngLastRow = .Range("A1048576").End(xlUp).Row
    For lngRow = 2 To lngLastRow
        If .Cells(lngRow, 1) = Sheets("Enter Data").Range("D6") Then
            .Cells(lngRow, 1) = Sheets("Enter Data").Range("G6")
        End If
    Next

End With
End Sub

Open in new window


BTW IMO you have too many modules. If I were you I would move the contents of all the subs to one or two modules.

Author

Commented:
Perfect!
Yes, you are right of course.
I thought I would be more organized keeping different modules, but it actually got out of control.
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
The harder job would be to go through the module(s) and delete what you don't need, like the Sub x that I added while I was working on of your questions.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial