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)
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)