Macro to find match and Update neighbouring cell(s)

NeoSez

Board Regular
Joined
Aug 14, 2020
Messages
164
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
I have a master sheet that has hundreds of rows and several columns, that I want to update with a status list that changes daily. Sometimes, it may only have 20 updates. I want it to find by the Tracking Column "G" and if there is a match in "A", to update the status column "C" with the data from "H" and leave everything else in the column untouched.
Here's the last part of this: I need to extract only the date from Column "I" to add to "D" as part of the update. How do I extract only the date portion?
I found this code but I don't quite understand it, as I am new to. It worked well, but it returns a '#name? error'. Is it because there is not a match? How to change the formula to not return an error and leave the unmatched column(s) untouched?
Thank you for your assistance.

VBA Code:
Sub MultiFindNReplace()
'Updateby Extendoffice
Dim Rng As Range
Dim InputRng As Range, ReplaceRng As Range
xTitleId = "Update"
Set InputRng = Application.Selection
Set InputRng = Application.InputBox("Original Range ", xTitleId, InputRng.Address, Type:=8)
Set ReplaceRng = Application.InputBox("Replace Range :", xTitleId, Type:=8)
Application.ScreenUpdating = False
For Each Rng In ReplaceRng.Columns(1).Cells
    InputRng.Replace what:=Rng.Value, replacement:=Rng.Offset(0, 1).Value
Next
Application.ScreenUpdating = True
End Sub

=IFERROR(INDEX($g$2:$h$16, MATCH(A2,$h$2:$h$500,0)),[B]cell[/B])  'returns #name? error'
 

Attachments

  • Update.JPG
    Update.JPG
    71.6 KB · Views: 16

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,976
Office Version
  1. 2010
Platform
  1. Windows
mastarr and updt are just the names I chose for two variant arrays, this statement:
VBA Code:
mastarr = Range(Cells(1, 1), Cells(lastmast, 4))
is code that copies the contents of the range from A1 (cells(1,1) to the range given by lastmast which is the last row with data in it and column 4 which column D
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

NeoSez

Board Regular
Joined
Aug 14, 2020
Messages
164
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
I hope that has made you realise how important it is to write clear , unambiguous and complete requirements.
I have updated the code according to the last statement of the requirements. I have also changed the way I detected the date. because I realised that the way I detected the date ( i the first example) depended on the date string always being 9 characters long, however this is not always the case 1/2/2020 is 8 characters long and 12/12/2020 is 10 characters long.
so I have changed the code so it first looks for the first number in the string and once it has found that it looks for the first character which ISN'T a number or a slash, or the end of the string. and uses that to extract the length of the date string. This does depend on the date being entered with the delimiter being a slash /. ie. it won't detect dates like:
2:2:2020 or 2\2\2020 or 23 jan 2020 .
I have also when checking for delivered or received I convert it to uppercase so it will detect Delivered, delivered and DELIVERED, or even DeLivered as all meeting the criteria. Same with received.
try this:
VBA Code:
Sub test3()
Dim fullstr As String
With Worksheets("Sheet2")
lastup = .Cells(Rows.Count, "F").End(xlUp).Row ' find last row in column F of sheet 2
updt = Range(.Cells(1, 1), .Cells(lastup, 24)) ' pick columns A to X and all rows in sheet 2
' new mapping G is now F column 6
' H  is now X column 24
'I is now T column 20 Date is assumed to be all the characters up to the first space
End With
Worksheets("Sheet1").Select      ' lots of people say don't use select  but doing it once is quick and easy!!
lastmast = Cells(Rows.Count, "A").End(xlUp).Row
mastarr = Range(Cells(1, 1), Cells(lastmast, 4))

For i = 2 To lastmast
For j = 2 To lastup
  If mastarr(i, 1) = updt(j, 6) Then ' Column F
  mastarr(i, 3) = updt(j, 24) ' update status for all rows
  ' convert update status to upper case for comparison
   sts = StrConv(updt(j, 24), vbUpperCase)
  If sts = "DELIVERED" Or sts = "RECEIVED" Then
   fullstr = updt(j, 20) ' Column T
    startstr = -1
    endstr = Len(fullstr)
    For kk = 1 To Len(fullstr)
        digt = Mid(fullstr, kk, 1)
        If IsNumeric(digt) And startstr < 0 Then ' check for the first number in the string
           startstr = kk   ' set this to start of the string
        End If
        digasc = Asc(digt)   ' convert the curent character to ascii
        If startstr > 0 And (digasc > 57 Or digasc < 47) Then ' this checks whether the digit is a number or /
         endstr = kk ' set then end of the string as the first character which isn't a number or a slash
         Exit For
        End If
    Next kk
      If startstr > 0 Then
      dt = Mid(fullstr, startstr, endstr - startstr + 1)
      mastarr(i, 4) = dt
      End If
  End If
  End If
Next j
Next i
Range(Cells(1, 1), Cells(lastmast, 4)) = mastarr
End Sub

@offthelip I was trying add a TRIM function to the end of the date that was extracted to have no spaces, but I don't know where to put or how to write it. Do I add a sub after ?
VBA Code:
mastarr(i, 4) = dt
 

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,976
Office Version
  1. 2010
Platform
  1. Windows
you should be able to do it like this:
VBA Code:
mastarr(i, 4) = trim(dt)
 

Forum statistics

Threads
1,141,865
Messages
5,709,076
Members
421,613
Latest member
wyzco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top