Setting selected cells as range

[DrParmeJohnson]

Hello,
I am trying to make my macro take a set of selected cells and assign those cells as a range to a variable, excluding the header. The macro is a WIP and so this is aimed to help build the next step of the macro. The next step would be to take that range, use it to find how long the column is and then use that length to loop through each cell of the column and do something else. Also, the commented bits can be ignored as they are just placeholder at the moment. But anyways, here is what I have thus far:

Sub LD_ASIN()
'
' LD_ASIN Macro
'
    'ActiveWorkbook.SaveAs ("C:\Users\Alek Pruszynski\Documents\Amazon Documents\Amazon Sheet - " _
                                            & Format(Now(), "DD-MM-YYYY") & ".xlsm")
    'Dim amznnum As String
    'Dim itemnum As String
    Dim i As Integer
    Dim cell As Variant
    Dim length As Integer
    Dim myRange As Range
    Dim rngASIN As Range
    
    Set rngASIN = Range("A1:Z1").Find("ASIN")
        If rngASIN Is Nothing Then
        MsgBox "ASIN column was not found."
    Exit Sub
    End If
    Columns.Range(rngASIN, rngASIN.End(xlDown)).Select
    
    If TypeName(Selection) = "Range" Then
        Set myRange = Selection
    Else
        Exit Sub
    End If
    
    For Each cell In myRange
        If cell.Value = "<>" Then
            length = length + 1
        End If
    Next
        
    MsgBox length
    
    
    'length =
    'For i = 1 To Columns.Count
   ' Application.CountA(Columns(rngASIN)) = length
  '  length = Range(rngASIN, rngASIN.End(xlDown)).Count
        'Next
    
   ' For i = 1 To length
        
    
    
    
   ' Application.Index(Application.VLookup( ) , Range("2:5000")) =
'
End Sub

 

[DrParmeJohnson]

Re: Help with setting selected cells as range

So, say I wanted to use that vlookup on another workbook named "ASIN to LD.xlsx" where it would look at the first column, with the header ASIN, and then find the value in that column and once it finds the correct value, it would copy the value of the cell to the right of it and then copy that value back to the first workbook and paste it in the column to the right of the ASIN column on that book. Would I just use

Application.VLookup(Cl.Value, Sheets("sheet1").Range("A2:B1000"), 1, 0)

And if so, how do I do that lookup on the other workbook?
Also, sorry for berating you with questions btw, I'm just quite lost in all this.

 

[Fluff]

Re: Help with setting selected cells as range

Something like

Sub LD_ASIN()
   Dim Wbk As Workbook
   Dim Ws As Worksheet
   Dim Cl As Range
   Dim rngASIN As Range
   Dim Res As Variant
   
   Set Ws = ActiveSheet
   Set Wbk = Workbook.Open("C:\.....") 'enter path & filename of workbook to open
   Set rngASIN = Ws.Range("A1:Z1").Find("ASIN")
   If rngASIN Is Nothing Then
      MsgBox "ASIN column was not found."
      Exit Sub
   End If
   
   For Each Cl In Ws.Range(rngASIN.Offset(1), Ws.Cells(Rows.Count, rngASIN.Column).End(xlUp))
      Res = Application.vlookup(Cl.Value, Wbk.Sheets("sheet1").Range("A2:B1000"), 2, 0)
      If Not IsError(Res) Then Cl.Offset(, 1).Value = Res
   Next
   
End Sub