Hi Kragelund

This part of your code he

With Cells
Set Cells(16, 2) = P1R1_udbt_start
Set Cells(30, 6) = P1R1_udbt_length
Set Cells(31, 8) = P1R1_amount_primo
End With

is not correct. May I ask what you are trying to achieve with this? I
believe it likely that you are trying to assign the values in the cells
to your declared variables. If correct, then you don't do it like this
(the Set is a keyword used for assigning references to Objects, but
there are no objects on the right hand side of the equal sign (cos they
are integers/doubles etc) and indeed you have no variables on the left
hand side of the equal sign either).

If you were trying to do assing the values in these cells to the
variables, then you simply do it like so:


P1R1_udbt_start = Cells(16, 2).Value
P1R1_udbt_length = Cells(30, 6).Value
P1R1_amount_primo = Cells(31, 8).Value

As regards your other question, no, there's no need to make these
variables Public - they can be self-contained within the one procedure
if they are not required to persist from one macro run to the next.

Hope this helps!

Richard

Kragelund wrote:

I'd appreciate a sharp eye on this snippet of code. The simple purpose of the
code is to take an amount from one sheet and transfer this to a column in
another sheet and insert this amount distributed in fractions over a number
of cells determined by the P1R1_udbt_length variable. Can anybody tell me why
the procedure returns the errror message "Object variable or with block
variable not set"? The code is not complete I should add.

I think I've tried 1000 different combinations of changes to variables
etc., but nothing seems to work for me.

Additional question: is it necessary to have variables as public variables,
when you are doing such a simple transaction as taking one set of data and
inserting them in another sheet?

Thanks in advance for help provided!

Kragelund



Public P1R1_udbt_start As Integer, P1R1_udbt_length As Integer,
P1R1_amount_primo As Double

Public Sub Export_P1R1()

Dim i As Integer, j As Integer
Dim sheets As worksheets
Dim worksheets As worksheets

worksheets("Per.1 Rate.Pen. nr. 1").Activate
With Cells
Set Cells(16, 2) = P1R1_udbt_start
Set Cells(30, 6) = P1R1_udbt_length
Set Cells(31, 8) = P1R1_amount_primo
End With

sheets("IndtPer1").Activate

For i = 0 To 55
On Error Resume Next

With Cells
Cells(10 + i, 1).Select
If Cells(10 + i, 1) = P1R1_udbt_start Then
Cells(10 + i, 12).Select
End If

For j = 0 To P1R1_udbt_length - 1
Cells(10 + i + j, 12) = P1R1_amount_primo
Next j
Cells(10 + P1R1_udbt_length, 12).Select

End With

Next i

sheets("Per.1 Rate.Pen. nr. 1").Select

End Sub