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

 

[Fluff]

Re: Help with setting selected cells as range

How about

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 Long
   Dim Cl As Range
   Dim rngASIN As Range
   Dim Lngth As Long
   
   Set rngASIN = Range("A1:Z1").Find("ASIN")
   If rngASIN Is Nothing Then
      MsgBox "ASIN column was not found."
      Exit Sub
   End If
   
   For Each Cl In Range(rngASIN.Offset(1), Cells(Rows.Count, rngASIN.Column).End(xlUp))
      If Cl.Value = "<>" Then
         Lngth = Lngth + 1
      End If
   Next
   
    MsgBox Lngth
End Sub

 

[DrParmeJohnson]

Re: Help with setting selected cells as range

I ran it and it just printed out 0. I'm trying to have it print the length of the column that has header "ASIN", which is what I'm trying to do with

Set rngASIN = Range("A1:Z1").Find("ASIN")

 

[Fluff]

Re: Help with setting selected cells as range

What exactly are you trying to do with that column?

 

[DrParmeJohnson]

Re: Help with setting selected cells as range

As of right now, I am trying to find the column with the header "ASIN" and then find the length of that column to use as a variable in a for loop like

For i to length
and then do something

eventually I plan on trying to loop through the column, looking at each cell, comparing that cell to some other information in a different workbook, copying some other information from that other workbook and the pasting that info back on the first book until I am out of cells in that "ASIN" column.

 

[Fluff]

Re: Help with setting selected cells as range

You don't need to "find the length" of the column.
This portion of the code

   For Each Cl In Range(rngASIN.Offset(1), Cells(Rows.Count, rngASIN.Column).End(xlUp))
      If Cl.Value <> "" Then
         Lngth = Lngth + 1
      End If
   Next

loops through the column from row 2 to the last used cell in the column.

 

[DrParmeJohnson]

Re: Help with setting selected cells as range

So you're saying I'd instead just use that to go through the whole range of the column? Or are you saying that's the part I'd need to cut? I'm sorry for the confusion but, I'm just quite new to this.

 

[Fluff]

Re: Help with setting selected cells as range

So you're saying I'd instead just use that to go through the whole range of the column?

That's right.
You could use it like this to perform a lookup on another sheet & return the value to the next column

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 Cl As Range
   Dim rngASIN As Range
   Dim Res As Variant
   
   Set rngASIN = Range("A1:Z1").Find("ASIN")
   If rngASIN Is Nothing Then
      MsgBox "ASIN column was not found."
      Exit Sub
   End If
   
   For Each Cl In Range(rngASIN.Offset(1), Cells(Rows.Count, rngASIN.Column).End(xlUp))
      Res = Application.vlookup(Cl.Value, Sheets("sheet1").Range("G2:J100"), 3, 0)
      If Not IsError(Res) Then Cl.Offset(, 1).Value = Res
   Next
   
End Sub

 

[DrParmeJohnson]

Re: Help with setting selected cells as range

So the For is looking at each cell in the range after the header, looking up a value on the sheet1 in the range G2:J100. But what's the 3 after the range for? Isn't that a column number? And then what is that If not doing? I'm a bit lost there.

 

[Fluff]

Re: Help with setting selected cells as range

Its simply returning a value from the 3rd column of the look range.
If the value is not found the code will return an error & the If Not iserror line is checking if an error was returned, if not then it pastes the value of Res in the column 1 to the right of of the ASIN column