Excel VBA Help with Declaring Variables

[taugdog21]

I am getting an object required error for some of my declared variables not sure how to correct it. I probably have more errors than this too if anyone can help me out it would be appreciated I don't have much experience in VBA.

Dim i As Integer, j As Integer, k As Integer
Dim x As Range, y As Range
Dim Num As Long

Num = 94
Set y = Range("A4:A9")
Set x = Range("B1:G10")
Set j = Range("C4")
Set i = Range("B4")
Set k = Range("A4")

Do
Num = Num - 1
i.Select
Selection.Copy
Windows("New Data Base.xlsm").Activate
j.Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Set x = x.Offset(10, 0)
Set j = j.Offset(5, 0)

Range("P3:P8").Select
Application.CutCopyMode = False
Selection.Copy
i.Select
ActiveSheet.Paste
Set i = i.Offset(5, 0)

Sheets("PNA Project Data").Select
k.Select
Application.CutCopyMode = False
Selection.Copy
Sheets("PNA Physical Needs Data").Select
k.Select
ActiveSheet.Paste
Set k = k.Offset(1, 0)

Application.CutCopyMode = False
Selection.AutoFill Destination:=y, Type:=xlFillDefault
y.Select
Set y = y.Offset(5, 0)
Loop Until Num = 0
End Sub

 

[dave3009]

If it's not an object you don't need to set it, so the integers like i, j, and k can lose the Set.

 

[RoryA]

It appears to me that your integers are actually ranges - that is certainly how the code uses them - so change the first line of declarations to Range rather than Integer.

 

[Firefly2012]

Also, Integers being simple data types don't have properties or methods so you can't do things like

i.Select

So, either you need to wholly amend your code or you need to change your dimensioning of the Integers to Ranges.

 

[Asala42]

(replying to locked thread http://www.mrexcel.com/forum/showthread.php?t=604016)

What would be a good way to increment each of my ranges for example I need x to copy Range B11:to G20 and paste it to C10 for the second loop. Each Variable has different increments.

Any Help is appreciated cause I'm horrible with Visual Basic.

Sub Macro5()
'
' Macro5 Macro
'
Dim i As Range, j As Range, k As Range
Dim x As Range, y As Range
Dim Num As Integer

Num = 94

Set x = Sheets("Sum Data").Range("B1:G10")
Set j = Sheets("PNA Physical Needs Summary Data").Range("C4")

etc

I'm trying to follow your logic, so lets simplify to just talk about x and j for the moment. If I read you correctly:

Loop 1 (num =94), You copy Sum data B1:G10 to PNAData C4.
Loop 2 (Num = 93), you want to copy B11:G20 to PNAData C10? Did you mean C14? Otherwise you'll be overwriting previous rows copied over.

For example purposes, I'll assume you meant C14 on loop 2, just so we have a consistant pattern. Here's another approach:

Application.ScreenUpdating = False

Dim i As Long

For i = 0 To 93
Sheets("Sum Data").Activate
Range(Cells(1 + 10 * i, 2), Cells(10 + 10 * i, 7)).Copy

Sheets("PNA Physical Needs Summary Data").Activate
Range(Cells(4 + 10 * i, 3), Cells(13 + 10 * i, 8)).PasteSpecial (xlPasteValues)

Next i

Application.ScreenUpdating = True