Excel VBA Help with Declaring Variables

T

taugdog21

New Member
Joined
Jan 6, 2012
Messages
4
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
 
Sort by date Sort by votes
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.
 
Upvote 0
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.
 
Upvote 0
Also, Integers being simple data types don't have properties or methods so you can't do things like
Code: 
i.Select

So, either you need to wholly amend your code or you need to change your dimensioning of the Integers to Ranges.
 
Upvote 0
(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.

Code: 
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
Click to expand...

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:

Code: 
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
EMAIL REMINDERS FROM EXCEL VBA
Replies
2
Views
369
Logit
Logit
togenmoser
  • Solved
Help with VBA code error "For variable already in use"
2 3
Replies
22
Views
1K
MARK858
MARK858
U
  • Question Question
VBA Search Functionality Issue with Arabic Supplier Names in Excel Help Needed???
Replies
1
Views
157
Peter_SSs
Peter_SSs
M
  • Question Question
Please help me to correct Excel VBA code!
Replies
1
Views
451
Minh Tuan VN
M
B
  • Solved
VBA - Copying Non-Contiguous Ranges in/out of (an) Array(s)
2
Replies
18
Views
527
kvsrinivasamurthy
K

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top