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]

=INDEX(Table_Query_from_GJ[Last_Stat_Date],MATCH(ClientListings!A664,Table_Query_from_GJ[CLIENT_NUM],0))

So far this is as far as I can get without VBA and it gives me the dates but replaces all the data that isn't there with N/A instead of leaving it alone with it's current date.

 

[mse330]

Hi Dark0Prince,

Try the below code ...

Sub UpdateDates()

Dim Arr As Variant, Dic As Object
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

Arr = Sheets("ClientListings").Range("A5", Sheets("ClientListings").Range("E" & Rows.Count).End(xlUp)).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").Range("A5").Resize(UBound(Arr), UBound(Arr, 2)).Value = Arr

End Sub

 

[Dark0Prince]

It does what it is supposed to but unfortunately it effects the whole sheet instead of the parts between "startGJ" and "endGJ" text and there are duplicate numbers in other sections of my sheet that should not be effected.

 

[mse330]

I also want it to effect a range of cells in between the words startGJ and endGJ

My bad I didn't notice this part of your original post

Also, what do you mean by "there are duplicate numbers in other sections of my sheet that should not be effected" ? Are these duplicates out of the first range you specify (startGJ and endGJ) ?

 

[Dark0Prince]

There are other sections of my sheet where I have the text startABQ, endABQ and startDEN, and endDEN so that I will only effect the proper sections with the code. some of the client numbers are the same in other sections of the clientlisting which is why I say that there are duplicate numbers.

 

[Rick Rothstein]

Does this macro do what you want...

[table="width: 500"]
[tr]
	[td]Sub ChangeDates()
  Dim R As Long, StartGJ As Range, EndGJ As Range, ChangeMe As Variant
  Set StartGJ = Sheets("ClientListings").Columns("A").Find("startGJ", , , xlWhole, , , False, , False)
  Set EndGJ = Sheets("ClientListings").Columns("A").Find("endGJ", , , xlWhole, , , False, , False)
  ChangeMe = Sheets("GJ_IMPORT").Range("A2", Sheets("GJ_IMPORT").Cells(Rows.Count, "E").End(xlUp))
  For R = 1 To UBound(ChangeMe)
    Range(StartGJ, EndGJ).Find(ChangeMe(R, 1), , , xlWhole, , , , , False).Offset(, 4) = ChangeMe(R, 3)
  Next
End Sub[/td]
[/tr]
[/table]

 

[mse330]

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

 

[Dark0Prince]

I'm getting an object variable or block variable not set msg. @Rick

 

[Rick Rothstein]

I'm getting an object variable or block variable not set msg. @Rick

On which line of code?