PDA

View Full Version : how to make general range



petroj02
10-21-2016, 12:04 AM
Hello,

I have few collumns with data and this data I want to coppy to different workbook only once. I am trying to make this code for Sub with General variable range. the part of range I want to Change is named myRng. Unfortunately it doesnt work and gives me an error message and I am not able to solve this...




Sub setRange (byval rng as range, wb as workbook)
Dim d As Object, c As Variant, i As Long, lr As Long
dim myRng as range
set myRng = Range(rng)
Set d = CreateObject("Scripting.Dictionary")
lr = ThisWorkbook.Sheets(sheetName).Cells(Rows.Count, 1).End(xlUp).Row
c = ThisWorkbook.Sheets(sheetName).Range(myRng& lr)
For i = 1 To UBound(c, 1)
d(c(i, 1)) = 1
Next i
For i = 3 To 100
If .Sheets("List1").Cells(i, 3) = "" Then
x = i
Exit For
End If
Next
wb.Sheets("List1").Cells(x, 3).Resize(d.Count) = Application.Transpose(d.Keys)

so I have to implement this twice



Dim d As Object, c As Variant, i As Long, lr As Long
Set d = CreateObject("Scripting.Dictionary")
lr = ThisWorkbook.Sheets(sheetName).Cells(Rows.Count, 1).End(xlUp).Row
c = ThisWorkbook.Sheets(sheetName).Range("BK5:BK& lr)
For i = 1 To UBound(c, 1)
d(c(i, 1)) = 1
Next i
For i = 3 To 100
If .Sheets("List1").Cells(i, 3) = "" Then
x = i
Exit For
End If
Next
.Sheets("List1").Cells(x, 3).Resize(d.Count) = Application.Transpose(d.Keys)

Set d = CreateObject("Scripting.Dictionary")
c = ThisWorkbook.Sheets(sheetName).Range("BL5:BL& lr)
For i = 1 To UBound(c, 1)
d(c(i, 1)) = 1
Next i
For i = 3 To 100
If .Sheets("List1").Cells(i, 3) = "" Then
x = i
Exit For
End If
Next
.Sheets("List1").Cells(x, 3).Resize(d.Count) = Application.Transpose(d.Keys)

mana
10-21-2016, 02:19 AM
What's the purpose to use dictionary?

petroj02
10-21-2016, 04:44 AM
actaully, I found this part of code somewhere on Internet. I dont even know whats the dictonary doing exactly... It is on higher skill Level than I actually have.. But It should take from my main workbook data at specific column. (this data could be in column multiple times) And take this data to new workbook but only once... this is the reason of using dictionary. but I have found that this solution isnt the best one because, if there is empty cell in column, it wont convert data that follows after this cell...

mana
10-21-2016, 05:16 AM
??


Dim d As Object
Dim v
Dim i As Long, j As Long

Set d = CreateObject("Scripting.Dictionary")

v = rng.Value

For i = 1 To UBound(v, 2)
For j = 1 To UBound(v, 1)
If v(i, j) <> "" Then d(v(i, j)) = 1
Next
Next

wb.Sheets("List1").Cells(Rows.Count, "C").End(xlUp).Offset(1).Resize(d.Count) _
= Application.Transpose(d.Keys)

mikerickson
10-22-2016, 02:38 PM
You've already declared rng to be a range object, you don't need to use the Range property.


Sub setRange (byval rng As range, wb As workbook)
Dim d As Object, c As Variant, i As Long, lr As Long
Dim myRng As range

' Set myRng = Range(rng): rem not this

Set myRng = rng: Rem this instead

' ...
End Sub