0

I might post my question here and hope for help before I bust my head through the wall.

Here is the deal: I have a date(Data_carga) and a time(Hora_carga) for the load the user changed its status. So I want to apply his change to my back-end Loads Table.

My Load table (Table5) is always custom sorted by date. So what I thought was to xmatch my Data_carga against the date column (Worksheets("CargasBD").Range("Table5[DATA]")) both from first to last (row1) and from last to first(row2). And then, using those two rows combined in an indirect function, I would xmatch the Hora_carga against that specific interval. Then I would have the map to the same load on the back-end table to update it.

But I cannot get the xmatch function to simple locate a value in a table on another tab.I keep getting this runtime error 1004.

Both my looked-up value and table are Date type. So I have no idea why I am getting this error.

Code is bellow.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False

Dim Data_carga As Date
Dim Hora_carga As Date
Dim Novo_status As String
Dim row, row1, row2 As Integer
Dim addrss, Range_data As String

Set In_range = Application.Intersect(Target, Range("$D$3:$P$11"))
If Not In_range Is Nothing Then
    Data_carga = Range("A" & Target.row).Value
    Hora_carga = Range(Left(Target.Address, Len(Target.Address) - Len(CStr(Target.row))) & "2").Value
    Novo_status = Application.WorksheetFunction.XLookup(Target.Value, Sheets("BD").Range("Table17[Abrev]"), Sheets("BD").Range("Table17[Status das cargas]"), "", 0, 1)
    
    'This is where it breaks
    row1 = Application.WorksheetFunction.XMatch(Data_carga, Worksheets("CargasBD").Range("Table5[DATA]"), 0, 1)
    row2 = Application.WorksheetFunction.XMatch(Data_carga, Worksheets("CargasBD").Range("Table5[DATA]"), 0, -1)
    
    addrss = Application.WorksheetFunction.Concat("CargasBD!C", 1 + row1, ":C", 1 + row2)
    Range_data = Application.WorksheetFunction.INDIRECT(addrss, True)
    
    row = Application.WorksheetFunction.XMatch(Hora_carga, Range_data, 0, 1)
    
    Sheets("CargasBD").Range("D" & row + row1).Value = Novo_status
End If

Application.EnableEvents = True
End Sub

Btw, the date(Data_carga) and a time(Hora_carga) are getting fetched okay.

Thank you in advance.

Gui
  • 13
  • 5
  • 1
    That is not how a table is referenced in vba. See: https://stackoverflow.com/questions/18030637/how-do-i-reference-tables-in-excel-using-vba – Scott Craner Sep 17 '20 at 20:09
  • also use: `Hora_carga = Cells(2, Target.Column).Value` less steps. – Scott Craner Sep 17 '20 at 20:34
  • Thanks, @ScottCraner. I went to that link and bounced from one to another and learned a bunch that may help. I have changed it to: `row1 = Application.WorksheetFunction.XMatch(Data_carga, Sheets("CargasBD").ListObjects("Table5").DataBodyRange.Columns(1), 0, 1)` but still no success. – Gui Sep 18 '20 at 12:34
  • I have made a test using that same writing but looking for a value of 8 in a table's sorted with numbers from 1 to 20: `row_teste = Application.WorksheetFunction.XMatch(8, Sheets("Sheet1").ListObjects("Table7").DataBodyRange.Columns(1), 0, 1)` It worked out as expected, so I am guessing that my looked update(`Data_carga`) is somehow a mismatch with my date type table column (`Sheets("CargasBD").ListObjects("Table5").DataBodyRange.Columns(1)` I will make some more tests and try to find a way around this. – Gui Sep 18 '20 at 12:34
  • It looks like I had a breakthrough. Maybe if I change my date variable (`Data_carga`) to be a range type it could work... – Gui Sep 18 '20 at 12:39

1 Answers1

0

Got it working finally.

So what I had to do was:

  • Change my looked-up variables as Range types instead of Date
  • Use Set to copy the data to these two i.e. Hora_cargaand Data_carga

I had messed a lot with it. It would work if I pasted the value from the Data_carga into a cell and use that on the xmatch formula but I did NOT want to take this detour. So there was something odd about my variable (that I had changed it to be a range type already) and a range in the worksheet.

One thing before continuing: the time value (Hora_carga) that was okay started being funky and while looking up the error I was directed to use the Set command for it.

So I used the Locals Window to investigate my date range variable and saw this strange difference between my two variables:

Locals window

Even though I had decalred both as Range type, my hora_cargaended up becoming a Date type. And it had a weird valeu with #s. So I just replicated what I had done to my Hora_carga and it worked.

Here is the final code:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False

Dim Data_carga, Hora_carga As Range
Dim Novo_status, addrss As String
Dim HoraRow, rowSubRngStrt, rowSubRngEnd, rowHdr As Integer

Set In_range = Application.Intersect(Target, Range("$D$3:$P$11"))
If Not In_range Is Nothing Then
    Set Data_carga = Range("A" & Target.row)
    Set Hora_carga = Range(Cells(2, Target.Column).Address(0, 0))
    Novo_status = Application.WorksheetFunction.XLookup(Target.Value, Sheets("BD").Range("Table17[Abrev]"), Sheets("BD").Range("Table17[Status das cargas]"), "", 0, 1)
    
    rowHdr = Sheets("CargasBD").ListObjects("Table5").HeaderRowRange.row
    rowSubRngStrt = Application.WorksheetFunction.XMatch(Data_carga, Sheets("CargasBD").ListObjects("Table5").DataBodyRange.Columns(1), 0, 1) + rowHdr
    rowSubRngEnd = Application.WorksheetFunction.XMatch(Data_carga, Sheets("CargasBD").Range("Table5[DATA]"), 0, -1) + rowHdr
    addrss = Application.WorksheetFunction.Concat("C", rowSubRngStrt, ":C", rowSubRngEnd)
    
    HoraRow = Application.WorksheetFunction.XMatch(Hora_carga, Sheets("cargasBD").Range(addrss), 0, 1)
    
    Sheets("CargasBD").Range("D" & (rowSubRngStrt + HoraRow - 1)).Value = Novo_status
End If

Application.EnableEvents = True
End Sub
Gui
  • 13
  • 5