×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Are you an
Engineering professional?
Join Eng-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

#### Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

# VBA Code Debug

## VBA Code Debug

(OP)
So it's probably obvious that I am a newbie by looking at my code. Why doesn't this work? What should I do to fix?

In this example I use the Excel Formula in C3 =CountA(Sheet2!A:A), which returns the value of 11 (number of rows not empty), which includes Headers.
I want to copy the Range on Sheet2 (A2:F11) because the count told me 11 rows to Sheet1 (A2:F11)

Dim MyCount = Integer
Dim i = Integer

Sub do_it()

MyCount = Sheets("Sheet1").Cells(C, 3)

For i = 2 To MyCount
Sheets("Sheet1").Cells(A, i) = Sheets("Sheet2").Cells(A, i)
Sheets("Sheet1").Cells(B, i) = Sheets("Sheet2").Cells(B, i)
Sheets("Sheet1").Cells(C, i) = Sheets("Sheet2").Cells(C, i)
Sheets("Sheet1").Cells(D, i) = Sheets("Sheet2").Cells(D, i)
Sheets("Sheet1").Cells(E, i) = Sheets("Sheet2").Cells(E, i)
Sheets("Sheet1").Cells(F, i) = Sheets("Sheet2").Cells(F, i)
Next i

End Sub
Replies continue below

### RE: VBA Code Debug

When referring to cell "A1" using the .Cells property, you should use Cells(1,1), not Cells(A,1).  Also, with .Cells, the row number comes first.  So cell "C2" is Cells(2, 3).

-handleman, CSWP (The new, easy test)

### RE: VBA Code Debug

try
sheets("sheet1").range("A2:F11").value = sheets("sheet2").range("A2:F11").value

### RE: VBA Code Debug

(OP)
Thank you for your help. Both of your solutions helped point be in the right direction. I was missing one critical component to the MyCount variable.

MyCount = Sheets("Sheet1").Cells(C, 3).value

the MyCount varialbe was not getting the value of cell C3 so the loop was never functional. Here is the code that solved my problem.

Dim i As Integer
Dim MyCount As Integer

Sub do_it()

MyCount = Cells(1, 3).Value

For i = 2 To MyCount
Sheets("Sheet1").Cells(i, 1) = Sheets("Sheet2").Cells(i, 1)
Sheets("Sheet1").Cells(i, 2) = Sheets("Sheet2").Cells(i, 2)
Sheets("Sheet1").Cells(i, 3) = Sheets("Sheet2").Cells(i, 3)
Sheets("Sheet1").Cells(i, 4) = Sheets("Sheet2").Cells(i, 4)
Sheets("Sheet1").Cells(i, 5) = Sheets("Sheet2").Cells(i, 5)
Sheets("Sheet1").Cells(i, 6) = Sheets("Sheet2").Cells(i, 6)
Next i

End Sub

Thanks again.

#### Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

#### Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Close Box

# Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!