Selecting Cells Based On User Input

[Willow123]

Good Evening All,

I'm trying to select a range of cells based on what a user enters into an input box. In theory the selection I want to copy will always start in row ten so the user would just have to enter the column (it would be beneficial to know if a person can enter a specific cell ex B11 and the range will be selected based on that). I've tried using a string data type and entering the letter as well as using a long data type and entering the number of the column ie B would be 2. Both methods end with an application or object defined error...

[FONT=Verdana]Sub TagType_Click()[/FONT]
[FONT=Verdana]
Dim MyCell As Long[/FONT]
[FONT=Verdana]
MyCell = InputBox("What Column Are The Document Numbers Located In?")[/FONT]

[FONT=Verdana]
Dim wb1 As Workbook, shxx As Worksheet

Set wb1 = ActiveWorkbook[/FONT]
[FONT=Verdana]
Set shxx = wb1.Sheets(2)[/FONT]
[FONT=Verdana]   Dim Cl As Range[/FONT]
[FONT=Verdana]
   With CreateObject("scripting.dictionary")
      For Each Cl In shxx.Range("MyCell", shxx.Range("MyCell").End(xlDown))
         .Item(Cl.Value) = Cl.Offset(, 1).Value
      Next Cl
      For Each Cl In shxx.Range("C1", shxx.Range("C1").End(xlDown))
         Cl.Offset(, 1).Value = .Item(Cl.Value)
      Next Cl
   End With[/FONT]
[FONT=Verdana]End Sub[/FONT]

Any thoughts would be greatly appreciated.

Thanks All

 

[Michael M]

so, you just want to copy the range in the selected column to the new location at the bottom of col "C", is that correct ??

 

[Willow123]

No, So what should happen is the subroutine will select a used range determined by what the user enters (for a cell reference) and then it will compare everything in that used range to some data in another 2nd used range, starting at C1 and going down. My problem is sometimes the first used range that needs to be compared is in a different column.

The code below works fine it will compare some data starting in I2 and going down till it hits a blank cell, to some data starting in C1 and going down till it hits a blank cell. I believe it is similar to V LookUp. The problem is sometimes the data, contained in the fist used range to be compared will not be in the I column starting at row 2, it may be in the J column, or the H column. A work around is just to add or delete columns to make the data in the first used range you want to compare end up in the I column. I was thinking if the user just entered what column the data for the first used range to compare was in...well that would be better. Hopefully that made sense. Apologies if it did not.

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]   Dim Cl As Range[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]
   With CreateObject("scripting.dictionary")
      For Each Cl In shxx.Range("I2", shxx.Range("I2").End(xlDown))
         .Item(Cl.Value) = Cl.Offset(, 1).Value
      Next Cl
      For Each Cl In shxx.Range("C1", shxx.Range("C1").End(xlDown))
         Cl.Offset(, 1).Value = .Item(Cl.Value)
      Next Cl
   End With

[/FONT]

 

[Michael M]

So maybe this....assuming the user puts in the the column Letter at the prompt.....UNTESTED

Sub TagType_Click()
Dim MyCell As String, lr As Long, Cl As Range, shxx As Worksheet
MyCell = InputBox("What Column Are The Document Numbers Located In?")
Set shxx = Sheets(2)
lr = shxx.Cells(Rows.Count, MyCell).End(xlUp).Row
   With CreateObject("scripting.dictionary")
      For Each Cl In shxx.Range(MyCell & 2 & ":" & MyCell & lr)
         .Item(Cl.Value) = Cl.Offset(, 1).Value
      Next Cl
      For Each Cl In shxx.Range("C1", shxx.Range("C1").End(xlDown))
         Cl.Offset(, 1).Value = .Item(Cl.Value)
      Next Cl
   End With
End Sub

 

[Willow123]

Thanks Michael that works awesome!