Code error

[Rohith1324]

Hi

I have excel sheet with 2 Columns

column 1 will have repetative values - 1 Value might repeat almost 50 - 100 Time and in the second Column the it will have the numbers

below is code i'm using and in some system the code is getting executed and in some the code is not getting executed, can someone please help :

Dim oDict As Dictionary
Dim sData() As Variant
Dim LastRow As Long
Dim i As Variant
Dim Cnt As Variant

Set oDict = CreateObject("Scripting.Dictionary")
LastRow = Cells(Rows.Count, "A").End(xlUp).Row

For i = 2 To LastRow
If Not oDict.Exists(Cells(i, "A").Value) Then
Cnt = Cnt + 1
ReDim Preserve sData(1 To 2, 1 To Cnt)
sData(1, Cnt) = Cells(i, "A").Value
sData(2, Cnt) = Cells(i, "B").Value
oDict.Add Cells(i, "A").Value, Cnt
Else
sData(2, oDict.Item(Cells(i, "A").Value)) = _
sData(2, oDict.Item(Cells(i, "A").Value)) & _
", " & Cells(i, "B").Value
End If
Next i

Range("G1").Value = Range("A1").Value
Range("H1").Value = Range("B1").Value
Range("I1").Value = "Circle"
Range("J1").Value = "HW/SW"

'Transfer the contents of the array to a worksheet range, starting at D2
Range("G2").Resize(UBound(sData, 2), 2).Value = _
WorksheetFunction.Transpose(sData)

End Sub

 

[jasonb75]

in some system the code is getting executed and in some the code is not getting executed,

So you mean it works on one pc, but not on another?
That would most likely be down to the user not allowing macros.

 

[Fluff]

Try changing

Dim oDict As Dictionary

to

Dim oDict As Object

 

[Rohith1324]

So you mean it works on one pc, but not on another?
That would most likely be down to the user not allowing macros.

if the lines are less then all the system the macro is running.

but if the lines are more than 50 - 60 then its not happening in other systems

 

[CountTepes]

When it comes to users not allowing macros, there is a way which will guarantee that the allow macros, or else they only see a warning.

When you have finished your project, create another worksheet.

In that worksheet, in big red letters, type in a warning that macros are off and need to be on.

Hide all the other sheets.

In the workbook code area type in Private sub Workbook_open()

In that code area put in the code to unhide all your hidden worksheets and to hide the warning worksheet

Then type in Private sub Workbook_Beforeclose(Cancel as boolean)

Then type type in the code to reverse what you did in workbook open.

Now whenever the user opens the workbook and doesn't allow macros, all they will see is a warning page.

 

[CountTepes]

Is everyone using the same version of excel?

 

[Fluff]

When you get the error, what is the Error number & error message?
Also if you hit debug what line is highlighted?

 

[Rohith1324]

When you get the error, what is the Error number & error message?
Also if you hit debug what line is highlighted?

After hitting Debug It is highlighting below:

Range("G2").Resize(UBound(sData, 2), 2).Value = _
WorksheetFunction.Transpose(sData)

At this moment I don't have the number, I will check the number and tell you as soon as possible...

 

[Fluff]

My best guess, without seeing the data, is that you are exceeding the limit of using Transpose. Especially as it works for smaller amounts of data.

 

[Rohith1324]

My best guess, without seeing the data, is that you are exceeding the limit of using Transpose. Especially as it works for smaller amounts of data.

could you please help me with code which can work for me even the data is more...