Tests Copying, Pasting, API Cliipboard issues. and Rough notes on Advanced API stuff

**DocAElstein** · 05-26-2020, 02:16 PM

Macro for this post:
https://excelfox.com/forum/showthrea...ata-if-matches

Code:

Sub AddColumnJValueInWs1basedOnMatchAndCritzeriaInWs2() ' https://excelfox.com/forum/showthread.php/2556-copy-and-paste-of-data-if-matches
Rem 1 Worksheets info
'1_1 sample1.xls
Dim Wb1 As Workbook, Ws1 As Worksheet
' Set Wb = Workbooks.Open(ThisWorkbook.Path & "\" & "Sample1.xls")
' Set Wb = Workbooks.Open(ThisWorkbook.Path & Application.PathSeparator & "Sample1.xls")
 Set Wb1 = Workbooks("1.xls")
 Set Ws1 = Wb1.Worksheets.Item(1)
Dim arrWs1() As Variant: Let arrWs1() = Ws1.Range("A1").CurrentRegion.Value2
Dim Lr1 As Long: Let Lr1 = UBound(arrWs1(), 1)
'1_1b) data range
Dim arrS1() As Variant 'Let arrS1() = Ws1.Range("A1").CurrentRegion.Value
Let arrS1() = Ws1.Range("A1:J" & Lr1 & "").Value ' Input data range
'1_2 AlertCodes.xlsx
Dim WbA As Workbook, WsA4 As Worksheet
 Set WbA = Workbooks("AlertCodes.xlsx")
 Set WsA4 = WbA.Worksheets.Item(4)
Dim RwCnt4 As Long: Let RwCnt4 = WsA4.Range("A" & WsA4.Rows.Count & "").End(xlUp).Row
'1_2b) dataa range
Dim arrWsA4() As Variant: Let arrWsA4() = WsA4.Range("A1:K" & RwCnt4 & "").Value2
'1_2d) second column in Alertcodes.xlsx
Dim ClmB() As Variant: Let ClmB() = WsA4.Range("B1:B" & RwCnt4 & "").Value

Rem 3
Dim Cnt As Long
    For Cnt = 2 To Lr1 ' going down "rows" in 1.xls
    Dim MtchRes As Variant
     Let MtchRes = Application.Match(arrWs1(Cnt, 9), ClmB(), 0) '  match column I of 1.xls  with sheet4 of column B of Alertcodes.xlsx
        If IsError(MtchRes) Then
        ' do nothing - no match
        Else ' look at symbol in column D, 4th worksheet of AlertCodes.xlsx for that matched row in column D, 4th worksheet of AlertCodes.xlsx
            If arrWsA4(MtchRes, 4) = ">" Then ' If symbol is > then
             Let arrS1(Cnt, 10) = "SHORT" ' put SHORT in column J of 1.xls for the matched row
            ElseIf arrWsA4(MtchRes, 4) = "<" Then '  If symbol < then
             Let arrS1(Cnt, 10) = "BUY" ' put BUY in column J of 1.xls for the matched row
            Else
            End If
            
        End If
    Next Cnt

Rem 4 Paste back out arrS1()
 Let Ws1.Range("A1:J" & Lr1 & "").Value2 = arrS1()
End Sub