Hi guys, I need some advice regarding the following problem, I hope you can help me out. I have a sheet1 and sheet2 that look like this:
Column1 and column2 are given in sheet1 while the rest should be filled with a macro. Sheet1.column2: if a cell is a number, go to sheet2, find that number and paste the numbers attached to it. Here is how sheet1 and sheet2 look like:
[ATTACH=JSON]{"data-align":"none","data-size":"medium","data-attachmentid":1203428}[/ATTACH]
If it's some text in sheet1.column2 then use the information from sheet1.column1(for example in cell B5, use the info from A5), go to sheet3 find the correct row using the info and paste the numbers back to sheet1 same as in the first case.
Sheet3 looks like this:
[ATTACH=JSON]{"data-align":"none","data-size":"medium","data-attachmentid":1203429}[/ATTACH]
I have a macro that does this, however I need it modified in a way such that when it's a number in column2 and that number doesn't exist in sheet2 macro acts as it is a text and goes to find numbers based on criteria of column1.Currently it just leaves it blank. This is my current macro:
Sub testmacro()
Dim sArr(), tArr(), dArr(), Rng As Range, Cls As Range
Dim i As Long, j As Long, K As Long
Set Rng = Sheets("Sheet1").Range("B2", Sheets("Sheet1").Range("B2").End(xlDown))
ReDim dArr(1 To Rng.Rows.Count, 1 To 100)
sArr() = Sheets("Sheet2").Range("A2", Sheets("Sheet2").Range("A2").End(xlDown)).Resize(, 101).Value
tArr() = Sheets("Sheet3").Range("A2", Sheets("Sheet3").Range("C2").End(xlDown)).Resize(, 101).Value
For Each Cls In Rng
If IsNumeric(Cls) Then
K = K + 1
For i = 1 To UBound(sArr, 1)
If sArr(i, 1) = Cls.Value Then
For j = 2 To 101
dArr(K, j - 1) = sArr(i, j)
Next j
End If
Next i
Else
K = K + 1
For i = 1 To UBound(tArr, 1)
If tArr(i, 1) = Cls.Offset(, -1).Value Then
For j = 2 To 101
dArr(K, j - 1) = tArr(i, j)
Next j
End If
Next i
End If
Next Cls
Sheets("Sheet1").Range("C2").Resize(K, 100) = dArr
End Sub
Thanks in advance!