×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • 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.

Students Click Here

VBA Code Debug

thread766-213053

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

Recommended for you

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.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login


News
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:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close