looking for VBA solution that changes data

[Dark0Prince]

I have old dates in the E column on my sheet called ClientListings. Then on the next sheet Called GJ_Import I have new dates in the C column.
How could I use VBA to update the old dates on SOME of the clients when there client number matches from the A column on both sheets. I also want it to effect a range of cells in between the words startGJ and endGJ

Example data is linked here: https://1drv.ms/x/s!Ap0t6Fmj7XZehBIhhLAgL58G_cFG

 

[Dark0Prince]

Range(StartGJ, EndGJ).Find(ChangeMe(R, 1), , , xlWhole, , , , , False).Offset(, 4) = ChangeMe(R, 3)

 

[Rick Rothstein]

Range(StartGJ, EndGJ).Find(ChangeMe(R, 1), , , xlWhole, , , , , False).Offset(, 4) = ChangeMe(R, 3)

What do you get if you execute the following statements in the Immediate Window after going into Debug mode when the error occurs...

? StartGJ.Address

? EndGJ.Address

? R

? UBound(ChangeMe)

? ChangeMe(R, 3)

 

[Dark0Prince]

Try this updated code …

Sub UpdateDates()
Dim Arr As Variant, Dic As Object, fRow As Long, lRow As Long
Set Dic = CreateObject("Scripting.Dictionary")
Arr = Sheets("GJ_IMPORT").Range("A2", Sheets("GJ_IMPORT").Range("C" & Rows.Count).End(xlUp)).Value
For x = LBound(Arr) To UBound(Arr)
    If Not Dic.exists(Arr(x, 1)) Then Dic.Add Arr(x, 1), Arr(x, 3)
Next
fRow = Sheets("ClientListings").Range("A:B").Find("startGJ").Row
lRow = Sheets("ClientListings").Range("A:A").Find("endGJ").Row
Arr = Sheets("ClientListings").Range(Cells(fRow, 1), Cells(lRow, 5)).Value
For x = LBound(Arr) To UBound(Arr)
    If Dic.exists(Arr(x, 1)) And Dic(Arr(x, 1)) > Arr(x, 5) Then
        Arr(x, 5) = Dic(Arr(x, 1))
    End If
Next
    
Sheets("ClientListings").Cells(fRow, 1).Resize(UBound(Arr), UBound(Arr, 2)).Value = Arr
End Sub

This works, I have a question though. How do I change this if I want to search the second column for the client number on the GJ_IMPORT?

 

[mse330]

@Rick … The StartGJ is actually a merged cell in columns A:B based on the sample file provided by the OP

​

<strike></strike>
​

 

[Dark0Prince]

What do you get if you execute the following statements in the Immediate Window after going into Debug mode when the error occurs...

? StartGJ.Address

? EndGJ.Address

? R

? UBound(ChangeMe)

? ChangeMe(R, 3)

As soon as I use ? StartGJ.Address I get "method not valid without a suitable object"

 

[mse330]

This works, I have a question though. How do I change this if I want to search the second column for the client number on the GJ_IMPORT?

So if the client number is in column B the Stat_Date is still in columns C or it moves as well to column D ?

 

[Dark0Prince]

It is still in C. So I would just like to search both columns for the client number.

 

[Dark0Prince]

I attempted to apply this to other sections and it didn't work I can't even debug it because I just get a runtime error. Does anything appear wrong to you?

Sub ABQ_UpdateDates()
Dim Arr As Variant, Dic As Object, fRow As Long, lRow As Long
Set Dic = CreateObject("Scripting.Dictionary")
Arr = Sheets("ABQ_IMPORT").Range("A2", Sheets("ABQ_IMPORT").Range("C" & Rows.Count).End(xlUp)).Value
For x = LBound(Arr) To UBound(Arr)
    If Not Dic.exists(Arr(x, 1)) Then Dic.Add Arr(x, 1), Arr(x, 3)
Next
fRow = Sheets("ClientListings").Range("A:B").Find("startABQ").Row
lRow = Sheets("ClientListings").Range("A:A").Find("endABQ").Row
Arr = Sheets("ClientListings").Range(Cells(fRow, 1), Cells(lRow, 5)).Value
For x = LBound(Arr) To UBound(Arr)
    If Dic.exists(Arr(x, 1)) And Dic(Arr(x, 1)) > Arr(x, 5) Then
        Arr(x, 5) = Dic(Arr(x, 1))
    End If
Next
    
Sheets("ClientListings").Cells(fRow, 1).Resize(UBound(Arr), UBound(Arr, 2)).Value = Arr
End Sub

 

[Rick Rothstein]

@Rick … The StartGJ is actually a merged cell in columns A:B based on the sample file provided by the OP

​

<strike></strike>
​

Merged cells are always a problem and need special handling (so in the future questions, you should always identify merged cells when they exist).

Are there any other merged cells? If so, where?

 

[Dark0Prince]

Just in A:B columns, but i still need to be able to search for the client number in A or B on the IMPORT tabs