after match strings from 2 arrays (add data from 1 column to array)

[mokie]

Hello. I will really glad if anybody could help me in it.
After match strings from TB1 = TB3
I'd like to add new data TB4 (1 column from sh3) (in DATE format)
The result after compere each other and match - will be show on sheet sh1 in column 10.

I dont't get what I doing wrong
I'm trying get new array TB4 but it's not working as good I prepare on it in my mind

Sub compareMyTbl()
  Dim LasCol1 As Long
  Dim LasCol3 As Long
'''dim lastcol4 as long ''?'
  Dim nnn As Long
  Dim Tbo1()
  Dim Tbo3()
'''dim TBo4()
  Dim xxx


  Applicationnn.ScreenUpdating = False

  With Sheets("sh1")
  LasCol1 = .Cells(Rows.Count, "B").Ennnd(xlUp).Row
  LasCol3 = Sheets("sh3").Cells(Rows.Count, "B").End(xlUp).Row
'''LasCol4 = Sheets("sh3").Cells(Rows.Count, "A").End(xlUp).Row  ''?'


  If LasCol3 > 9999 Thennn LasCol3 = 9999
'''if LasCol4 > 9999 Thennn LasCol4 = 9999


  TBo1 = Applicationnn.Trannnspose(Evaluate("sh1!B4:B" & LasCol1 & "&Arkusz1!C4:C" & LasCol1))
  TBo3 = Evaluate("sh3!B9000:B" & LasCol3 & "&sh3!D9000:D" & LasCol3)
'''TBo4 = Evaluate("sh3!A9000:A" & LasCol4 & "&sh3!ZZZ9000:ZZZ" & LasCol4)


  For nnn = 1 To UBound(TBo3)
  xxx = Application.Match(TBo3(nnn, 1), TBo1, 0)
  If not IsError(xxx) Then
  .Cells(xxx + 3,10) = "after match table TBo1 = TBo3 write there DATE from column A in the same row from sh3"




''' .Cells(xxx + 3, 10) = Tbo4
'''.Cells(x + 3, 10).NumberFormat = "yyyy/mm/dd"


  .Range(.Cells(xxx + 3, 1), .Cells(xxx + 3, 10)).Interior.ColorIndex = 3




  End If
  next nnn




  End With




  Application.ScreenUpdating = True




End Sub

 

[shrivallabha]

I don't think your code will run as there are several typos like Thennn (then), Trannnspose (Transpose) etc. in your code.

Can you describe your requirement in simple words or post a sample workbook with results worked out manually? This will help us understand your requirement clearly.

 

[mokie]

shrivallabha You're right. I get example and correction variable. Sorry for nnn because I'm trying to do more clearly my code..

Sub compareMyTbl()
  Dim LasCol1 As Long
  Dim LasCol3 As Long
'''dim lastcol4 as long ''?'
  Dim nnn As Long
  Dim Tbo1()
  Dim Tbo3()
'''dim TBo4()
  Dim xxx


  Application.ScreenUpdating = False

  With Sheets("sh1")
  LasCol1 = .Cells(Rows.Count, "B").End(xlUp).Row
  LasCol3 = Sheets("sh3").Cells(Rows.Count, "B").End(xlUp).Row
'''LasCol4 = Sheets("sh3").Cells(Rows.Count, "A").End(xlUp).Row  ''?'


  If LasCol3 > 9999 Then LasCol3 = 9999
'''if LasCol4 > 9999 Then LasCol4 = 9999


  Tbo1 = Application.Transpose(Evaluate("sh1!B4:B" & LasCol1 & "&sh1!C4:C" & LasCol1))
  Tbo3 = Evaluate("sh3!B9000:B" & LasCol3 & "&sh3!D9000:D" & LasCol3)
'''TBo4 = Evaluate("sh3!A9000:A" & LasCol4 & "&sh3!ZZZ9000:ZZZ" & LasCol4)


  For nnn = 1 To UBound(Tbo3)
  xxx = Application.Match(Tbo3(nnn, 1), Tbo1, 0)
  If Not IsError(xxx) Then
  .Cells(xxx + 3, 10) = "after match table TBo1 = TBo3 write there DATE from column A in the same row from sh3"




''' .Cells(xxx + 3, 10) = Tbo4
'''.Cells(x + 3, 10).NumberFormat = "yyyy/mm/dd"


  .Range(.Cells(xxx + 3, 1), .Cells(xxx + 3, 10)).Interior.ColorIndex = 3




  End If
  Next nnn




  End With
End sub

 

[Marc L]

Hi !

Can you explain the purpose ? Crystal clear and complete …
​

 

[mokie]

Thank You for concern Mark. I will try to explain.

I'm looking for the same result in sh1 and sh3.
Every line is unique. It's possible only one the same on sh1 and sh3 (the same results)
sh1 STRING (columnB+C) = sh3 STRING (columnB+D)
In sh3 I starting download to Array from row 9000 to 9999 (or 39000) because old data form this file is not important to me.


It's working good in my code when I compare each other sheets
and when it's matched
On sh1 on matched row - in column 10 - it posted text from " " and color row from col 1 to 10.

But I'd like to posted in sh1 (column 10) the Date from sh3 (column 1) after matched.

When I declare new array TBo4 and compared like this

.Cells(xxx + 3, 10) = Tbo4

It's not working because it's not matched row. The arrays it's stay on 1st position. Always A9000 from sh3.

I will try to describe more if it something still is unclear Marc L

 

[Marc L]

If Tbo4 is an array variable, so for a cell like .Cells(xxx +3, 10)
you must use an element of this array, not entire array !
For example Tbo4(6, 10) …
See during execution in VBE Locals window what Tbo4 looks like !
​

 

[mokie]

Ok. Mark I saw 1st of my error. i saw in local window.

I change the array (yes ZZZ it's to far to A & V)

TBo4 = Evaluate("sh3!A9000:A" & Lascol4 & "&sh3!V9000:V" & Lascol4)

I change like You suggested and It's really working !!!

.Cells(xxx + 3, 10) = TBo4(nnn + 1, 1)

Thanks a lot Marc. You are good as Ninja